How to Import Excel file to MySql Database


1.     Open the Excel workbook, then go to File -> save as “CSV (Comma delimited).” If there is a warning that says “Do you want to keep the workbook in this format?” Click “yes” and close the workbook. Click “NO” if there is a warning that asks “Do you want to save the changes made to yourworkbook.csv?”

  1. Go to phpmyadmin and create a database using the same name as your workbook.

    To do this in XAMPP, go to http://localhost/phpmyadmin and then, under the main page, look for the database link. Click that; it will take you to a page that asks you for a database name. Type the name of the database and then leave it as default “collation.”

  2. Create the name of the first database table. Note that we can export one worksheet at a time to MySQL. This is true whether we’re doing it offline (using the XAMPP Localhost phpmyadmin panel) or online (using the actual MySQL server).
  3. Then “Enter the number of fields,” which is equal to the number of columns of the Excel worksheet table. For example if your Excel worksheet table has three fields (name, email and address), then enter 3 as number of fields.
  4. MySQL will then ask you to enter the field name. Enter it exactly as you have it in the Excel worksheet. Then on the type, change it from VARCHAR to TEXT. Leave everything else the way it is, and then click “SAVE.”
  5. The next step is to click “IMPORT” (you can see this under “Structure tab” in phpmyadmin). Refer to the guide below:

Location of the text file: (click “Browse” and navigate to the .csv file you need to import)

Character set of the file: Set it to “utf8”

Check “Allow interrupt of import in case script detects it is close to time limit.” This might be good way to import large files, however it can break transactions.

Number of records (queries) to skip from start: 1

Format of imported file: CSV with LOAD data

Uncheck:

Replace table data with file

Ignore duplicate rows

Fields terminated by: , (change it to comma)

Fields enclosed by: (change it to blank, or empty it)

Fields escaped by:

Lines terminated by: auto

Column names: (change it to blank, or empty it)

Use LOCAL keyword: Check this one

Finally when everything is set, click “GO.” The csv file will then be exported to your first MySQL database table. You can then click “BROWSE” in the phpmyadmin navigation to see the exported data. You can now see the table in MySQL format.

If you have other worksheets to be exported as another MySQL table, repeat the above procedure.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: