Loading data from Excel to MySQL

Databases
1

Good day,

I have just created a new MySQL datyabase and its tables. I have data in Excel files, and I want to load it to the database.

Is there a way to do this?

Thanks a lot!!!

2

Save your excel spreadsheet as a tab delimited text file - it is an option under Save as… (NOT the commonly recommended csv file)

then use this sort of command

load data local infile “C:/Documents and Settings/dr john/My Documents/logpile/FlightDataExport_2011-09-02.txt” into table myflights;

Adjust the above query as required, table by table.

3

Dr. John, why do you recommend Tab over Comma separated? I generally use comma separated and have never had a problem with it.

4

I use tab delimited because lots of data can have a comma in it.
eg 25, My Street, mySuburb
Or
caravan parking, glider rental, and trailer parking fees.
OR
sent one email, two letters, and even phoned him, and still not payed

so these would mess up the insert. But the data that I insert regularly, which often has commas in the actual data, never has a tab, and I think that most other people are very unlikely to encounter a tab in their data.

5

Hi Dr. John,

Thanks a lot for your help!!

Does the Excel file have column names or just the data? (I guess in the same order as the table)
Should I include also the autoincremental field (table Primary Key)

Thanks!!!

6

And one last extra silly question:

From where should I execute load data local infile instruction?

Thanks!!!

7

Hi again,

I was ablt to load the data as you suggested.
The only probles I had is some information includes special characters like ü, and these names were cut.

How can I fix this?

Thanks a lot!!!

8

ALL SOLVED!!!

Had to save .txt file with UTF-8 format!!

Thanks a lot!!!