4 Replies Latest reply: Oct 11, 2011 8:57 AM by gr8scott RSS

    When Verbatim=1; doesn't work.

      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

        • When Verbatim=1; doesn't work.
          Stefan Wühl

          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

            • Re: When Verbatim=1; doesn't work.

              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.