Input vs Load Table - Fixed Width File

[former_SQLA_member1694868]

I have some fixed-width files that I need to extract data from. Each record is a new line, but the columns are fixed-width.

I have a statement that works correctly when I run it in ISQL using the

input into mytable from 'c:\\myfile.txt' format fixed column widths (5,1,3,7);

I would like to capture the task in a procedure so I can run it multiple times against various files without running ISQL.

The input statement is only available in ISQL, so what are my options for loading the fixed-width type of files from within a procedure? I am guessing LOAD TABLE but how specifically to get LOAD TABLE to parse the fixed-widths and any other advice is appreciated.

[Former Member · Accepted solution]

Rather than LOAD TABLE, you could use an INSERT ... FROM SELECT that uses OPENSTRING over the file. This is somewhat dependent on the size of the file - it won't be efficient for very large files. However, Once you have the file's contents available to a query, you can then use SUBSTRING() to chunk off the appropriate pieces and use those sub-expressions for the INSERT.

[former_SQLA_member1694868 · Accepted solution]

The files are about 1.1GB per, but I'm willing to take the hit on the time processing if I don't have to spend time converting them to csv or accessing ISQL each time. I'll give this a shot.

[Former Member · Accepted solution]

The big hit will be the transaction log. By default LOAD TABLE merely writes the LOAD TABLE statement to the log, not the contents of the file.

[VolkerBarth · Accepted solution]

So what about using LOAD TABLE to load the unseparated contents (line by line) into a temporary table (say, with two columns: line number and line contents) and then use SUBSTRING() to separate the values and insert them into the real table?

[Former Member · Accepted solution]

You could certainly do that, Volker, to work around the issues of the fixed-format file. It doesn't avoid the transaction log overhead, however, of the subsequent INSERT statement.

[VolkerBarth · Accepted solution]

Yes, I'm aware of that. My suggestion comes simply from the facts that

• the OPENSTRING approach would have the same impact on the log,
• and an approach to INSERT from a temporary table filled with LOAD TABLE might be more common usage.

I do not claim that it is a better approach...

[former_SQLA_member1694868]

Here is some code in case anyone needs it:

insert into usr.SourceTable with auto name

WITH IMPORTED AS (
    select MYROW from openstring( 
        FILE 'C:\\\\myfile.txt')
        WITH
        (
        MYROW long varchar
        )
        OPTION (DELIMITED BY 'STRINGNEVERTOBEFOUND')
        as ImportedSub
)
SELECT 
  SUBSTRING(IMPORTED.MYROW,0,13) AS Col1
, SUBSTRING(IMPORTED.MYROW,13,5) AS Col2
, SUBSTRING(IMPORTED.MYROW,18,7) AS Col3
FROM IMPORTED;