Skip to content

a checkbox option in "Import CSV file" menu to Import unique entries only #1585

Closed

Description

@antonybits

Hi, i import many new csv files into my database every few days, and after importing entries i have to remove repeated rows (using DISTINCT or GROUP BY on multiple columns).
Fortunately "Import CSV file" menu can repeat the import process for all new csv files, i hope if it could import non-repeated rows based on multiple columns.
refer to the picture which illustrate my goal: left side shows the current menu and the right side represents what i wish.
picture2

Activity

  1. changed the title [-]a chech mark in "Import CSV file" menu to Import uniqe enteries only[/-] [+]a checkbox option in "Import CSV file" menu to Import unique entries only[/+] on Oct 21, 2018
  2. chrisjlocke commented on Oct 21, 2018

    @chrisjlocke
    Member

    Can't you create an index on a column that you don't want duplicated with a UNIQUE constraint?

  3. antonybits commented on Oct 23, 2018

    @antonybits
    Author

    there is not a column that i don't want duplicated , its a row i don't want to, just like DISTINCT or GROUP BY on multiple columns
    the combination of lac,cell.lat,lon should be uniqe but each "lat" can be duplicated (same for lac,cell.lat,lon)
    each cell may be repeated but the combination should not, in other word this is unique lac||cell||lat||lon not each one of them
    (keeping in mind that importing a csv table with N columns (e.g, N=5) into a database's table with M columns (e.g, M=6) is not permitted and returns error)

  4. chrisjlocke commented on Oct 23, 2018

    @chrisjlocke
    Member

    You can create an index on multiple columns.

    importing a csv table with N columns (e.g, N=5) into a database's table with M columns (e.g, M=6) is not permitted

    Not entirely true. Import into a new table, then do an 'insert ... select ....'* query. Bit messy, and long winded, but seems to work.

    *insert into old_table (field1,field3,field5) select * from table_imported
    
  5. antonybits commented on Oct 23, 2018

    @antonybits
    Author

    You can create an index on multiple columns.

    importing a csv table with N columns (e.g, N=5) into a database's table with M columns (e.g, M=6) is not permitted

    Not entirely true. Import into a new table, then do an 'insert ... select ....'* query. Bit messy, and long winded, but seems to work.

    *insert into old_table (field1,field3,field5) select * from table_imported
    

    yes that's right but your answer is more about executing SQLs, than creating easy access through GUI into database, so isn't That off the scope of the enhancement feature requests ?

  6. antonybits commented on Oct 23, 2018

    @antonybits
    Author

    You can create an index on multiple columns.

    importing a csv table with N columns (e.g, N=5) into a database's table with M columns (e.g, M=6) is not permitted

    Not entirely true. Import into a new table, then do an 'insert ... select ....'* query. Bit messy, and long winded, but seems to work.

    *insert into old_table (field1,field3,field5) select * from table_imported
    

    inserting pre-imported entries into a UNIQUE indexed table on multiple columns definitely returns this error "UNIQUE constraint failed: ### " which forced the GUI to create the attached error massage and made it unresponsive
    please download the zip file and try to import the test2.csv into test2 table!
    that error!!! test.zip
    untitled23

  7. chrisjlocke commented on Oct 23, 2018

    @chrisjlocke
    Member

    This does actually raise an interesting error (and not the one you have screenshotted).
    We're currently looking at this part of the program, so have raised this as a separate issue.

  8. antonybits commented on Oct 24, 2018

    @antonybits
    Author

    this procedure create different errors (I don't know why) for different databases , testing the same procedure in my laptop resulted into the error mentioned in #1590
    capture3

  9. chrisjlocke commented on Oct 24, 2018

    @chrisjlocke
    Member

    Hopefully (in the latest nightly build) that particular error has been squished. Your previous issue may remain however - will test later today...

  10. antonybits commented on Oct 24, 2018

    @antonybits
    Author

    just tested the latest nightly build! #1591
    Now I get this error which is probably a rational massage, at least
    capture5

  11. chrisjlocke commented on Oct 24, 2018

    @chrisjlocke
    Member

    Yes, have just tested, and the right error is now displayed. However, the import still terminates when an error occurs, which isn't ideal (but expected). It would be nice if DB4S could continue, but that would depend on the error occurred, and would also throw up a lot of dialog boxes.

    In the short term, as you're on Windows, I could build a .Net application which could look in a table and move across records which are unique. So import into a new table, then my program would look in this new table. Yell if that could help.

  12. antonybits commented on Oct 24, 2018

    @antonybits
    Author

    Shouldn't we just create a ON CONFLICT REPLACE or ON CONFLICT IGNORE option some where ?! (not sure)

    refer to: https://sqlite.org/lang_conflict.html

    SQLITE let the users for having unique constraint on multiple columns both in table and index
    is it possible to create unique constraint on multiple columns in a "table" in DB browser (not in the index)
    if possible, where ON CONFLICT should be for us in the index or in the table?
    refer to : https://stackoverflow.com/questions/2701877/sqlite-table-constraint-unique-on-multiple-columns

  13. MKleusberg commented on Oct 24, 2018

    @MKleusberg
    Member

    @antonybits That's a really good idea! 👍 I have just added a new option for that. It is hidden behind the 'Advanced' button in the Import CSV dialog and is called 'Conflict strategy'. That's of course still not equivalent to a simple 'Unique entries only' checkbox as you suggested but should make it easy enough to simulate that behaviour by adding a unique constraint for all columns as you mentioned. So in the next nightly build you should be able to:

    1. Create your table like this
    CREATE TABLE name(a INT, b INT, c INT, UNIQUE(a,b,c));   -- List all columns in the UNIQUE constraint here
    1. Then open the Import CSV dialog like before
    2. Click the Advanced button
    3. Select your ON CONFLICT strategy there

    Do you want to give that a try tomorrow or so? 😄

  14. antonybits commented on Oct 24, 2018

    @antonybits
    Author

    Thanks, Sure, I will when the next nightly build is out,
    keep in mind that

    1. user should be led to choose UNIQUE combination of the columns (some how may be a partial clause)
    2. now I have to recreate my table with executing sql (as you wrote but for 4 of the columns not all 5 of them)
    3. I suggest a partial clause option like "partial index clause" in the "Edit Index Schema" dialog for "edit table definition" dialog and then UNIQUE(a,b,c) could be added in that dialog either.
  15. antonybits commented on Nov 3, 2018

    @antonybits
    Author

    hi, sorry for the delay, It took a while to finish my tests, the results were completely fine,
    i had to rebuild my database from scratches. (that's because i just could not add the highlighted line to the older table)
    untitled
    the "ON CONFLICT strategy" was great, i can say there is no need for the users to have #1585 "a checkbox option in "Import CSV file" menu to Import unique entries only" but i hope we had a "partial clause" illustrated in the example artificial pic below so we could set the unique constraint on multiple columns as highlighted in the previous picure. ( or any other way to make that possible for the users other than executing SQLs)
    untitled2

  16. chrisjlocke commented on Nov 6, 2018

    @chrisjlocke
    Member

    Thanks @antonybits . Will close this issue as its semi resolved, but if you have any further issues, feel free to comment further or re-open it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions