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. |
Rather than 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
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; |