Thanks for the question, Pratima.
Asked: December 06, 2016 - 8:46 am UTC
Last updated: December 12, 2016 - 1:12 pm UTC
Version: 11.2.0.1.0
Viewed 10K+ times! This question is 
You Asked
Hi Team,
We have a bank statement datafile provided in SWIFT940 standard format that has Cyrillic characters . On the basis of first 3 characters of lines we decide what information is provided in the line hence using POSITION is the only solution here.
Datafile example:
:20:9400124576021331
:25:200340027
:28:141
:60F:C160801KZT21637948,20
:61:1608010801CT70250000,00NFEX1162140060//1162140055
:86:059?00Commissions And Charges?20/PY/КПН ЗА НЕРЕЗИДЕНТА КОМПАН
И АО МХК ЕВРОХИМ РОССИЯ (ДОГОВОР ?21ЗАЙМА №001-0655165 ОТ 30.0
4.2015Г.).?22/BN/НАЛОГОВОЕ УПРАВЛЕНИЕ ПО БОСТАНДЫКСК?23/BI/KZ2407
0105KSN0000000?24/AB/KKMFKZ2A ГУ КОМИТЕТ КАЗНАЧЕЙСТВА МФ РК АЛМАТ
Ы KZ24070105KSN00?2500000?26/OA/KZT,
:62F:C160801KZT42536082,21
:64:C160801KZT42536082,21
In above datafile, record_id_no :86: has multiple lines.
If new line starts with multi-byte character, sql loder fails to load data. I tried using LIKE and NOT LIKE but it seems we can only use '=' and '<>' with WHEN. Is there any way to load such files?
Test CTL file :
LOAD DATA
CHARACTERSET UTF8
REPLACE
PRESERVE BLANKS
-- Record Type 61: Supplimentary Detail (set rec_id_no to 9)
INTO TABLE ce_stmt_int_tmp
WHEN rec_id_no not like ':%'
TRAILING NULLCOLS
(rec_no RECNUM,
rec_id_no POSITION(1:10) CHAR "decode(substr(rec_id_no,1,1), ':', '0', '9')",
column1 POSITION(1:80) CHAR)
-- Record Type 86: Information to Account Owner
INTO TABLE ce_stmt_int_tmp
WHEN rec_id_no like ':86%'
TRAILING NULLCOLS
(rec_no RECNUM,
rec_id_no POSITION(1:10) CHAR
TERMINATED BY WHITESPACE "ltrim(substr(rec_id_no,1,3), ':')",
column1 POSITION(5:2000) CHAR)
Kindly help in this regard.
Thanks,
Pratima
and Chris said...
To change your likes equalities, just compare the first N characters to your string. e.g.:
WHEN (1:3) = ':86'
To load records that span multiple lines you can use the str clause of infile. But you need a character to mark the end of records to do this!
For example, if you can have a pipe (|) at the end of each record, you can load multiple lines as one row with:
INFILE data.dat "STR '|\r\n'"
But you'll need your file to look something like this:
:20:9400124576021331|
:25:200340027|
:28:141|
:60F:C160801KZT21637948,20|
:61:1608010801CT70250000,00NFEX1162140060//1162140055|
:86:059?00Commissions And Charges?20/PY/КПН ЗА НЕРЕЗИДЕНТА КОМПАН
И АО МХК ЕВРОХИМ РОССИЯ (ДОГОВОР ?21ЗАЙМА №001-0655165 ОТ 30.0
4.2015Г.).?22/BN/НАЛОГОВОЕ УПРАВЛЕНИЕ ПО БОСТАНДЫКСК?23/BI/KZ2407
0105KSN0000000?24/AB/KKMFKZ2A ГУ КОМИТЕТ КАЗНАЧЕЙСТВА МФ РК АЛМАТ
Ы KZ24070105KSN00?2500000?26/OA/KZT|
:62F:C160801KZT42536082,21|
:64:C160801KZT42536082,21|
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment