sqlite3 file import

Question

is here a way to import a file into table with ":" being the separator?

for example I have file test.txt with

value1:result1
value2:result2

want to use something like this on the CLI:

sqlite> .import test.txt table

and at the end I would have:

+--------------+
|value1|result1|
+------+-------+
|value2|result2|
+------+-------+

Answer 1

use .separator :

.separator STRING      Change separator used by output mode and .import

Here.

Answer 2

If you use .help, you might notice something interesting :

sqlite> .help
...
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
...
.separator STRING      Change separator used by output mode and .import
...

So, .import might be what you are searching for ;-)

Answer 3

Here's a problem, maybe someone else is getting it too. My data is saved in a tab-delimited text file so I can have commas in it. I import it with no problem BUT the data with commas saves with quote marks around it ("). The big problem is some of that data is url addresses so I can't just rip the commas out. It is possible to update the data once it's in the db to remove the quotes but there are thousands of rows of data so that's not practical.

How can I tell the import not to do this???