New working place, new company but still wants to blogging with Openshell. Yes friends, I changed my company to C-DAC, Pune.
Target Audience: DB Administrators, DB Developers, System Integrators
What Should You Know Already? SQL query knowledge
The very first task assigned to me in the new company was to import MS Excel file (.xls or .xlsx) to MySQL table. Usually MS Excel provides many options to import data from other sources like XML or using data source. Now, the data from excel file has to be exported to a form which MySQL could read and insert into existing relational tables. You can think about .sql file. Usually, MySQL tables can be imported by SQL script (.sql) files. SQL script file contains the DDL and DML queries. But in our case, we need to load the rows of excel file into a table. The Microsoft Excel file can be converted as a delimited file called as .csv (Comma Separated Values), from csv file we can load the data to MySQL relational table.
Excel to CSV
Just open the excel file, choose File -> Save As -> Other formats. (Figure 1.) In the save dialog box under file type choose .csv, give a name. Note that only the current sheet can be saved as .csv file not the entire workbook. Open the .csv file using any simple text editor. You can see the content; each row is delimited by a carriage return (ie. \\r\\n in Windows, \\n in Unix) and each field is separated by a comma.
Figure 1. Save As (other formats)
CSV to MySQL
Once the .csv file is created, now you can use the following command to import into your table. Make sure the target table’s field count is equivalent to .csv file field count.
Example:
Assume student_details.xls file contains 4 rows of student details such as registration number, name, address and gender (as shown in Figure 2), that need to be loaded into an existing MySQL table, might be student table.
Figure 2. Student Details Excel Sheet
Save the file as .csv format. Choose File->Save As->Other Formats . Give a name, say students.csv and choose Comma Delimited (.CSV) for save as type as shown in figure 3.
Figure 3. Save student_detail.xls file as students.csv
Open students.csv file in Wordpad, you can see each row including the header ( you can delete the first line of .csv file as it is header) is separated by new line and each field is separated by , (comma).
Now, think about the target table. In my example the following is the struture of student table in MySQL.
| Field | Field definition |
|---|---|
| regno | int not null primary key auto_increment |
| name | varchar (50) |
| address | tinytext |
| gender | varchar(6) |
Assume the students.csv file is saved in My Documents of current user, then the LOAD command will be:
If you have any doubts, feel free to send comments or queries. Happy programming.