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
:
How to insert csv file data into mysql database
You can try below syntax if it works for you otherwise provide csv data:
LOAD DATA LOCAL INFILE 'C:/region_codes.csv' INTO TABLE `region_codes` FIELDS ESCAPED BY '\\' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
If above syntax does not work then export data by below command again and import by below given command.select * into outfile 'C:/region_codes.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' from `region_codes`;
Now use below command (to ignore column heading line)LOAD DATA LOCAL INFILE 'C:/region_codes.csv' INTO TABLE `region_codes` FIELDS ESCAPED BY '\\' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
Note: If data is prepared manually then need to correct it manually.If still not work then attach your csv data to check exact problem.
how to Export data from csv file into mysql database?
I think you mean IMPORT
., here's how:
load data local infile 'yourCSVfilepath.csv' into table tableNameHERE
fields terminated by ','
optionally enclosed by '"'
lines terminated by '\r\n'
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 MySQLEDIT
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:
Therefore, you can use the following statement: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;
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
Iconv_Strlen Function Causing Execution Timeout, Running on Mamp
PHP Utf-8 to Windows Command Line Encoding
Type Hinting: Default Parameters
Get the Get Variables from a Url String
Use PHP to Check If Page Was Accessed with Ssl
Resource Interpreted as Image But Transferred with Mime Type Text/HTML - Magento
How to Deal with "Method Not Found in Class" Warning for Magically Implemented Methods
Imagemagick Brew Installation with PHP Module in MAC Os X
Call to Undefined Function Curl_Init() - with Wamp
Different Timezone_Types on Datetime Object
Echo 'String' While Every Long Loop Iteration (Flush() Not Working)
Laravel 4 - Including a "Partial" View Within a View (Without Using Blade Template)
How to Reference a Column Other Than 'Id' for a Joincolumn
Prevent Back Button After Logout
Pdo Prepared Statements for Insert and on Duplicate Key Update with Named Placeholders