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
:
Import CSV file directly into MySQL
You can create a script to parse your csv file and to put the data into db.
Something like:
$path = "yourfile.csv";
$row = 1;
if (($handle = fopen($path, "r")) !== FALSE) {
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$row++;
$data_entries[] = $data ;
}
fclose($handle);
}
// this you'll have to expand
foreach($data_entries as $line){
$sql = "INSERT INTO ..."
$db->execute($line);
}
Fastest way to import csv file into MYSQL
You can insert data this way. This is default way to insert rows in a table.
$deleterecords = "TRUNCATE TABLE discount"; //empty the table of its current records
mysql_query($deleterecords);
//readfile($name);
//Import uploaded file to Database
$handle = fopen($name, "r");
$i=0;
$ins = "INSERT into discount(id,title,expired_date,amount,block) values ";
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
if($i>0){
$import .= $ins."('".$data[0]."','".$data[1]."','".$data[2]."','".$data[3]."','".$data[4]."'),";
//imports data serially to the allocated columns.
}
$import = rtrim($import,',');
mysql_query($import) or die(mysql_error());//query
$i=1;
}
fclose($handle);
//closing the handle
// print "Import done ";
?>
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)
Importing data from CSV file into MYSQL
If both files are in same folder then try using the following command:
load data local infile './covidDeath.csv'
Let me know if you still face any issue.
Related Topics
Should I Use Curly Brackets or Concatenate Variables Within Strings
Convert Var_Dump of Array Back to Array Variable
How to Add Exif Data to an Image
PHP Difference Between Array() and []
What Do the "=&" and "&=" Operators in PHP Mean
Eloquent Orm Code Hinting in PHPstorm
PHP X86 How to Get Filesize of ≫ 2 Gb File Without External Program
How to Write Specific CSS For Mozilla, Chrome and Ie
PHP: How to Get File Creation Date
Match A^N B^N C^N (E.G. "Aaabbbccc") Using Regular Expressions (Pcre)
Ip Address of the Machine in PHP Gives ::1 But Why
PHP Fatal Error: Class 'Pdo' Not Found
Symfony2 - Force File Download
How to Sanitze User Input in PHP Before Mailing
Filter Values from an Array Similar to SQL Like '%Search%' Using PHP