By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,691 Members | 1,143 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,691 IT Pros & Developers. It's quick & easy.

uploading excel file to mysql

P: 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
Share this Question
Share on Google+
6 Replies


ronverdonk
Expert 2.5K+
P: 4,258
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

vpmurdan
P: 25
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
Expert 2.5K+
P: 4,258
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

P: 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

P: 6
hi
thankz to all I do it successfully
Feb 17 '07 #6

vpmurdan
P: 25
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.