Re: Import data from an excel spreadsheet into an mysql database

[Michael Markel]

If you can't afford to buy navicat then you could always do it the old fashioned way with the load data infile command, thusly.

LOAD DATA INFILE 'c:/xampp/QryBillDateAnalysis.txt' into table excel;

steps:
1. create a tab delimited .txt file of the excel sheet you want to upload


2. create a table to store the data you will import

mysql> describe excel;
+----------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| billdate | date | YES | | NULL | |
| dayofmonth | int(11) | YES | | NULL | |
| groupdescr | varchar(50) | YES | | NULL | |
| billperiod | char(6) | YES | | NULL | |
| currentcharges | decimal(14,2) | YES | | NULL | |
+----------------+---------------+------+-----+---------+-------+
5 rows in set (0.04 sec)

3. execute LOAD DATA INFILE 'c:/xampp/QryBillDateAnalysis.txt' into table excel; on the
MySQL command line.


Where 'c:/xampp/QryBillDateAnalysis.txt' is the file to import

You can actually do the above much quicker than you would might suppose.