upload script for multiple rows/columns

[Bazzaah]

Not sure if this is the right forum, but I have a database that I will need to populate with a large number of rows (2000+).

 

I have written a PHP script that uploads individual entries.

 

Is it possible to use something like a spreadsheet where I can set out the rows/columns as they will appear in the database, and then upload in one go rather than uploading each row individually?

 

Thanks for any observations and/or help.

[tomfmason]

Yes, this can be done easily by exporting your excel file to a csv and then importing that directly using mysql. Here is an example of what the query would look like:

 

LOAD DATA LOCAL INFILE '/path/to/your.csv' 
INTO TABLE `table` 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n' 
(`field1`,`field2`,`field3`);

 

If I am not mistaken(it has been a while since i have done this) you will need to remove the top line from the cvs otherwise the column list at the top will also get inserted as a new row

[cypher86]

sure you can.. you just need to build a parser for that, then take a line at a time, explode it by special characters which separte every column and then run the query. all of this untill the end of the file.

[Bazzaah]

Ok thanks for the ideas there. I'll give the .csv a go.

 

The db has an auto_increment to number each row - would that be covered or would I need to number each entry within the spread?

[tomfmason]

as long as you don't have an id column in the csv it will automagically insert the id for you like normal.

[Bazzaah]

nice one, thanks.

 

 

[Bazzaah]

The script worked just fine thanks - the entries were all surrounded by quotes though.

 

Any ideas how to avoid this?

 

[tomfmason]

After reading the docs I see that I forgot to add "ENCLOSED BY". This should solve the quote problem

LOAD DATA LOCAL INFILE '/path/to/your.csv' 
INTO TABLE `table`
OPTIONALLY ENCLOSED BY '"' 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n' 
(`field1`,`field2`,`field3`);

 

[Bazzaah]

Thanks - I really appreciate your help.

 

I'll read the manual.