Import CSV into new table

Hi - new to HeidiSQL, just downloaded and installed it today. It looks like it could be useful, but I was absolutely astonished to learn that it does not provide any way to import a CSV file directly into a new table. I have no need to import into an existing table, and if I have to create the code manually to create a new table with all the right fields, that seems to defeat the purpose of using HeidiSQL!

Here's what I'd hoped to see:

1. Go to Tools and select Import CSV.
2. Specify the .CSV file to be imported.
3. Specify database and select option "New Table" in menu along with names of existing tables.
4. Check box indicating that first row in text file should be interpreted as column names.
5. Have HeidiSQL do brief, simple analysis of first few lines of CSV file and make best guess as to data type (text, integer, etc.) and offer me option to specify data type manually for each column if necessary. Alternately, skip the analysis, assume "text", and just present me a list of potential columns and let me specify the data type.
6. Once options are specified, create new table on the fly with specified column names and import data into new columns.

This doesn't seem like it would be that difficult to do, programmatically, which makes it even more surprising that it hasn't already been done. Are there other tools that can do this?

The table editor in HeidiSQL can be used to create an empty table before you import your data.

Hi, my situation is exactly the same. I have CSV files with lot of fields and I import a few lines with phpmyadmin (it createst the table) and import the rest of the lines with HeidiSQL..not the best solution.

Hi - new to HeidiSQL, just downloaded and installed it today. It looks like it could be useful, but I was absolutely astonished to learn that it does not provide any way to import a CSV file directly into a new table. I have no need to import into an existing table, and if I have to create the code manually to create a new table with all the right fields, that seems to defeat the purpose of using HeidiSQL!

Here's what I'd hoped to see:

1. Go to Tools and select Import CSV.
2. Specify the .CSV file to be imported.
3. Specify database and select option "New Table" in menu along with names of existing tables.
4. Check box indicating that first row in text file should be interpreted as column names.
5. Have HeidiSQL do brief, simple analysis of first few lines of CSV file and make best guess as to data type (text, integer, etc.) and offer me option to specify data type manually for each column if necessary. Alternately, skip the analysis, assume "text", and just present me a list of potential columns and let me specify the data type.
6. Once options are specified, create new table on the fly with specified column names and import data into new columns.

This doesn't seem like it would be that difficult to do, programmatically, which makes it even more surprising that it hasn't already been done. Are there other tools that can do this?

Hi.

I've been coding SQL for over 20 years and this "problem" is fairly common but not well solved, even by the likes of SQL Server Integration Services. The typical scenario is loading a new data set as a one-off or starting a new ETL. You are given sample data file and then end up refining your table layout. Very tiresome. I have written the layout discovery code in the past; it traverses the whole file and refines the field attribute as it goes (staring with specifics, refining to more general). SSIS give you the option to choose how many rows to test this way, but it only takes one row to stuff up your layout.

I have found a website recently that does just this (can't tell you as first post hehe) but check out convertcsv dot com :P

Not advocating for them but it is the same idea and just may save some of us a bit of time. I am concerned about loading a file to any 3rd party (even if it is only processed by local javascript - I can't tell, my js isn't that good).

This would be a great advancement of this product.

Regards, Daz.

Yes, I totally agree with your idea. Would be a nice enhancement. Also for the reason that I mostly find myself (and I guess many others do) setting up a quick-and-dirty table with all MEDIUMTEXT columns, just because it's quicker than analyzing the data in the csv file.

Could you probably file that as a new issue on Github? I'm not sure how to integrate that into Heidi but we'll find a way.

Here is how I did it dirty coding,

Check csv first is a must. 0. first row, first character = enclosed by, hence there must be even count of that char in row. Ignoring escape char here.

1. first row of csv how many columns
2. are all other rows same col. count and same formated -> otherwise it is badly formated csv = can't proceed

Import 3. import into new table named: 'auto_importedtable' + auto UID. All fields text. Field names f1.f2,f... 4.If filling like analysing incoming data, change created table columns accordingly, should fail if data type is not right - leave it then type of text.

Done! @ansgar See issues 477 raised on Github.