[sqlite] .import command/NULL values
On 2016/03/16 11:53 AM, Bernard McNeill wrote: > Any particular reason why the shell '.import' command cannot interpret || > as a NULL for the field? The reason the import mechanism doesn't cope with NULLs is not because it can't, but because it shouldn't - until the TEXT standard changes in this regard. > Does || have another use/interpretation? > > Just seems a shame that the standard bulk input mechanism cannot cope > directly with NULLs. You are looking for the fault in the wrong culprit. The Importer would be very able to cope with nulls, if you can find me a standard where TEXT imports are described to have a way of defining NULLs. There is no such TEXT standard currently to my knowledge (though I could be wrong), and as such, importing things that can't be there, is by definition not feasible. There is no way that I know of currently to specify in a text file: "Value here" | Value here | | (There isn't really a value here) | "Value here" that would be logically distinguishable from: Value here | Value here | "" | "(There isn't really a value here)" | Value here and certainly would NOT be equivalent to: "Value here" | Value here | NULL | NULL | "Value here" I know it looks like an easy adaptation (and probably is!), but perhaps best to be reminded that any special seemingly normal use-case isn't necessarily globally useful to everyone - this is why we have standards. Cheers, Ryan
[sqlite] .import command/NULL values
Bernard McNeill wrote: > Any particular reason why the shell '.import' command cannot interpret || > as a NULL for the field? > Does || have another use/interpretation? Everything is a string. The data between the two separators is an empty string. Regards, Clemens
[sqlite] .import command/NULL values
Bernard McNeill wrote: > If it is required to import NULL values into an Sqlite table field, can > this be done No; the .import commands imports everything as text. (This can result in numbers in columns that have numeric affinity.) To get NULL values, UPDATE the table afterwards. Regards, Clemens
[sqlite] .import command/NULL values
Any particular reason why the shell '.import' command cannot interpret || as a NULL for the field? Does || have another use/interpretation? Just seems a shame that the standard bulk input mechanism cannot cope directly with NULLs. Best regards On Wed, Mar 16, 2016 at 9:13 AM, Simon Slavin wrote: > > On 16 Mar 2016, at 9:02am, Simon Slavin wrote: > > > No. Use one of these two methods. > > I'm sorry. I read your 'import' but I thought you meant using INSERT. If > you are using the shell's ".import" command then Clemens is right. You are > importing from a text file, and there is no way to put a NULL in a text > file. After your import you should do something like > > UPDATE t SET b=NULL WHERE b='NULL'; > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] .import command/NULL values
On 16 Mar 2016, at 9:02am, Simon Slavin wrote: > No. Use one of these two methods. I'm sorry. I read your 'import' but I thought you meant using INSERT. If you are using the shell's ".import" command then Clemens is right. You are importing from a text file, and there is no way to put a NULL in a text file. After your import you should do something like UPDATE t SET b=NULL WHERE b='NULL'; Simon.
[sqlite] .import command/NULL values
On 16 Mar 2016, at 8:43am, Bernard McNeill wrote: > If it is required to import NULL values into an Sqlite table field, can > this be done simply by arranging that the before and after delimiters for > that field are consecutive? No. Use one of these two methods. CREATE TABLE t(a,b,c) It is required that column b contains NULL Method 1: INSERT INTO t (a, c) VALUES (1, 3) In this method you do not name column 'b' in the INSERT command. Therefore column b is set to NULL. Method 2: INSERT INTO t (a, b, c) VALUES (1, NULL, 3) In this method you name column b and put NULL into it. Simon.
[sqlite] .import command/NULL values
I couldn't find this issue documented anywhere. If it is required to import NULL values into an Sqlite table field, can this be done simply by arranging that the before and after delimiters for that field are consecutive? Example: For a three field table (all three fields text type), importing from a text file, using default delimiters, something like A||H would put A in the first field, H in the third, NULL in the second? And appropriate modifications to put NULL's into the first or last field? Best regards
[sqlite] .import command
Hi, I would like to continue a discussion started here: https://www.sqlite.org/src/tktview/c25aab7e7ea55c861313 First I assume the sqlite3 shell still has the related problem (I'm using Ubuntu, so not sure if the last version has fixed it). I would like to desagree of a comment posted on the ticket: >? Different programs do different things. There might be users that depend on the current behavior. If we fix this for the OP, it could cause problems for others. So it is unclear whether or not we should do anything for this. In the way sqlite shell currently works, users have to import a very strict formatted CSV file: no comma inside quoted string values. Implementing a way to allow commas to come inside quoted string values by no means will affect the behavior of current users relying on .import. This would be backward compatible evolution. Does anyone has an counter example? Finally, I'm not sure if the contribution sent on the last post was accepted on sqlite. Was it accepted? Does it solved the problem? Thank you so much for your attention, Leonardo Leite - "Esta mensagem do SERVI?O FEDERAL DE PROCESSAMENTO DE DADOS (SERPRO), empresa p?blica federal regida pelo disposto na Lei Federal n? 5.615, ? enviada exclusivamente a seu destinat?rio e pode conter informa??es confidenciais, protegidas por sigilo profissional. Sua utiliza??o desautorizada ? ilegal e sujeita o infrator ?s penas da lei. Se voc? a recebeu indevidamente, queira, por gentileza, reenvi?-la ao emitente, esclarecendo o equ?voco." "This message from SERVI?O FEDERAL DE PROCESSAMENTO DE DADOS (SERPRO) -- a government company established under Brazilian law (5.615/70) -- is directed exclusively to its addressee and may contain confidential data, protected under professional secrecy rules. Its unauthorized use is illegal and may subject the transgressor to the law's penalties. If you're not the addressee, please send it back, elucidating the failure."
