Excel to MySQL Possible?

[onedee]

Hey guys,

I have tons of Excel data to add to a MySQL database and I'm looking for a quick way around doing the job manually due to time constraint. Is there a way to upload Excel data into MySQL? Any programs, plugins?

[r937]

Is there a way to upload Excel data into MySQL?

yes, just export from excel to a csv file, and import to mysql using the LOAD DATA INFILE command

[onedee]

yes, just export from excel to a csv file, and import to mysql using the LOAD DATA INFILE command

Hello r937, thanks for dropping by, I will have to give that a try. I hope importing a CSV file will also take care of the ID's, etc?

[r937]

take care of the ids?

[onedee]

take care of the ids?

For some reason the ID's in some of the tables are not in order and I'd assumed it's a randomly assigned number. But I see now I can actually add an ID to each row and that just puts it in order within the table.

[r937]

clarification: the rows in a table are in undefined order

the only order you can impose is the order of presentation when you use the ORDER BY clause of a SELECT statement

[Angrypoonani]

For some reason the ID's in some of the tables are not in order and I'd assumed it's a randomly assigned number. But I see now I can actually add an ID to each row and that just puts it in order within the table.

I may be misunderstanding the question... But you can organize all of your data beforehand if you're importing from an excel file. Organize all your data first. I usually save the data as a CSV then import it with something along the lines of:
IMPORT DATA INFILE 'data.csv' INTO TABLE my_table​ FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE LINES 1 ( colA, colB, ... , colX );

Of course... your syntax may be different depending on your file type and what OS it is saved in. In other words, it's not a copy paste situation

[onedee]

Thanks guys, here is what I will be doing.

1. Arrange everything in Excel, give columns and rows the same title as the columns & rows in the database
2. From PhpMyAdmin - navigate into the respective DB table
3. Click import and from the import screen set these options:

a. Format: CSV
b. Format-Specific Options: NONE
c. Select (Do not use AUTO_INCREMENT for zero values)