How can I choose column names from the first line when importing a text file with DBISQL into a new table?

[VolkerBarth]

[Note: This is a similar question as this one relating to LOAD TABLE and OPENSTRING. However, while I generally agree with the answers there, I feel there are different requirements for a primarily GUI-based solution within DBISQL.]

I just have to import an Excel file into my SA 11.0.1 database. It's a one-time operation, and it's a typical data file with the first line consisting of the column names. So I would like to minimize my efforts...

As I would like to automatically create the table (the worksheet contains lots of columns), I do not want to use LAOD TABLE. Instead I'm trying to use the DBISQL import.

So I have converted the excel file to a tab-delimited text file and tried to import. As the SA 11 DBISQL doesn't handle the first line differently, it seems inappropriate. (E.g. I can't even choose an integer type for any column because the column names in the first line all contain letters.)

So I switched to the SA 12 DBISQL, and luckily, it has the option to skip the first (n) lines. A senseful enhancement, thanks! So I felt the GUI is handling my simple task quite well, and I can now choose appropriate column types. (And it's nice that DBISQL v12 runs against a v11 server...)

But now I would like to use the first line to choose the column names (as can be done when importing text files within Excel, for example).

And that option seems missing, if I have not overseen anything...

---

Therefore I would suggest to add an "Interpret first line as column names" option in the GUI (and possibly in the INPUT statement).

I know, I can define an ODBC DSN for the data file and import from that (as suggested for the cited question), but I guess that's way too complicated for a GUI-based solution (and possibly a hurdle for a bunch of DBISQL users, too).

[reimer_pods]

I support that request. For new customers we often import data into our application's database supplied as CSV files, usually a one time thing. The enhancement suggested by Volker would be very helpfull.

[VolkerBarth]

What I like about the import tool: It leaves an automatically created table as is, even if the import fails (e.g. because of conversion errors). So one is free to adjust the column type (or data) and try again. - In contrast, my experience with the MS SQL import/export tool (dtswiz.exe) is different: If the import fails, the table is deleted, the program exits, and you have to start from scratch again, i.e. repeating all those steps...

[VolkerBarth]

Just to add: It would fit well to the new SA12 OUTPUT WITH COLUMN NAMES option:)

[VolkerBarth · Accepted solution]

Just a late thank you:

Version 16 does offer that facility, see Interactive SQL new features. Heres's a snippet from the according Import Wizard dialog:

[reimer_pods · Accepted solution]

Thanks for posting the information, I've overlooked that in the "Whats' new" section.