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?
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?
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.
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
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
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)
This topic is now archived. It is frozen and cannot be changed in any way.