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.

asked 27 Apr '12, 13:04

Siger%20Matt's gravatar image

Siger Matt
3.2k516998
accept rate: 13%

edited 27 Apr '12, 13:06


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.

permanent link

answered 27 Apr '12, 13:37

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

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.

(27 Apr '12, 14:34) Siger Matt

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.

(27 Apr '12, 15:27) Glenn Paulley
Replies hidden

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?

(29 Apr '12, 06:56) Volker Barth
1

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.

(29 Apr '12, 19:30) Glenn Paulley

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

(30 Apr '12, 03:33) Volker Barth

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;
permanent link

answered 01 May '12, 12:02

Siger%20Matt's gravatar image

Siger Matt
3.2k516998
accept rate: 13%

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:

×14
×5
×1

question asked: 27 Apr '12, 13:04

question was seen: 1,434 times

last updated: 01 May '12, 12:02