469,950 Members | 1,924 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,950 developers. It's quick & easy.

uploading excel file to mysql

6
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.
Feb 14 '07 #1
6 7295
ronverdonk
4,258 Expert 4TB
What usually works for me is statement
Expand|Select|Wrap|Line Numbers
  1. 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:
Feb 14 '07 #2
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

Expand|Select|Wrap|Line Numbers
  1. LOAD DATA INFILE 'test.txt' INTO TABLE aa FIELDS TERMINATED BY ',' lines terminated by '\n'
  2.  


Prakash
Feb 15 '07 #3
ronverdonk
4,258 Expert 4TB
vpmurdan: is there any reason why you would take such a (manually performed) detour to get the same results?

Ronald :cool:
Feb 15 '07 #4
chiya
6
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

Expand|Select|Wrap|Line Numbers
  1. LOAD DATA INFILE 'test.txt' INTO TABLE aa FIELDS TERMINATED BY ',' lines terminated by '\n'
  2.  


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
Feb 17 '07 #5
chiya
6
hi
thankz to all I do it successfully
Feb 17 '07 #6
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.
Feb 20 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

10 posts views Thread by John Smith | last post: by
4 posts views Thread by newsprofile | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.