How do I import CSV file into a MySQL table

Question

I have an unnormalized events-diary CSV from a client that I m trying to load into a MySQL table so that I can refactor into a sane format  I created a table called  CSVImport  that has one field for every column of the CSV file  The CSV contains 99 columns   so this was a hard enough task in itself   CREATE TABLE  CSVImport   id INT   ALTER TABLE CSVImport ADD COLUMN Title VARCHAR 256   ALTER TABLE CSVImport ADD COLUMN Company VARCHAR 256   ALTER TABLE CSVImport ADD COLUMN NumTickets VARCHAR 256       ALTER TABLE CSVImport Date49 ADD COLUMN Date49 VARCHAR 256   ALTER TABLE CSVImport Date50 ADD COLUMN Date50 VARCHAR 256     No constraints are on the table  and all the fields hold VARCHAR 256  values  except the columns which contain counts  represented by INT   yes no  represented by BIT   prices  represented by DECIMAL   and text blurbs  represented by TEXT    I tried to load data into the file   LOAD DATA INFILE   home paul clientdata csv  INTO TABLE CSVImport  Query OK  2023 rows affected  65535 warnings  0 08 sec  Records  2023  Deleted  0  Skipped  0  Warnings  198256 SELECT   FROM CSVImport    NULL               NULL          NULL             NULL   NULL                        The whole table is filled with NULL   I think the problem is that the text blurbs contain more than one line  and MySQL is parsing the file as if each new line would correspond to one databazse row  I can load the file into OpenOffice without a problem   The clientdata csv file contains 2593 lines  and 570 records  The first line contains column names  I think it is comma delimited  and text is apparently delimited with doublequote   UPDATE   When in doubt  read the manual  http   dev mysql com doc refman 5 0 en load-data html  I added some information to the LOAD DATA statement that OpenOffice was smart enough to infer  and now it loads the correct number of records   LOAD DATA INFILE   home paul clientdata csv  INTO TABLE CSVImport COLUMNS TERMINATED BY     OPTIONALLY ENCLOSED BY     ESCAPED BY     LINES TERMINATED BY   n  IGNORE 1 LINES    But still there are lots of completely NULL records  and none of the data that got loaded seems to be in the right place

User · Answer

You can fix this by listing the columns in you LOAD DATA statement. From the manual:

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

...so in your case you need to list the 99 columns in the order in which they appear in the csv file.

User · Answer

If you are using a windows machine with Excel spreadsheet loaded  the new mySql plugin to Excel is phenomenal   The folks at Oracle really did a nice job on that software   You can make the database connection directly from Excel   That plugin will analyse your data  and set up the tables for you in a format consistent with the data  I had some monster big csv files of data to convert   This tool was a big time saver    http   dev mysql com downloads windows excel   You can make updates from within Excel that will populate to the database online   This worked exceedingly well with mySql files created on ultra inexpensive GoDaddy shared hosting    Note when you create the table at GoDaddy  you have to select some off-standard settings to enable off site access of the database      With this plugin you have pure interactivity between your XL spreadsheet and online mySql data storage

User · Answer

In case if you using Intellij https   www jetbrains com datagrip features importexport html

User · Answer

Yet another solution is to use csvsql tool from amazing csvkit suite   Usage example   csvsql --db mysql    user  password localhost  database --insert --tables  tablename   file   This tool can automatically infer the data types  default behavior   create table and insert the data into the created table  --overwrite option can be used to drop table if it already exists  --insert option     to populate the table from the file   To install the suite  pip install csvkit   Prerequisites  python-dev  libmysqlclient-dev  MySQL-python  apt-get install python-dev libmysqlclient-dev pip install MySQL-python

User · Answer

Change servername username  password dbname path of your file  tablename and the field which is in your database you want to insert    lt  php      servername    localhost        username    root        password            dbname    bd dashboard         For create connection      conn   new mysqli  servername   username   password   dbname         query    LOAD DATA LOCAL INFILE                   C  Users lenovo Desktop my data csv                  INTO TABLE test tab                 FIELDS TERMINATED BY                     LINES TERMINATED BY   n                  IGNORE 1 LINES                  name mob        if    result   mysqli query  conn   query            echo   lt script gt alert  Oops    Some Error occured     lt  script gt            exit                  exit mysqli error             else          echo   lt script gt alert  Data Inserted Successfully     lt  script gt                  gt

User · Answer

Use mysqlimport to load a table into the database   mysqlimport --ignore-lines 1               --fields-terminated-by                 --local -u root               -p Database                TableName csv   I found it at http   chriseiffel com everything-linux how-to-import-a-large-csv-file-to-mysql   To make the delimiter a tab  use --fields-terminated-by   t

User · Answer

Try this  it worked for me      LOAD DATA LOCAL INFILE  filename csv  INTO TABLE table name FIELDS TERMINATED BY     ENCLOSED BY     IGNORE 1 ROWS    IGNORE 1 ROWS here ignores the first row which contains the fieldnames  Note that for the filename you must type the absolute path of the file

User · Answer

I see something strange  You are using for ESCAPING the same character you use for ENCLOSING  So the engine does not know what to do when it founds a     and I think that is why nothing seems to be in the right place  I think that if you remove the line of ESCAPING  should run great  Like   LOAD DATA INFILE   home paul clientdata csv  INTO TABLE CSVImport COLUMNS TERMINATED BY     OPTIONALLY ENCLOSED BY     LINES TERMINATED BY   n  IGNORE 1 LINES    Unless you analyze  manually  visually        your CSV and find which character uses for escape  Sometimes is      But if you do not have it  do not use it

