Input vs Load Table - Fixed Width File

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.

input load-table fixed-width

asked 27 Apr '12, 13:04

Siger Matt
3.3k●56●72●101
accept rate: 15%

edited 27 Apr '12, 13:06

flat view

2 Answers:

active answers oldest answers newest answers popular answers

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 Paulley
10.8k●5●76●106
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

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

flat view

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 Matt
3.3k●56●72●101
accept rate: 15%

flat view

Your answer

toggle preview

community wiki:

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

learn more about Markdown

Question tags:

load-table ×18
input ×6
fixed-width ×1

question asked: 27 Apr '12, 13:04

question was seen: 3,015 times

last updated: 01 May '12, 12:02

SAP SQL Anywhere

Input vs Load Table - Fixed Width File

Follow this question

Related questions