Import CSV to mysql table

Question

What is the best fastest way to upload a csv file into a mysql table   I would like for the first row of data be used as the column names   Found this   How to import CSV file to MySQL table  But the only answer was to use a GUI and not shell

User · Answer

Here s how I did it in Python using csv and the MySQL Connector   import csv import mysql connector  credentials   dict user        password        database        host        connection   mysql connector connect   credentials  cursor   connection cursor prepared True  stream   open  filename csv    rb   csv file   csv DictReader stream  skipinitialspace True   query    CREATE TABLE t    query        join       VARCHAR 255   format column  for column in csv file fieldnames  query        cursor execute query  for row in csv file      query    INSERT INTO t SET       query        join            format column  for column in row keys        cursor execute query  row values     stream close   cursor close   connection close     Key points   Use prepared statements for the INSERT Open the  file csv in  rb  binary Some CSV files may need tweaking  such as the skipinitialspace option  If 255 isn t wide enough you ll get errors on INSERT and have to start over  Adjust column types  e g  ALTER TABLE t MODIFY  Amount  DECIMAL 11 2   Add a primary key  e g  ALTER TABLE t ADD  id  INT PRIMARY KEY AUTO INCREMENT

User · Answer

I wrestled with this for some time  The problem lies not in how to load the data  but how to construct the table to hold it  You must generate a DDL statement to build the table before importing the data   Particularly difficult if the table has a large number of columns   Here s a python script that  almost  does the job      usr bin python     import sys import csv    get file name  and hence table name  from command line   exit with usage if no suitable argument    if len sys argv   lt  2     sys exit  Usage      sys argv 0       input CSV filename   ifile   sys argv 1     emit the standard invocation print  create table     ifile         with open ifile     csv   as inputfile     reader   csv DictReader inputfile     for row in reader        k   row keys         for item in k           print       item      TEXT         break    print    n    The problem it leaves to solve is that the final field name and data type declaration is terminated with a comma  and the mySQL parser won t tolerate that   Of course it also has the problem that it uses the TEXT data type for every field   If the table has several hundred columns  then VARCHAR 64  will make the table too large   This also seems to break at the maximum column count for mySQL  That s when it s time to move to Hive or HBase if you are able

User · Answer

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 use IGNORE 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

User · Answer

I wrote some code to do this  i ll put in a few snippets    dir   getcwd       Get current working directory where this  php script lives  fileList   scandir  dir      scan the directory where this  php lives and make array of file names   Then get the CSV headers so you can tell mysql how to import  note  make sure your mysql columns exactly match the csv columns      extract headers from  csv for use in import command  headers   str replace            array shift file  path      headers   str replace   n        headers     Then send your query to the mysql server   mysqli query  cons            LOAD DATA LOCAL INFILE     path                INTO TABLE    dbTable                 FIELDS TERMINATED by       ENCLOSED BY                   LINES TERMINATED BY    n               IGNORE 1 LINES                 headers                            or die mysql error

User · Answer

As others have mentioned  the load data local infile works just fine  I tried the php script that Hawkee posted  but didnt work for me  Rather than debug it  here s what i did   1  copy paste the header row of the CSV file into a txt file and edit with emacs  add a comma and CR between each field to get each on on it s own line  2  Save that file as FieldList txt 3  edit the file to include defns for each field  most were varchar  but quite a few were int x   Add create table tablename   to the beginning of the file and   to the end of the file  Save it as CreateTable sql 4  start mysql client with input from the Createtable sql file to create the table 5  start mysql client  copy paste in most of the  LOAD DATA INFILE  command subsituting my table name and csv file name  Paste in the FieldList txt file  Be sure to include the  IGNORE 1 LINES  before pasting in the field list  Sounds like a lot of work  but easy with emacs

User · Answer

Use TablePlus application  Right-Click on the table name from the right panel Choose Import      From CSV Choose CSV file Review column matching and hit Import All done

User · Answer

First create a table in the database with same numbers of columns that are in the csv file    Then use following query  LOAD DATA INFILE  D  Projects testImport csv  INTO TABLE cardinfo FIELDS TERMINATED BY     ENCLOSED BY     LINES TERMINATED BY   r n

User · Answer

To load data from text file or csv file the command is     load data local infile  file-name csv  into table table-name fields terminated by    enclosed by    lines terminated by   n   column-name     In above command  in my case there is only one column to be loaded so there is no  terminated by  and  enclosed by  so I kept it empty else programmer can enter the separating character   for e g     comma  or   or   or any thing     for people who are using mysql version 5 and above     Before loading the file into mysql must ensure that below tow line are added in side etc mysql my cnf  to edit my cnf command is   sudo vi  etc mysql my cnf   mysqld    local-infile   mysql    local-infile

User · Answer

if you have the ability to install phpadmin there is a import section where you can import csv files to your database there is even a checkbox to set the header to the first line of the file contains the table column names  if this is unchecked  the first line will become part of the data

User · Answer

I have google search many ways to import csv to mysql  include   load data infile    use mysql workbench  etc    when I use mysql workbench import button  first you need to create the empty table on your own  set each column type on your own  Note  you have to add ID column at the end as primary key and not null and auto increment  otherwise  the import button will not visible at later   However  when I start load CSV file  nothing loaded  seems like a bug  I give up    Lucky  the best easy way so far I found is to use Oracle s mysql for excel  you can download it from here mysql for excel  This is what you are going to do   open csv file in excel   at Data tab   find mysql for excel button  select all data  click export to mysql  Note to set a ID column as primary key   when finished  go to mysql workbench to alter the table  such as currency type should be decimal 19 4  for large amount decimal 10 2  for regular use  other field type may be set to varchar 255

User · Answer

If you start mysql as  mysql -u  -p --local-infile    it will work fine

User · Answer

Here s a simple PHP command line script that will do what you need    lt  php   host    localhost    user    root    pass        database    database     db   mysql connect  host   user   pass   mysql query  use  database    db                                                                                          Parameters  filename csv table name   argv     SERVER argv    if  argv 1      file    argv 1     else       echo  Please provide a file name n   exit     if  argv 2      table    argv 2     else        table   pathinfo  file        table    table  filename                                                                                             Get the first row to create the column headings   fp   fopen  file   r     frow   fgetcsv  fp    foreach  frow as  column        if  columns   columns               columns       column  varchar 250        create    create table if not exists  table   columns     mysql query  create   db                                                                                          Import the data into the newly created table    file     SERVER  PWD        file   q    load data infile   file  into table  table fields terminated by     ignore 1 lines   mysql query  q   db      gt    It will create a table based on the first row and import the remaining rows into it   Here is the command line syntax   php csv import php csv file csv table name

User · Answer

Import CSV Files into mysql table  LOAD DATA LOCAL INFILE  d   Site csv  INTO TABLE  siteurl  FIELDS TERMINATED BY     ENCLOSED BY     LINES TERMINATED BY   r n    Character   Escape Sequence  0      An ASCII NUL  0x00  character  b      A backspace character  n      A newline  linefeed  character  r      A carriage return character  t      A tab character   Z      ASCII 26  Control Z   N      NULL   visits   http   www webslessons com 2014 02 import-csv-files-using-php-and-mysql html

[mysql] Import CSV to mysql table

Examples related to mysql

Examples related to csv

Examples related to import

Examples related to load-data-infile

Examples related to database-table