Menu
I want to import csv file into SQLite db using
sqlite> .separator ,
sqlite> .mode csv data
sqlite> .import test.csv data
where data is the table name with three columns, just like the file.
The file has some string value that are encapsulated using double quotes.
Some of the string values have commas in them (actual example from the file "Bond\, James") which should be treated as a single column, but SQLite produces an error
Error: test.csv line 2: expected 3 columns of data but found 4
How can I make SQLite import these values correctly?
624
1. Enclose the text which has delimiters(comma) as a part of the text with double quotes/single quote. 2. When loading the source csv file, under the source tab, select Source Object > Formatting Options > Text qualifier > Double Quote / Single quote accordingly.
Using the "From Text" feature in Excel Click the Data tab, then From Text. Select the CSV file that has the data clustered into one column. Select Delimited, then make sure the File Origin is Unicode UTF-8. Select Comma (this is Affinity's default list separator).
First, from the menu choose tool menu item. Second, choose the database and table that you want to import data then click the Next button. Third, choose CSV as the data source type, choose the CSV file in the Input file field, and choose the ,(comma) option as the Field separator as shown in the picture below.
I know this is a bit old, but this was the first relevant google search result, so I wanted to share my solution.
Use a different separator, and remove the quotes around values.
sed -i -e 's/","/|/g' -e 's/"$//g' -e 's/^"//g' file.csv
sqlite> .separator "|"
sqlite> .import file.csv tablename
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
