Import an Excel File into MySQL database..

I want to import an excel database into MySQL

Can this be done...

And if yes, how?

Arthur.

 

Use phpMyAdmin. Save Excel as CSV, then use phpMyAdmin's import function.

 

When I tried it, it dumped all the fields into ONE field in the mySQL. Is there a format for the first line in CSV file to define fields?

Arthur.

 

You didnt change the escape character from a ; to a , i thinks.....loading data into mysql from a CSV file is the industry standard for affiliate catalogues and things.

 

i think, its better you use this software, use there free option...

http://www.convert-in.com/xls2sql.htm

Jalika

 

Hi,

If you want from mysql command line just look at this thread : http://www.webmasterstalks.com/index.php/topic,158.0.html

Regards

 

I import my excel file into access then export the file from MS access to txt file and use a |pipe to seperate fields. It works pretty well for me.

 

You can also use LOAD DATA INFILE Syntax to load flat files into MySQL tables.

For example:

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

 

if you are under a windows system and is not working try to replace '\n' with '\r\n'

 

LOAD DATA INFILE - does not work on Netfirms, great $10 per year for 1 GB/750 GB, but realy very hard to upload even 5 MB mySQL :-(

 

Thanks esnstudio that is a great tip

 

1. create a table in MySQL with appropriate columns.
2. Create a csv file of the data you want to import. (I tried with no headings in the csv file).
3. While importing in MySQL, fields terminated by ; replace by ,
4. hit Go and you should have all the data in MySQL.

e.g.
table user: user_id (primary key, auto increment), user_first_name, user_last_name

CSV should look like (without headings):
1 John Smith
2 Alan Smith

works like a charm.

 

If you would like to pre-validate and rollback on errror - you might like check out this post/tip on CSV import from Excel to MySQL

leansoftware.net/tabid/123/g/posts/t/17/How-to-validate-and-upload-CSV--comma-seperated-value--data-to-a-database.aspx

Could save you some time
Thanks

 

I definitely would not install someone else's software they're pushing. It's almost always the developer trying to get recognition or someone making a commission.

It was probably stated above that you use phpmyadmin, import as csv- make sure you have your mysql structure already made to match the excel file data you want to import, change delimiter to , and import. Lots of examples in more detail online if this doesn't suffice. No use reinventing the wheel when tutorials exist everywhere for this basic action.

 

You could just use this FREE online converter http://excel2sql.esy.es/ to solve your problem easy for a few secconds.

 

You could just use this FREE online converter http://excel2sql.esy.es/ to solve your problem easy for a few secconds.

 

you have to create the blank table with all the fields before trying to load the data