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.
answered 27 Apr '12, 13:37
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;
answered 01 May '12, 12:02