1

I would like to import data from a CSV file with column headers into a database. I can do this with the COPY statement, but only if I first manually create a table with a column for each column in the CSV file. Is there any way to automatically create this table based on the headers in the CSV file?

here below is the query for to load csv into mysql table t1 in which t1 is already created in database manually

load data local infile "C:\\EQA\\project\\input1.csv" 
    into table request_table 
    character set latin1 
    fields terminated by','
    ENCLOSED BY '"'
    lines terminated by'\r\n' 
    IGNORE 1 ROWS

but i am actually looking to generate table based on csv headers is that possible? here my requirement is evertime when i export some data from other database like oracle i a able to store it into csv now i need to import this csv into other database like mysql if it is for one time we can do manually to create table in mysql what if i get many csv files from oracle databse? if i follow manually creating table it leads more complexity right!is ther any better case available if so can u please help me

3
  • 1
    I would comment that what you are calling a script is actually a query - a script is a set of commands which I surmise would create additional queries which would create the table first. This is definately technically doable, but I don't do Windows, and prefer Postgres to MySQL, so can't be of much assistance on exactly how its done.
    – davidgo
    Feb 23 '19 at 9:54
  • I don't know if this is a good answer, but you might consider looking into the Write-ObjectToSQL cmdlet for Powershell, here: gallery.technet.microsoft.com/scriptcenter/…. You could probably have a script read the first line of your csv file to get the table names, and then pass those variables to the cmdlet. Feb 27 '19 at 4:54
  • You cannot directly do this. You can only import the data. You need some external tool to accomplish this. I suggest you try MySQL Workbench. Feb 27 '19 at 5:01
1

awk is a truly awesome tool, pun intended. you should learn it as it is one of the easiest scripting languages for text manipulation there is.

Here is an awk script named 'createtable.awk' that gives you a good start. you have to save the first line of the csv file to 'somefile.csv' and ONLY the first line

Minor editing is required, you will still have to specify a primary key and varchar(60) is assumed for every field so you can modify the field type to suite...

awk -f createtable.awk somefile.csv to view the output

awk -f createtable.awk somefile.csv > createtable.txt to send to a file then copy/paste into an SQL command

BEGIN{
  FS=","
  tablename = "MyTable"
  printf("\n DROP TABLE %s IF EXISTS",tablename)
  printf("\n CREATE TABLE %s ",tablename)
  printf("\n( ")
}
{
  #printf("\n NF=%d",NF)
  for (i=0; i<=NF; i++)
  {
    printf("\n %s varchar(60) NOT NULL DEFAULT 'mt', ",$(i))
  }
}
END{
  printf("\n ); ")
}
1

I'd suggest you look at Data Transformer (disclaimer - I'm its developer). It can convert CSV (and others) to SQL. The generated SQL contains "insert" statements for each line and a "create table" statement.

The app works offline, and your data never leaves your computer.

You can get it from the Mac App Store or the Microsoft Store.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.