database management
Binita12
ImportCSVintoMySQL41.docxIndividual Project / Assignment 2: How to Import CSV into MySQL
Save CSV in Excel
The following steps give you an example on how to save your data in Excel in a CSV file format and then import into your MySQL database.
1. Open the Excel workbook that contains the data you want to export.
2. Check that the data appears at the top of the sheet. The field headings should be on row 1 and the first field in column A. It is recommended that you modify the column headings in the Excel workbook containing sample data (either remove spaces or replace spaces with an underscore) before you export to a CSV file. Dates should also be formated in a YYYY-MM-DD format with the year first before exporting to a CSV file.
3. Select the worksheet that contains the data to export.
4. Excel 2010: From the File tab, choose Save As, then change the Save as Type to CSV (Comma Delimited)
5. Select CSV (Comma delimited) (*.csv) as the Save as Type (shown below).
6. Type a name for your CSV file, navigate to the folder where you want to save, and then click on Save.
7. Repeat steps 2 to 5 above to save each of your other sheets containing your data.
8. Open the CSV files in NotePad to check the data.
Each field should be separated by a comma and each record separated by a carriage return and a line feed. If there is any record that contains a comma as part of the data then the field value will be delimited in double quotation marks.
If you see additional commas at the bottom of the CSV file or if each record has one or more commas at the end then it is likely that there were blank rows or columns that have been exported as part of the data. An easy way to test in Excel is from the worksheet containing the data press [Ctrl End] to move the active cell to the bottom right corner of the worksheet. If the active cell is positioned below the last record in the list or to the right of the last column then make a note of this bottom right corner position and then drag on the row numbers to select blank rows up to this cell and delete. Do the same for any blank columns up to the same cell position. Repeat this test for all the other sheets containing data. Save the Excel workbook and then export each sheet to a CSV file.
Import CSV into your table in MySQL
Note that the design of your table in MySQL must match the format and column order of your CSV file. For example your subject CSV file has
|
CustomerID |
Customer Name |
Customer Phone |
then the MySQL table should have the same design. The alternative is to import each CSV file into your database and then rename the table and check the data types for each field.
1. Start the MySQL database server and also Apache web server.
2. Click Admin to go into PHPMyAdmin.
3. Select your database.
4. Select the table that you want to import data into.
5. Click the Import tab
6. Click Browse and then navigate to find your .csv file.
7. Change the file format to CSV.
8. Note that depending on what version of XAMPP you are using you can skip rows or click Column Headings so that MySQL does not attempt to import the column headings into your table (which will cause an error).
9. Click Go to import the data.
10. Click the table name to check that the data has imported successfully.
2
