How to import file into sqlite?

Question

On a Mac, I have a txt file with two columns, one being an autoincrement in an sqlite table:

, "mytext1"
, "mytext2"
, "mytext3"

When I try to import this file, I get a datatype mismatch error:

.separator ","
.import mytextfile.txt mytable

How should the txt file be structured so that it uses the autoincrement?

Also, how do I enter in text that will have line breaks? For example:

"this is a description of the code below.
The text might have some line breaks and indents.  Here's
the related code sample:

foreach (int i = 0; i < 5; i++){

  //do some stuff here

}

this is a little more follow up text."

I need the above inserted into one row. Is there anything special I need to do to the formatting?

For one particular table, I want each of my rows as a file and import them that way. I'm guessing it is a matter of creating some sort of batch file that runs multiple imports.

Edit

That's exactly the syntax I posted, minus a tab since I'm using a comma. The missing line break in my post didn't make it as apparent. Anyways, that gives the mismatch error.

Answer 1

I was looking on the same problem. Looks like I've found an answer on the first part of your question — about importing a file into a table with ID field.

So yes, create a temporary table without ID, import your file into it, then do insert..select to copy its data into your target table. (Remove leading commas from mytextfile.txt).

-- assuming your table is called Strings and
-- was created like this:
-- create table Strings( ID integer primary key, Code text )

create table StringsImport( Code text );
.import mytextfile.txt StringsImport
insert into Strings ( Code ) select * from StringsImport;
drop table StringsImport;

Do not know what to do with newlines. I've read some mentions that importing in CSV mode will do the trick (.mode csv), but when I tried it did not seem to work.