Question Re: Input vs Load Table - Fixed Width File in Technology Q&A

Input vs Load Table - Fixed Width File

former_SQLA_member1694868 — Fri, 27 Apr 2012 13:04:50 GMT

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.

Re: Input vs Load Table - Fixed Width File

Former Member — Fri, 27 Apr 2012 13:37:13 GMT

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.

Re: Input vs Load Table - Fixed Width File

former_SQLA_member1694868 — Fri, 27 Apr 2012 14:34:42 GMT

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.

Re: Input vs Load Table - Fixed Width File

Former Member — Fri, 27 Apr 2012 15:27:05 GMT

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.

Re: Input vs Load Table - Fixed Width File

VolkerBarth — Sun, 29 Apr 2012 06:56:50 GMT

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?

Re: Input vs Load Table - Fixed Width File

Former Member — Sun, 29 Apr 2012 19:30:38 GMT

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.

Re: Input vs Load Table - Fixed Width File

VolkerBarth — Mon, 30 Apr 2012 03:33:07 GMT

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

Re: Input vs Load Table - Fixed Width File

former_SQLA_member1694868 — Tue, 01 May 2012 12:02:33 GMT

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;