Skip to content

Ability to import NULL values from CSV #1339

Closed

Description

@mkweskin

Details for the issue

As far as I can tell, there is not a way to import NULL values from a CSV. I suggest an additional checkbox in the "Import CSV file" window of "Treat missing values as NULL". Or I'm curious if you all have another idea to handle this.
Thanks!

Useful extra information

I'm opening this issue because:

  • DB4S is crashing
  • DB4S has a bug
  • DB4S needs a feature
  • DB4S has another problem

I'm using DB4S on:

  • Windows: ( version: ___ )
  • Linux: ( distro: ___ )
  • Mac OS: ( version: 10.12.6 )
  • Other: ___

I'm using DB4S version:

  • 3.10.1
  • 3.10.0
  • 3.9.1
  • Other: ___

I have also:

Activity

  1. justinclift commented on Mar 8, 2018

    @justinclift
    Member

    Yep, that would be useful. The generally referenced CSV spec (an RFC) doesn't take into consideration database things. eg default values, NULL values, and similar

    On that note, we should probably also have the choice of treating missing (eg empty) values as "use the default value for the field, if defined". 😉

  2. mkweskin commented on Mar 8, 2018

    @mkweskin
    Author

    Agree about the use default option. I tried defining default as NULL and doing an import and saw that the default wasn't used.

  3. chrisjlocke commented on Mar 8, 2018

    @chrisjlocke
    Member

    A crappy hacky workaround which I've used in the past is to use a value thats either 'nonsense' or known. So if a date field was to have NULL values, use a date thats otherwise invalid (eg, 99/99/9999) or is outside the usual range (eg, 01/01/2000) then simply do a 'update tablename set fieldname = null where fieldname='99/99/9999' etc.
    Yup, told you it was hacky. 🔢

  4. justinclift commented on Mar 8, 2018

    @justinclift
    Member

    Still, that's a useful workaround for now. 😄

  5. remram44 commented on Mar 15, 2018

    @remram44
    Contributor

    Interestingly the behavior matches sqlite3's .import command. But I agree that it's a terrible default.

  6. remram44 commented on Mar 16, 2018

    @remram44
    Contributor

    The current code looks at the column type to decide what to do, but only has special behavior for INTEGER columns:

    if(f->isInteger() && f->notnull()) // If this is an integer column but NULL isn't allowed, insert 0
    nullValues << "0";
    else if(f->isInteger() && !f->notnull()) // If this is an integer column and NULL is allowed, insert NULL
    nullValues << QByteArray();
    else // Otherwise (i.e. if this isn't an integer column), insert an empty string
    nullValues << "";

  7. aelfric commented on Apr 25, 2018

    @aelfric

    We're trying to migrate from the no-longer maintained Firefox extension (which treated blank columns as NULL) to DB Browser and this difference bit us unexpectedly. I'd vote for an option to treat blanks as NULL on the import screen.

  8. justinclift commented on Apr 25, 2018

    @justinclift
    Member

    Oops. We really should get a review done of #1349.

    As a thought for now, while we wait for the code review to get done... would it help if I make a one-off build of DB4S (our abbreviation for DB Browser) including the code in that Pull Request?

  9. MKleusberg commented on May 22, 2018

    @MKleusberg
    Member

    Just merged that pull request 😄 Can you check tomorrow's nightly build @aelfric and @mkweskin to see if it's now working for you as expected? 😄

  10. mkweskin commented on May 23, 2018

    @mkweskin
    Author

    Looks great! Thanks team.
    This is my first look at the nightly build versions. I like the other changes I noticed: all caps for the suggested SQL commands, in the "Execute SQL" tab moving "Save as CSV/View" to the tool bar. Do you have a new official release planned?

  11. justinclift commented on May 23, 2018

    @justinclift
    Member

    We're hoping for some time in the next few weeks. As a rough guide 😉, we're generally wanting to get all of the Pull Requests completed, and fix any other bug type things that seem important enough to get included as well.

  12. MKleusberg commented on May 24, 2018

    @MKleusberg
    Member

    Thanks for testing, @mkweskin! I'll close this issue then 😄

    By the way, if you're interested in helping with the next release, maybe @justinclift can build a once-off build for #1394 for you to play around with. The changes in this pull request are large enough that some real-world test results could be helpful 😉

  13. justinclift commented on May 24, 2018

    @justinclift
    Member

    Yep, that's pretty easy to do, especially for OSX builds. It's 99.5% scripted, so I just need to point the script at the right bits and launch it.

    @mkweskin I'm happy to create an OSX build with the multi-threading code for #1394 included, if you'd be ok to try things out with it and report back weirdness/bugs/etc? 😄

  14. mkweskin commented on May 24, 2018

    @mkweskin
    Author

    Sure thing, happy to do more testing. Let me know where to download this one-off build when it's ready. It sounds like you're saying it's different than the nightly builds.

  15. justinclift commented on May 24, 2018

    @justinclift
    Member

    Yeah. The nightly builds (done automatically) use whatever code is in our master branch.

    I made a copy of the nightly build script a while ago, and adjusted it so it can be told to build from a different source. It also uploads to a different place on our nightlies server.

    I'll get the build we're discussing here launched in a few minutes. It takes a while to complete though (~1/2 from memory). 😄

  16. justinclift commented on May 24, 2018

    @justinclift
    Member

    k, just started the script. In theory - if I didn't forgot something 😉 - then the builds should appear here in about 30-40 mins:

        https://nightlies.sqlitebrowser.org/osx-onceoffs/

    There will be 2 versions. One with standard SQLite support, and one with SQLCipher support. Use whichever appeals. 😄

  17. justinclift commented on May 24, 2018

    @justinclift
    Member

    Playing around with the one including SQLCipher support. Seems to "work ok" in very light initial testing now.

    I'd better get the DBHub.io migration stuff completed today, so we have some large example data sets we can easily try with too. 😄

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

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions