How do I import CSV file into a MySQL table?
The core of your problem seems to be matching the columns in the CSV file to those in the table.
Many graphical mySQL clients have very nice import dialogs for this kind of thing.
My favourite for the job is Windows based HeidiSQL. It gives you a graphical interface to build the LOAD DATA
command; you can re-use it programmatically later.
Screenshot: "Import textfile" dialog
To open the Import textfile" dialog, go to Tools > Import CSV file
:
Tool for importing CSV files into MySQL database?
MySQL has the LOAD DATA INFILE syntax:
LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
See the documentation about custom data handling.
How to import a CSV file into a MySQL table
Instead of writing a script to pull in information from a CSV file, you can link MYSQL directly to it and upload the information using the following SQL syntax.
To import an Excel file into MySQL, first export it as a CSV file. Remove the CSV headers from the generated CSV file along with empty data that Excel may have put at the end of the CSV file.
You can then import it into a MySQL table by running:
load data local infile 'uniq.csv' into table tblUniq fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(uniqName, uniqCity, uniqComments)
as read on: Import CSV file directly into MySQL
EDIT
For your case, you'll need to write an interpreter first, for finding the first row, and assigning them as column names.
EDIT-2
From MySQL docs on LOAD DATA
syntax:
The
IGNORE number LINES
option can be used to ignore lines at the
start of the file. For example, you can useIGNORE 1 LINES
to skip
over an initial header line containing column names:LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;
Therefore, you can use the following statement:
LOAD DATA LOCAL INFILE 'uniq.csv'
INTO TABLE tblUniq
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(uniqName, uniqCity, uniqComments)
Related Topics
Postgresql Update Multiple Tables in Single Query
What Determines the Locking Order for a Multi-Table Query
Identity_Insert Is Already on for Table 'X'. Cannot Perform Set Operation for Table 'Y'
Using Variables for Asc and Desc in Order By
Select the First 3 Rows of Each Table in a Database
SQL - Use a Reference of a Cte to Another Cte
How to Compare Two Columns in the Same Table
Create SQL Server Table Based on a User Defined Type
Conversion Failed When Converting from a Character String to Uniqueidentifier
Foreign Keys - What Do They Do for Me
How to Split Comma Separated String Inside Stored Procedure
Using Guid in SQLite Select Where Guid Is Stored in the SQLite Db as Binaries
How to Force Oracle to Change a Query's Plan Without Using Hints
Extracting Xml Sub-Tags from a Clob in Oracle via SQL
Duplicate Columns with Inner Join
Efficient Query to Split a Delimited Column into Separate Rows in Another Table