uploading excel file to mysql

[chiya]

plz anybody help me in uploading data from excel file to mysql database. data in excel sheet is in vertical format not horizontally. I mean to say that the columns of mysql tables are in rows in excel.

[ronverdonk]

What usually works for me is statement

LOAD DATA INFILE 'test.txt' INTO TABLE aa FIELDS TERMINATED BY '\n';

Hereby it treats each line as a value and fills all columns in a row until all are filled, then it starts a new row.

This is based on the MySQL manual at LOAD FILE

Ronald :cool:

[vpmurdan]

Hi.
I would personally prefer the following solution:


* Convert your rows to columns and vice-versa in the excel file. This may be done by selecting and copying all your data; click on Edit->Paste Special. Tick 'Transpose'. Paste the data in a new sheet so that you don't lose your data.

* Save the excel file as a 'Comma-separated values' text file (with extension .txt).

* load the file in mySQL using

LOAD DATA INFILE 'test.txt' INTO TABLE aa FIELDS TERMINATED BY ',' lines terminated by '\n'

Prakash

[ronverdonk]

vpmurdan: is there any reason why you would take such a (manually performed) detour to get the same results?

Ronald :cool:

[chiya]

Hi.
I would personally prefer the following solution:


* Convert your rows to columns and vice-versa in the excel file. This may be done by selecting and copying all your data; click on Edit->Paste Special. Tick 'Transpose'. Paste the data in a new sheet so that you don't lose your data.

* Save the excel file as a 'Comma-separated values' text file (with extension .txt).

* load the file in mySQL using

LOAD DATA INFILE 'test.txt' INTO TABLE aa FIELDS TERMINATED BY ',' lines terminated by '\n'

Prakash

hi

thanks. I do it, it is good for local server, as I save my csv files in the directory of mysql server where my database was created. But I have to do it for remote mysql server that is installed on remote linux machine. My database is already created there. How can I remotely save my csv files there.
error is:

'/var/lib/mysql/stdDB/test.csv' not found

[chiya]

hi
thankz to all I do it successfully

[vpmurdan]

vpmurdan: is there any reason why you would take such a (manually performed) detour to get the same results?

Ronald :cool:

The detour is b'coz, I could never import excel files directly to mysql. It is also said in the MySQL manual that import feature imports text files.
Hence the detour.