We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

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

asked 2020-07-19 18:50:09 +0200

kasloman gravatar image

updated 2020-07-19 18:54:30 +0200

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!!

edit retag flag offensive close merge delete

Comments

"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).

robleyd gravatar imagerobleyd ( 2020-07-20 01:08:54 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2020-07-19 19:31:14 +0200

CivFan gravatar image

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.

edit flag offensive delete link more

Comments

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.

kasloman gravatar imagekasloman ( 2020-07-20 00:53:04 +0200 )edit

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.

CivFan gravatar imageCivFan ( 2020-07-21 05:22:24 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-07-19 18:50:09 +0200

Seen: 131 times

Last updated: Jul 19 '20