Create mysql table directly from CSV file using the CSV Storage engine

Question

I just learned that MySQL has a native CSV storage engine which stores data in a Comma-Separated-Value file per table   Is it possible to create a table directly from a uploaded CSV file  something like   CREATE TABLE USERS  lt  PATH USERS CSV   where users csv is uploaded by the user

User · Answer

Convert CSV to SQL  helped me   Add your CSV file and you are good to go

User · Answer

you can use this bash script convert sh and run   convert sh -f example mycsvfile csv

User · Answer

I have made a Windows command line tool that do just that     You can download it here  http   commandline dk csv2ddl htm  Usage   C  Temp gt csv2ddl exe mysql test csv test sql   Or  C  Temp gt csv2ddl exe mysql advanced doublequote comma test csv test sql

User · Answer

I just discovered csvkit  which is a set of Unix command-line tools for CSV files   I installed it on my Mac with pip install csvkit  The command was   csvsql --dialect mysql --snifflimit 100000 bigdatafile csv  gt  maketable sql   You can alternatively provide a DB connection string and it can load the table directly

User · Answer

This is not possible  To create a table you need a table schema  What you have is a data file  A schema cannot be created with it    What you can do is check if your file has a header row  and  in that case  you can manually create a table using that header row   However  there is a way to generate a create table statement using a batch file as described by John Swapceinski in the comment section of the MySQL manual      Posted by John Swapceinski on September 5 2011 5 33am    Create a table using the  csv file s header       bin sh   pass in the file name as an argument    mktable filename csv echo  create table  1     head -1  1   sed -e  s    varchar 255   n g  echo   varchar 255

User · Answer

I m recommended use MySQL Workbench where is import data  Workbench allows the user to create a new table from a file in CSV or JSON format  It handles table schema and data import in just a few clicks through the wizard   In MySQL Workbench  use the context menu on table list and click Table Data Import Wizard     More from the MySQL Workbench 6 5 1 Table Data Export and Import Wizard documentation  Download MySQL Workbench here

User · Answer

If you re ok with using Python  Pandas worked great for me  csvsql hanged forever for my case   Something like   from sqlalchemy import create engine import pandas as pd  df   pd read csv   PATH TO FILE csv     Optional  set your indexes to get Primary Keys df   df set index   COL A    COL B     engine   create engine  mysql   user pass host db   echo False   df to sql table name  dwh engine  index False    Also this doesn t solve the  using CSV engine  part which was part of the question but might me useful as well

User · Answer

There is an easier way if you are using phpMyAdmin as your MySQL front end    Create a database with the default settings  Select the database  Click  Import  at the top of the screen  Select  CSV  under  Format   Choose the options appropriate to your CSV file  open the CSV file in a text editor and reference it to get the  appropriate  options    If you have problems  no problem  simply drop the database and try again

User · Answer

In addition to the other solutions mentioned Mac users may want to note that SQL Pro has a CSV import option which works fairly well and is flexible - you can change column names  and field types on import   Choose new table otherwise the initial dialogue can appear somewhat disheartening   Sequel Pro - database management application for working with MySQL databases

User · Answer

If someone is looking for a PHP solution see  PHP MySQL wrapper     db   new MySQL wrapper MySQL HOST  MySQL USER  MySQL PASS  MySQL DB    db- gt connect         this sample gets column names from first row of file    db- gt createTableFromCSV  test files countrylist csv    csv to table test        this sample generates column names   db- gt createTableFromCSV  test files countrylist1 csv    csv to table test no column names                   0  array     generate     r n         Create table from CSV file and imports CSV data to Table with possibility to update rows while import      param   string       file           - CSV File path     param   string       table          - Table name     param   string       delimiter      - COLUMNS TERMINATED BY  Default           param   string       enclosure      - OPTIONALLY ENCLOSED BY  Default           param   string       escape         - ESCAPED BY  Default           param   integer      ignore         - Number of ignored rows  Default  1      param   array        update         - If row fields needed to be updated eg date format or increment  SQL format only  FIELD is variable with content of that field in CSV row   update   array  SOME DATE    gt   STR TO DATE  SOME DATE    d  m  Y      SOME INCREMENT    gt    SOME INCREMENT   1       param   string       getColumnsFrom - Get Columns Names from  file or generate  - this is important if there is update while inserting  Default  file      param   string       newLine        - New line delimiter  Default   n      return  number of inserted rows or false        function createTableFromCSV  file   table   delimiter         enclosure         escape          ignore   1   update   array     getColumnsFrom    file    newLine     r n     db- gt close

User · Answer

MySQL for excel plugin can help you   http   dev mysql com doc refman 5 6 en mysql-for-excel html  Open your CSV file in excel  You can use this plugin to export excel data into a new table of remote or local mysql server  It will analyze your data  top 100 to 1000 rows  and create a corresponding table schema

User · Answer

I adopted the script from shiplu mokadd im to fit my needs  Whom it interests      bin bash if        -lt 2    then     if        -lt 1    then          echo  usage   0  path to csv file   lt table name gt   gt   sql filename           exit 1     fi     TABLENAME  1 else     TABLENAME  2 fi echo  CREATE TABLE  TABLENAME     FIRSTLINE   head -1  1    convert lowercase characters to uppercase FIRSTLINE   echo  FIRSTLINE   tr    lower       upper       remove spaces FIRSTLINE   echo  FIRSTLINE   sed -e  s     g     add tab char to the beginning of line FIRSTLINE   echo   t FIRSTLINE     add tabs and newline characters FIRSTLINE   echo  FIRSTLINE   sed -e  s      n  t g     add VARCHAR FIRSTLINE   echo  FIRSTLINE   sed -e  s    VARCHAR 255   g     print out result echo -e  FIRSTLINE  VARCHAR 255

User · Answer

This is not possible  you can however overwrite an existing table file  But be sure  that the line endings in your file are unix style  ending only with  n   not windows style  ending with  r n   whether you are working under windows or not

[mysql] Create mysql table directly from CSV file using the CSV Storage engine?

Examples related to mysql

Examples related to database

Examples related to csv

Examples related to storage-engines