User · Answer

Here is sample excel file screen shot     Save as  and choose  csv   And you will have as shown below  csv data screen shot if you open using notepad   or any other notepad     Make sure you remove header and have column alignment in  csv as in mysql Table  Replace folder name by your folder name  LOAD DATA LOCAL INFILE  D  folder name myfilename csv  INTO TABLE mail  FIELDS TERMINATED BY       fname lname  email  phone    If big data  you can take coffee and have it load    Thats all you need

User · Answer

I did it in simple way using phpmyadmin  I followed the steps by  Farhan but all data were eltered in single column  How I did   Created a CSV file and deleted the header row with column names  Kept only data  I created a table with column names matching the csv columns  Remember to assign appropriate types to each column  I just selected the import and went to import tab  In browse I selected the CSV file and kept all options as it is  To my surprise all the data got imported successfully in their appropriate columns

User · Answer

If you are using MySQL Workbench  currently 6 3 version  you can do this by    Right click on  Tables   Chose Table Data Import Wizard  Chose your csv file and follow the instructions  JSON also could be used   The good thing is that you can create a new table based on the csv file you want to import or load data to an existing table

User · Answer

The mysql command line is prone to too many problems on import  Here is how you do it    use excel to edit the header names to have no spaces save as  csv use free Navicat Lite Sql Browser to import and auto create a new table  give it a name  open the new table insert a primary auto number column for ID change the type of the columns as desired  done

User · Answer

phpMyAdmin can handle CSV import  Here are the steps    Prepare the CSV file to have the fields in the same order as the MySQL table fields  Remove the header row from the CSV  if any   so that only the data is in the file  Go to the phpMyAdmin interface  Select the table in the left menu  Click the import button at the top  Browse to the CSV file  Select the option  CSV using LOAD DATA   Enter     in the  fields terminated by   Enter the column names in the same order as they are in the database table  Click the go button and you are done    This is a note that I prepared for my future use  and sharing here if someone else can benefit

User · Answer

I use mysql workbench to do the same job    create new schema  open newly created schema right click on  Tables  and select  Table Data Import Wizard  give the csv file path and table name and finally configure your column type because the wizard set default column type based on their values    Note  take a look at mysql workbench s log file for any errors by using  tail -f  mysqlworkbenchpath  log wb  log

User · Answer

PHP Query for import csv file to mysql database   query    lt  lt  lt EOF             LOAD DATA LOCAL INFILE   file               INTO TABLE users              FIELDS TERMINATED BY                  LINES TERMINATED BY   n               IGNORE 1 LINES              name mobile email      EOF  if    result   mysqli query  this- gt db   query                exit mysqli error  this- gt db             Sample CSV file data     name mobile email Christopher Gritton 570-686-3439 ChristopherKGritton inbound plus Brandon Wilson 541-309-5149 BrandonMWilson inbound plus Craig White 516-795-8065 CraigJWhite inbound plus David Whitney 713-214-3966 DavidCWhitney inbound plus

User · Answer

I know that the question is old  But I would like to share this   I Used this method to import more than 100K records   5MB  in 0 046sec   Here s how you do it       LOAD DATA LOCAL INFILE    c  temp some-file csv  INTO TABLE your awesome table   FIELDS TERMINATED BY      ENCLOSED BY     LINES TERMINATED BY   n   field 1 field 2   field 3     It is very important to include the last line   if you have more than one field i e normally it skips the last field    MySQL 5 6 17   LINES TERMINATED BY   n   field 1 field 2   field 3     Then  assuming you have the first row as the title for your fields  you might want to include this line also  IGNORE 1 ROWS   This is what it looks like if your file has a header row   LOAD DATA LOCAL INFILE    c  temp some-file csv  INTO TABLE your awesome table   FIELDS TERMINATED BY      ENCLOSED BY     LINES TERMINATED BY   n  IGNORE 1 ROWS  field 1 field 2   field 3

User · Answer

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  gt  Import CSV file

User · Answer

How to import csv files to sql tables  Example file  Overseas trade index data CSV File  Steps    Need to create table for overseas trade index  Need to create columns related to csv file   SQL Query     id int not null primary key auto increment  series reference varchar  60   period varchar  60   data value decimal 60 0   status varchar  60   units varchar  60   magnitude int 60   subject text 60   group text 60   series title 1 varchar  60   series title 2 varchar  60   series title 3 varchar  60   series title 4 varchar  60   series title 5 varchar  60        Need to connect mysql database in terminal     gt show databases    gt use database    gt show tables   Please enter this command to import the csv data to mysql tables   load data infile   home desktop Documents overseas csv  into table trade index fields terminated by     lines terminated by   n   series reference period data value status units magnitude subject series title1 series title 2 series title 3 series title 4 series title 5    Find this overseas trade index data on sqldatabase   select   from trade index

User · Answer

Simplest way which I have imported 200  rows is below command in phpmyadmin sql window  I have a simple table of country with two columns CountryId CountryName  here is  csv data  here is command   LOAD DATA INFILE  c  country csv   INTO TABLE country  FIELDS TERMINATED BY      ENCLOSED BY     LINES TERMINATED BY   n  IGNORE 1 ROWS   Keep one thing in mind  never appear   in second column  otherwise your import will stop

[mysql] How do I import CSV file into a MySQL table?

Examples related to mysql

Examples related to csv

Examples related to load-data-infile