onedee — 2012-05-21T19:52:46-04:00 — #1
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 — 2012-05-21T21:43:02-04:00 — #2
yes, just export from excel to a csv file, and import to mysql using the LOAD DATA INFILE command
onedee — 2012-05-21T22:55:09-04:00 — #3
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 — 2012-05-21T23:55:37-04:00 — #4
onedee — 2012-05-22T11:39:28-04:00 — #5
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 — 2012-05-22T11:59:04-04:00 — #6
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 — 2012-05-22T14:32:01-04:00 — #7
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\
' 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 — 2012-05-22T20:18:01-04:00 — #8
Thanks guys, here is what I will be doing.
- Arrange everything in Excel, give columns and rows the same title as the columns & rows in the database
- From PhpMyAdmin - navigate into the respective DB table
- 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)