CODEWITHSUNDEEP
phpmysqlcsvimport
ehmad
ehmad

Reputation: 2623

Import CSV file directly into MySQL

i want to import csv file into mysql.. something like:

load data local infile 'uniq.csv' into table tblUniq
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(uniqName, uniqCity, uniqComments)

http://www.tech-recipes.com/rx/2345/import_csv_file_directly_into_mysql/

but column names in csv and that in database table are different what should i do? i want to do it programmatically..

Upvotes: 8

Views: 17179

Answers (6)

Sourav Basak
Sourav Basak

Reputation: 486

The below statements import the data from csv file into users table.

LOAD DATA INFILE 'c:/xampp/example.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Upvotes: 0

Sanket Shinde
Sanket Shinde

Reputation: 1

I created the table with the following MySQL query,

create table sales(row_id int not null, order_id varchar(20),order_date DATE, ship_mode varchar(30),customer_id varchar(10), customer varchar(15),segment varchar(30),profit double(10,4));

Then I checked with following MySQL query,

mysql> show columns from sales;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| row_id      | int          | NO   |     | NULL    |       |
| order_id    | varchar(20)  | YES  |     | NULL    |       |
| order_date  | date         | YES  |     | NULL    |       |
| ship_mode   | varchar(30)  | YES  |     | NULL    |       |
| customer_id | varchar(10)  | YES  |     | NULL    |       |
| customer    | varchar(15)  | YES  |     | NULL    |       |
| segment     | varchar(30)  | YES  |     | NULL    |       |
| profit      | double(10,4) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

Then I checked SQL mode with the following query,

mysql> SHOW VARIABLES LIKE 'sql_mode';

Answer was

+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+

Then I changed it as follows,

SET SQL_MODE='';

Then finally run following SQL query to load data from CSV into the table,

mysql> load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/sales_data.csv" into table sales fields terminated by ','lines terminated by '\r\n' ignore 1 rows;

It worked for me but save CSV file into C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/

Upvotes: 0

soni pushkar
soni pushkar

Reputation: 21

Try this, this is working fine for me.

ini_set('auto_detect_line_endings',TRUE);

$csv_data=array();

$file_handle = fopen($_FILES['file_name']['tmp_name'], 'r');

while(($data = fgetcsv($file_handle) ) !== FALSE){

 $update_data= array('first'=>$data['0'],
                      'second'=>$data['1'],
                      'third'=>$data['2'],
                       'fourth'=>$data['34']);

// save this array in your database
}

Upvotes: 0

shantanuo
shantanuo

Reputation: 32276

If you want to load only the first 7 characters of the last column from the CSV file into the uniqComments column of the table, then you can do something like this...

load data local infile 'uniq.csv' into table tblUniq fields terminated by ',' 
enclosed by '"' lines terminated by '\n' (uniqName, uniqCity, @seven_chars) 
set uniqComments=left(@seven_chars,7)

Upvotes: 3

Pekka
Pekka

Reputation: 449783

but column names in csv and that in database table are different what should i do?

Not a problem. You can specify which CSV column gets imported into which database column.

LOAD DATA INFILE syntax

By default, when no column list is provided at the end of the LOAD DATA INFILE statement, input lines are expected to contain a field for each table column. If you want to load only some of a table's columns, specify a column list:

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

What I like to do when I find the INFILE syntax too complicated is use a graphical client like HeidiSQL to click together the proper column order (it has a graphical preview) and copy+paste the generated SQL query.

Upvotes: 11

Elzo Valugi
Elzo Valugi

Reputation: 27886

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);
    }

Upvotes: 3

Related Questions