Import data from excel to database

[rei27]

Is it impossible to import excel data to database's table? I know that it can be done by using MySQL command LOAD DATA INFILE 'path/file.txt' INTO TABLE your_table; , but how the data match with the table field? Is it necessary to declare it first?

[Phill Pafford]

I have used this in the past


LOAD DATA LOCAL INFILE 'C:\\Documents and Settings\\My Documents\\STATIC_DATA\\Data.CSV'
INTO TABLE table_name
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(Field1, Field2, Field3, Field4, Field5);


Yes the fields must match the table structure but after you import the data it's easy to create a table with just the data you need.

Something like this


CREATE TABLE new_table_name SELECT * FROM old_table_name;


or


CREATE TABLE new_table_name SELECT Field1, Field3, Field5 FROM old_table_name;