CODEWITHSUNDEEP
mysqlexcel
themeparkfocus
themeparkfocus

Reputation: 187

adding data from excel to existing table in MySQL

I have an existing table on MySQL with 7 fields - 4 are integer(one acting as primary key and auto incremete) and 3 text. This table already has data.

I am collecting new data in Excel and wish to bulk-add the data to the Mysql table. Some of the cells in Excel will be blank and need to show as null in MySQL and the primary key won't be part of excel, I let MYSQL auto add it. Previously I was manually adding each record through PHPmyadmin but it is far too time consuming (I have 1000's of records to add).

How do I go about this in terms of setting the Excel sheet in the right way and making sure I add to the exsisting data instead of replacing it? I have heard CSV files are the way? I use PHPmyadmin if that helps.

Thanks

Upvotes: 1

Views: 6930

Answers (1)

Barranka
Barranka

Reputation: 21047

If you want to append data to a MySQL table, I would use .csv files to do it.

In MySQL:

load data local infile 'yourFile'
into table yourTable (field1, field2, field3)
fields terminated by ',' optionally enclosed by '"'
lines terminated by '\n'  -- On windows you may need to use `\r\n'
ignore 1 lines;           -- Ignore the header line

Check the reference manual: http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Upvotes: 2

Related Questions