dumping an excel file into your Mysql Database

Another handy little bit of code. Sometimes people have data that they want incorporated into the shiny new web app you've designed for them. The following is the code necessary to dump a comma delimited file into a mysql table. You can get comma delimited files by exporting them from any spreadsheet/database program (like Excel or Access). A couple of things to consider, make sure your columns match up in both the csv file and the Mysql database, otherwise things get really messy. Also, if you have columns of data that include comma seperated items, they will be enclosed within " " marks in the csv file, and you'll have to specify this to mysql when dumping:


Protocol for loading excel files into mysql databases

Save the excel file as a comma de-limited (csv) format

Make sure that you don’t have any empty rows.

The following mysql statement will load the file into the specified table. Change "file.csv" to your file name. Change yourtableDB to the table you need to load into. Change "yourtableDB" to your mysql table name.

load data local infile 'file.csv' into table yourtableDB
fields terminated by "," <- This specifies that the fields will be separated by commas optionally enclosed by '"' <- in case you have multiple values per column
lines terminated by '\n' <- start a new record every time it encounters a new line in the csv file
(column1, column2, column3….etc); <- Specify the columns you wish to load into
An example of this in use

load data local infile '/Documents and Settings/Adminsistrator/Desktop/list.csv'
into table newtableDB
fields terminated by "," optionally enclosed by '"' lines terminated by '\n'
(authors, title, journal, year, number, pages, URL);

I hope this helps....if you run into problems, make sure the comma delimited file is properly formatted.