How to transfer data from excel to mysql? 1

[DavidTigerch]

Hi all. I got a huge list of data in excel(7000 records) . I want to transfer it to mysql database. Could any one show me how to do it using a fast and easy method.Thanks

 

[mbrooks]

Step 1:
Export the Excel data to a .CSV

Step 2:
Create the MySQL database table (I'm assuming that you already know how to do this)

Step 3:
On the MySQL command line do

LOAD DATA LOCAL INFILE 'excel.csv' INTO TABLE table_name FIELDS
TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n';

M. Brooks

http://mbrooks.info

 

[DavidTigerch]

mbrook thanks. I am using phpmyadmin and excel 2002 and i could not find the export!! Furthermore, can i just paste the command in phpmyadmin? my excel sheet has only one column but in my mysql db i want to give each row on unidqu id with auto increment how i can do that in transfer phaste?Thanks

 

[mbrooks]

In Excel do a 'Save As' and make sure you select .CSV (Comma Seperated)

Then using MySQL create your table like this

CREATE TABLE `table_name` (
  `id` int(10) NOT NULL auto_increment,
  `column` varchar(55) NOT NULL default '',
  PRIMARY KEY  (`id`)
);

M. Brooks

http://mbrooks.info

 

[DavidTigerch]

I tried this and i got error :

LOAD DATA LOCAL INFILE 'c:\albumscsv.csv' INTO TABLE sheet1 FIELDS
TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n'

error;

MySQL said:  

#2 - File 'c:albumscsv.csv' not found (Errcode: 2)

 

[mbrooks]

Oh.. you are working on Windows.

It seems the slashes (C:\\albumscsv.csv) are being stripped by the PHP program you are using.

The only thing I can suggest from here is to run this action on the MySQL CLI (command line).

M. Brooks

http://mbrooks.info

 

[DavidTigerch]

I tried

LOAD DATA LOCAL INFILE 'c:\\albumscsv.csv' INTO TABLE sheet1 FIELDS
TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n'
[code]
and i got garbage in my db !!! also when i created my csv under save as there were no csf so i had to do like this "filename.csv" and save it. Do u think that is problem i get garbage in my db?

 

[mbrooks]

Hmmm.. The garbage is Excel's proprietary format.

You should try to re-save that data using the .CSV option within Excel.
Unless you are using an archaic version (pre 97 era). Check the help menu.

M. Brooks

http://mbrooks.info

 

[DavidTigerch]

I found .csv it was down the dropdown box i missed . When i save it i get this window:

shoul i say yes or no ? Thanks

 

[mbrooks]

Yes!!! Make sure you have a backup of the original though.

M. Brooks

http://mbrooks.info

 

[mbrooks]

By clicking yes you are removing any Excel formatting.
To verify that the file was successfully saved as a .CSV. Open the file in Notepad.

M. Brooks

http://mbrooks.info