MYSQL import data from csv using LOAD DATA INFILE

Question

I am importing some data of 20000 rows from a CSV file into Mysql     Columns in the CSV are in a different order than MySQL table s columns  How to automatically assign columns corresponding to Mysql table columns     When I execute     LOAD DATA INFILE abc csv  INTO TABLE abc      this query adds all data to the first column      Please suggest auto syntax for importing data to Mysql

User · Answer

I was getting Error Code  1290  The MySQL server is running with the --secure-file-priv  option so it cannot execute this statement   This worked for me on windows 8 1 64 bit using wampserver 3 0 6 64bit   Edited my ini file from C  wamp64 bin mysql mysql5 7 14  Delete entry secure file priv c  wamp64 tmp   or whatever dir you have here   Stopped everything -exit wamp etc - and restarted everything  then punt my cvs file on C  wamp64 bin mysql mysql5 7 14 data u242349266 recur  the last dir being my database name   executed  LOAD DATA INFILE  myfile csv    INTO TABLE alumnos   FIELDS TERMINATED BY       ENCLOSED BY      LINES TERMINATED BY   r n   IGNORE 1 LINES      and VOILA

User · Answer

You can use LOAD DATA INFILE command to import csv file into table    Check this link MySQL - LOAD DATA INFILE   LOAD DATA LOCAL INFILE  abc csv  INTO TABLE abc FIELDS TERMINATED BY      ENCLOSED BY      LINES TERMINATED BY   r n  IGNORE 1 LINES  col1  col2  col3  col4  col5        For MySQL 8 0 users   Using the LOCAL keyword hold security risks and as of MySQL 8 0 the LOCAL capability is set to False by default  You might see the error      ERROR 1148  The used command is not allowed with this MySQL version   You can overwrite it by following the instructions in the docs  Beware that such overwrite does not solve the security issue but rather just an acknowledge that you are aware and willing to take the risk

User · Answer

You can load data from a csv or text file  If you have a text file with records from a table  you can load those records within the table  For example if you have a text file  where each row is a record with the values for each column  you can load the records this way   table sql  id   field 1  name   field2   table txt  1 peter  2 daniel        --example on windows  LOAD DATA LOCAL INFILE  C   directory example  table txt  INTO TABLE Table CHARACTER SET UTF8 FIELDS TERMINATED BY     LINES TERMINATED BY   r n

User · Answer

Before importing the file  you must need to prepare the following    A database table to which the data from the file will be imported  A CSV file with data that matches with the number of columns of the table and the type of data in each column  The account  which connects to the MySQL database server  has FILE and INSERT privileges    Suppose we have following table      CREATE TABLE USING FOLLOWING QUERY    CREATE TABLE IF NOT EXISTS  survey       projectId  bigint 20  NOT NULL     surveyId  bigint 20  NOT NULL     views  bigint 20  NOT NULL     dateTime  datetime NOT NULL         YOUR CSV FILE MUST BE PROPERLY FORMATTED FOR EXAMPLE SEE FOLLOWING   ATTACHED IMAGE       If every thing is fine   Please execute following query to LOAD DATA FROM CSV FILE       NOTE   Please add absolute path of your CSV file   LOAD DATA INFILE   var www csv data csv   INTO TABLE survey  FIELDS TERMINATED BY      ENCLOSED BY     LINES TERMINATED BY   r n  IGNORE 1 LINES    If everything has done  you have exported data from CSV to table successfully

User · Answer

If you are running LOAD DATA LOCAL INFILE from the windows shell  and you need to use OPTIONALLY ENCLOSED BY      you will have to do something like this in order to escape characters properly    C  Program Files MySQL MySQL Server 5 6 bin mysql  -u root --password  password  -e  LOAD DATA LOCAL INFILE   file   INTO TABLE  table  FIELDS TERMINATED BY     OPTIONALLY ENCLOSED BY        LINES TERMINATED BY   n  IGNORE 1 LINES  --verbose --show-warnings  gt  mysql  fname  out

User · Answer

let suppose you are using xampp and phpmyadmin   you have file name  ratings txt  table name  ratings  and database name  movies   if your xampp is installed in  C  xampp     copy your  ratings txt  file in  C  xampp mysql data movies  folder   LOAD DATA INFILE  ratings txt  INTO TABLE ratings FIELDS TERMINATED BY     ENCLOSED BY     LINES TERMINATED BY   r n  IGNORE 1 LINES    Hope this can help you to omit your error if you are doing this on localhost

User · Answer

Syntax   LOAD DATA  LOW PRIORITY   CONCURRENT   LOCAL  INFILE  file name  INTO TABLE  tbl name  CHARACTER SET  CHARACTER SET charset name  FIELDS   FIELDS   COLUMNS  TERMINATED BY  string      LINES TERMINATED BY  string      IGNORE number  LINES   ROWS     See this Example   LOAD DATA LOCAL INFILE  E   wamp  tmp  customer csv  INTO TABLE  customer  CHARACTER SET  utf8  FIELDS TERMINATED BY     ENCLOSED BY     LINES TERMINATED BY   r n  IGNORE 1 LINES

User · Answer

You probably need to set the FIELDS TERMINATED BY     or whatever the delimiter happens to be   For a CSV file  your statement should look like this   LOAD DATA INFILE  data csv  INTO TABLE tbl name FIELDS TERMINATED BY     ENCLOSED BY     LINES TERMINATED BY   r n  IGNORE 1 LINES

User · Answer

By these days  ending 2019  I prefer to use a tool like http   www convertcsv com csv-to-sql htm  I you got a lot of rows you can run partitioned blocks saving user mistakes when csv come from a final user spreadsheet

User · Answer

Insert bulk more than 7000000 record in 1 minutes in database superfast query with calculation  mysqli query  cons        LOAD DATA LOCAL INFILE  quot    file   quot      INTO TABLE tablename     FIELDS TERMINATED by           LINES TERMINATED BY    n       IGNORE 1 LINES      isbn10 isbn13 price discount free stock report report date       SET RRP   IF discount   0 00 price-price   45 100 IF discount   0 01 price IF discount    0 00 price-price   discount 100  RRP              RRP nl   RRP   1 44   8           RRP bl   RRP   1 44   8           ID   NULL          affected    int   mysqli affected rows  cons  -1    log- gt lwrite  Inventory CSV to database     affected   record inserted successfully      RRP and RRP nl and RRP bl is not in csv but we are calculated that and after insert that

User · Answer

You can try to insert like this     LOAD DATA INFILE   tmp filename csv  replace INTO TABLE  table name  FIELDS TERMINATED BY     LINES TERMINATED BY   n   field1 field2 field3

[mysql] MYSQL import data from csv using LOAD DATA INFILE

Examples related to mysql

Examples related to csv

Examples related to import

Examples related to load-data-infile

Examples related to mysql-loadfile