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? 
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?
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.
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)