[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).

asked 08 Oct '10, 10:04

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 08 Oct '10, 12:40

Comment Text Removed

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.

(08 Oct '10, 12:41) Reimer Pods

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...

(08 Oct '10, 13:40) Volker Barth

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

(13 Oct '10, 10:52) Volker Barth

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:

alt text

permanent link

answered 02 May '16, 05:22

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

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

(02 May '16, 06:04) Reimer Pods
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×113
×105
×14
×14
×7

question asked: 08 Oct '10, 10:04

question was seen: 12,791 times

last updated: 02 May '16, 06:04