How do I properly import CSV data into a SQLite DB ...

[kasloman]

BEFORE I START CAN SOMEONE PLEASE TELL ME HOW TO FIND MY QUESTIONS IN THIS FORUM

I am writing a program in PHP which reads a CSV file produced by " Thunderbird email program" and writes an INSERT query to be executed by SQLite3. Typical raw data read from ‘thunderbird.csv’ file with PHP ‘fgets($fh)’ is like;
“US Post Office”,“Gov”,“Gov, US Post Office”,“2082632338”,
Which my program converts to:

INSERT INTO cdata (Headers …)
VALUES (“US Post Office”,“Gov”,“Gov, US Post Office”,“2082632338”, );

And got syntax error about “,”.

I did try using … ,null,null,null,null, in place of , and the query works.

However, I have 1000+ records that need to be processed and I can’t see writing ‘null’ in every blank space by using LibreOffice calc which I did to produce the INSERT query that did work. I think I have missed something along the way. I am switching over from MySql , it’s not easy!!

[robleyd]

“HOW TO FIND MY QUESTIONS IN THIS FORUM”

Click on your username at the right of your question, or under your comment, to go to your profile which contains a list of your questions and answers (if any).

[CivFan]

The SQLite INSERT documentation notes:

You can omit a column from the SQLite INSERT statement if the column allows NULL values.

I would update your PHP program to ignore blank columns.

[kasloman]

I don’t think that will work if you have blank fields in the midst of full ones. How does one update a PHP program to ignore blank columns.

[CivFan]

It depends on how you’re reading the CSV file, but I suggest you load the CSV properly into your PHP program using str_getcsv so you can dynamically create the INSERT statement.