Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm importing semi colon delimited data from a text file that has a field that is exactly sixteen characters long usually with extra spaces left on the end. Those extra spaces are extremely important when I concatenate that field along with others because it forms the central key to the entire database. I need to concatenate this data into my main table through this script, but the resultant output has no spaces in it. Here is my script.
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';Set Verbatim=1;
Directory;
Forecast:
$(Include=\\Master\Load Forecast.txt)
Forecast1:
LOAD *,
date(makedate(left([Week End Date],4),mid([Week End Date],5,2),mid([Week End Date],7,2)),'YYYY/MM/DD') as [Production Date]
Resident Forecast;DROP Table Forecast;
Concatenate (Forecast1) LOAD Weekend(date(makedate(left([DUE DATE],4),mid([DUE DATE],5,2),mid([DUE DATE],7,2)),'YYYY/MM/DD'))+7 as [Production Date],
CST & STYLE & SIZE & LEFT(COLOR,5) as [Customer SKU],
NUM([OPEN QUANTITY],'#,##0') as [PO Pairs]
FROM
\\Vmi\fcstdata\PO8000.TXT
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);CONCATENATE (Forecast1) LOAD Weekend(date(makedate(left([ETA DATE],4),mid([ETA DATE],5,2),mid([ETA DATE],7,2)),'YYYY/MM/DD'))+7 as [Production Date],
CST & STYLE & SIZE & LEFT(COLOR,5) as [Customer SKU],
NUM([OPEN QUANTITY],'#,##0') as [PO Pairs]
FROM
\\Vmi\fcstdata\PO8020.TXT
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);
The text files I'm working from (PO8000.TXT and PO8020.TXT) have the correct amount of characters except for COLOR having one extra that I removed via the LEFT function.
Why isn't the SET VERBATIM=1; command working?
Thanks,
Gr8Scott
Hi gr8scott,
still not sure why the Verbatim is not working, honestly I haven't used it up to now.
As a workaround, you could fill the field with spaces (chr(32)) up to 16 characters in total, like
STYLE&repeat(chr(32),16-len(STYLE)) as STYLE,
CST & STYLE&repeat(chr(32),16-len(STYLE)) & SIZE & LEFT(COLOR,5) as [Customer SKU],
Hope this helps,
Stefan
Hi Scott,
is it possible that you upload your two text files or parts of them here in the forum (upload is availabe from advanced editor)?
Stefan
PO NUMBER ;STATUS; SOURCE ; SOURCE NAME ;CTRY;RECD;ORDER DT;ORIG DUE;DUE DATE;PCKUP DT;ETA DATE;TYPE;LV;CST;STYLE ;SIZE ;COLOR ;FG;SUPPLIER ITEM ; OPEN QUANTITY ; PO NUMBR2;MGR;SHIPMENT NR.;CONTAINER ;CRE DATE;
000102695801; 800 ;58800005;NAIGAI CO. LTD ;5880;D ;20110617;20111015;20111015;20111015;20111014; 010;60;972;81131 ;100130;BLACK ;30;81131 1013 BLACK ; 000000079.000000;0001026958;MM ;000044468031;000044468030;20110909;
000102695801; 800 ;58800005;NAIGAI CO. LTD ;5880;D ;20110617;20111015;20111015;20111015;20111014; 010;60;972;84794 ;100130;ASST ;30;225457/225451/225459/225455 ; 000000026.000000;0001026958;MM ;000044468031;000044468032;20110909;
PO NUMBER ;STATUS; SOURCE ; SOURCE NAME ;CTRY;RECD;ORDER DT;ORIG DUE;DUE DATE;PCKUP DT;EX-V DT ;TYPE;LV;CST;STYLE ;SIZE ;COLOR ;FG;SUPPLIER ITEM ; OPEN QUANTITY ;BR; PO NUMBR2;MGR;
000000010201; 400 ;57000200;ZHEJIANG FOUR SEASONS HOSIERY ;5700;2 ;20110920;20111227;20111227;20111227;20111204; 010;60;961;64573A-WHITE ;090110;WHITE ;01;64573A-WHITE 090110 WHITE ; 000000096.000000;KB;0000000102;DMS;
000000010201; 400 ;57000200;ZHEJIANG FOUR SEASONS HOSIERY ;5700;2 ;20110920;20111227;20111227;20111227;20111204; 010;60;961;51617A-BLUE ;090110;BLUE ;01;51617A-BLUE 090110 BLUE ; 000000096.000000;KB;0000000102;DMS;
This should be about two lines worth of data from PO8020.txt first and then two lines from PO8000.txt. Both include the headers as well.
Hi gr8scott,
still not sure why the Verbatim is not working, honestly I haven't used it up to now.
As a workaround, you could fill the field with spaces (chr(32)) up to 16 characters in total, like
STYLE&repeat(chr(32),16-len(STYLE)) as STYLE,
CST & STYLE&repeat(chr(32),16-len(STYLE)) & SIZE & LEFT(COLOR,5) as [Customer SKU],
Hope this helps,
Stefan
That helps trememdously Stefan. Thank you very much.