Repository navigation
NULL value imported from CSV file become empty string #195
Description
OS : OS X 10.10.2
SQLitebrowser Version : Feb 06, 2015 nightlies
It is as the title suggests, null value is become empty string when I tried to import from CSV file which has a null value (etc,10,,10,). I tried to import using other SQLite app the null value (etc,10,,10,) become NULL instead of empty string. Oh, and the preview when importing a CSV file (after choosing the CSV file) btw, also display an empty string rather than NULL value. I have already checked when I import using SQLitebrowser and open with another app it becomes empty string for the NULL value.
I don't know if this is a feature or not, but maybe you can add an option when trying to import you give a checkbox and textfield for the NULL value, so when the user check the box but leaves the textfield empty the NULL value becomes empty string or the value of the textfield if any. And when the user does not check the box, the textfield is disabled and the null value becomes NULL when imported.
Preview screenshot:
After import screenshot:
Browse table data using another app after import from CSV using SQLitebrowser:
Browse table data using SQLitebrowser after import using other SQL app:
In case you need the CSV file, here it is:
https://www.dropbox.com/s/e8pad14efeki5e7/ZWATCHACTIVITY.csv?dl=0
Activity
harysutanto commentedon Feb 7, 2015
On second try, I think when importing from a CSV data to current table that exist in the sqlite database ignores whatever default value the table has. Maybe this is why, when importing from a CSV the value of the table become empty cell rather than the table default value NULL.
I already tried using other default value other than NULL, and the result is also an empty cell rather than default value.
rp- commentedon Feb 7, 2015
Well I can live with the argument that an empty: ',,' column should NULL and if you want a empty string the CSV data should be ',"",'.
But to get default values for columns is a bit more tricky and maybe not always what a user would expect or want. And usually it shouldn't be such a problem to get default values you want into the csv data. e.g. with libreoffice calc or excel.
justinclift commentedon Mar 12, 2015
Thinking through this a bit, its pretty straightforward (to me atm 😉), that a field consisting of only two single quotes, should be an empty string. eg:
Also, a field with only NULL in it (unquoted) is very likely meant to be a NULL. eg:
Though section 2.6 of RFC 4180 does indicate it could also be considered a string value of "NULL". 😦
A field without a value though...yeah, that's interesting. NULL or "default field value" are both possibilities.
At the risk of making things more complicated, we may need another option added to our cvs import dialog. ...sigh...
harysutanto commentedon Mar 12, 2015
Yes, actually I also prefer to have an option for what to do with the empy value (',,') than to set the field to default value by default.
Anyway, Thanks for the responses and keep up the good work!
justinclift commentedon Jan 7, 2016
Just tested CSV import with our new 3.8.0 release. We still need to add that extra option to the CSV import dialog, to specify what happens to fields without values.
It should be a very simple bit of coding, if someone has time to get it done. @MKleusberg maybe? 😄
justinclift commentedon Aug 4, 2016
@revolter This "adding an extra option to the CSV import dialog" seems like it might be your kind of thing. Want to take a look at it?
revolter commentedon Aug 4, 2016
@justinclift, If only I would know how to branch based on the setting.
justinclift commentedon Aug 4, 2016
The database interaction bit could be tricky at the moment, yeah. 😦
justinclift commentedon Jun 27, 2017
@lhunsicker Unfortunately... we haven't put much/any time into this yet. We will "at some point"... but it hasn't happened yet.☹️
When importing CSV data into existing tables, don't insert empty strings
MKleusberg commentedon Jun 29, 2017
@lhunsicker Hi Larry, yeah we're currently drowning in issues, so nobody put any effort into this. But thanks for reminding us 😉 I've just made an (experimental) change to the CSV import that implements your suggestion on this. If you're importing into an existing table (if you're not we can't really make any assumptions about data types) and the importer hits an empty field in the CSV file it now checks the data type of the field. If it is numeric it will insert
0orNULL(depending on theNOT NULLflag of the field). If it isn't numeric it is still going to insert an empty string.Are you ok to download and test tomorrow's nightly build (see our main page for the link) and report back if it is improving the situation for you? 😄
10 remaining items
justinclift commentedon Jul 2, 2017
Hmm, with this:
It's a bit unclear (to me) who the "them" you're meaning are. 😉
If you're meaning the developers of the SQLite3 bash shell, then definitely reach out to them on the "sqlite-users" mailing list as a first step. They're pretty friendly too, so it should be a fairly easy experience.
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Does that help? 😄
chrisjlocke commentedon Jul 8, 2017
Its not very clear, but when you create a new issue, you are meant to type after the right pointing arrow.
The arrows are actually HTML markers to put comments, and anything put between these markers are ignored. Unfortunately, your comment was between these markers. I amended your last query, so it should show up now. 👍
Hope that helps. 🚁
PeterSmyth12 commentedon Dec 20, 2017
Using the 20th December 2017 release (Windows x64)
Importing a table into an already defined schema still does not treat a missing value as NULL but as an empty string ''.
Even if the field has been defined as REAL and defined as NOT NULL it gets the empty string.
Is there any chance that this is going to get fixed soon?
MKleusberg commentedon Jul 14, 2018
Sorry for the delay but this feature is now added to the CSV import. If there is no value in a CSV field there are now four possible actions in the import:
If you're still interested in this, please check the nightly builds and let me know if it's working as expected for you, @harysutanto, @lhunsicker and @PeterSmyth12 😄
MKleusberg commentedon Jul 14, 2018
Also, the import should be much faster now 😉
MKleusberg commentedon Aug 16, 2018
Closing this because the issue should be solved and we haven't heard back from the reporters for a while. If you feel like I closed this issue too early, feel free to re-open it - I'd be happy to hear any more ideas or opinions about this 😄