Can I automatically create a table in mySQL from a csv file with headers as column names?

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


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 ); ")
}

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.