SQLite Forum

Set diferent Quote Character
Login

Set diferent Quote Character

(1) By Matias (vikingoarg) on 2020-09-04 15:18:41 [link] [source]

Hi all I am trying to import a file into a DB and I need to set a different text qualifier. Is there a command for this? Something like that: .quote "*"? Thank you very much Regards

(2) By Richard Hipp (drh) on 2020-09-04 15:35:25 in reply to 1 [source]

Your question is unclear. Are you trying to use the .import command from the CLI to read file into a table? If so, perhaps you are looking for the ".separator" command.

Perhaps you could show an example of two or three lines of your input text in a follow-up post, so that we can better understand what you are trying to do.

(3) By Matias (vikingoarg) on 2020-09-04 17:27:21 in reply to 2 [link] [source]

Hi Richard,

Yes im using the .import command from the CLI to read file into a table.

 .mode csv

 .separator "|"

.import Fan_extrac_ccontac_26082020.txt BS_CCONTACT_ALL

And I get the following error in some fields:


Fan_extrac_ccontac_26082020.txt:81862: unescaped " character
Fan_extrac_ccontac_26082020.txt:16780: unescaped " character


219165|3||||||||||||17009||X|140349529|3794041321||
88545067|4|FAVIAN AGUSTIN|MARTIN|S/N -- (PREG POR FLIA DE "TAPIOLA")|S/N||3705|Catamarca|BARRIO LAS PARCELAS||X||9424||||||
22314319|1|JUAN CARLOS|FORNAS|AVDA GRAL LAS HERAS|3395|9   "B"|C1425ASK|CAPITAL FEDERAL|(RUGGIERI Y PACHECO DE MELO)|48025563||X|3137|||||Sr.|

I need to set something Like: quote = none, 
The process detect " as a text qualifier, and i don't want that.


Sorry, my english is not good.
Thank you very much
Regards

(4) By Larry Brasfield (LarryBrasfield) on 2020-09-04 17:43:16 in reply to 3 [link] [source]

You should see RFC4180, aka "Common Format and MIME Type for Comma-Separated Values (CSV) Files", and perhaps this Wikipedia article on comma-separated values. Your fields which have embedded doublequote characters do not belong in legitimate CSV. You will need to either get your data into a known format (such as CSV is known to SQLite) or write an importer or translator for your special format. The SQLite extension for CSV does not provide options for eliminating the special meaning of doublequote.

(5) By Matias (vikingoarg) on 2020-09-04 19:34:12 in reply to 4 [link] [source]

Ok I understand.
Thank you very much for the information
Regards
Matias

(6) By Trudge on 2020-09-05 15:37:52 in reply to 3 [link] [source]

You may have to mangle your input data if possible to replace double quotes with single quotes. I had a situation where I wanted output delimited by the pipe character and did this:

sqlite> select * from keywords limit 20; sqlite> .output /Users/user/Desktop/fubar.txt sqlite> .separator | sqlite> select * from keywords limit 20;