What are the steps required to convert excel to MySQL data and what mechanisms are used in transferring the data while maintaining the order of data between the two platforms?
To maintain the order of the data from Excel to MySQL, we use a CSV or Comma-Separated Value file. In a CSV file, each row contains a sequence of column headings or values separated by a comma. The commas are used to maintain the file structure. Note there is no space after the comma, unlike standard sentence punctuation.
So, first save your Excel data as a .CSV file. To do this select File, then Save As and choose CSV (Comma delimited), do not choose the MAC or MS-DOS version if CSV. Note this conversion to CSV does not support Excel documents of more than one workbook so you will have to do this multiple times if you have multiple workbooks in your excel file. You can then check the CSV file by opening it in Notepad.
So, in this example let’s say we have a CSV file saved to the root of the C: drive as C:test1.csv and we want to import into a table called table 1.
We then start the MySQL command prompt and type the following command.
LOAD DATA LOCAL INFILE ‘C:test1.csv’ INTO TABLE table1 FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘n’;
Before transferring the data, be aware that MySQL does not like empty cells so it is good practice to find and replace empty cells with NULL values, NULL isn’t actually a string so will have to exist in the CSV separated by commas but not in double quote marks, used Find and Replace to change this in Notepad.Â
