12 Replies Latest reply: Jan 10, 2018 7:07 AM by Yeo Poh sai RSS

    How to convert my load script to read excel file ?

    Yeo Poh sai

      Hi All

       

      I have a load script which run for 6 years. Now the raw data file need to change to excel file.

       

      May i know what is the fast way to modify my script ?

       

      I mean now my load script read txt file , but now raw data file change to excel file xls file format.

       

      One option is :-

       

      I can convert excel file to txt file format , but when i try to convert , it does not able to get 100%. so this option not possible.

       

      Second option is :-

       

      I need to modify my script. which need to spend min 4 hour.

       

      Does any one here , can suggest me other approach which is more productive.

        • Re: How to convert my load script to read excel file ?
          Anil Samineni

          options i have

          1) May be use Ctrl + F to replace the string from and to for all Tabs?

          2) If you stored the path in Variable just replace that

            • Re: How to convert my load script to read excel file ?
              Yeo Poh sai

              Hi Anil

               

              I just try Control + F in excel is find and replace.

               

              PAul

                    • Re: How to convert my load script to read excel file ?
                      Anil Samineni

                      Can you come up with what you are doing here. May be sample script and you want to accomplish ??

                        • Re: How to convert my load script to read excel file ?
                          Yeo Poh sai

                          Hi Anil

                           

                          Below is my script , it read my raw data file txt format no issue. But then now the data file in XLS format :-

                           

                          //LOAD left(FileBaseName(), 4) AS Report4_invc,


                          load


                          //'TDSS' as SOURCE_STK,


                          'TDSS' as SOURCE,



                          Ceil(Num(if(num(((date(today(),'DD/MM/YYYY') - date(@168:179,'DD/MM/YYYY'))/365))=0,1,num(((date(today(),'DD/MM/YYYY') - date(@168:179,'DD/MM/YYYY'))/365)))*12)) as  [No of Months_],


                          //Ceil(Num(if(num(((date(today(),'DD/MM/YYYY') - date(@180:190,'DD/MM/YYYY'))/365*2))=0,1,num(((date(today(),'DD/MM/YYYY') - date(@180:190,'DD/MM/YYYY'))/365*2)))*12)) as  [No of Months_],


                          Ceil(Num(if(num(((date(today(),'DD/MM/YYYY') - date(@168:179,'DD/MM/YYYY'))/365*2))=0,1,num(((date(today(),'DD/MM/YYYY') - date(@168:179,'DD/MM/YYYY'))/365*2)))*12)) as  [No of Months],


                          @168:179T as [L_issue],

                          @180:190T as [L_move],

                           


                          year(@180:190T) as [YEAR_],


                          Date(Monthstart(@180:190T), 'YY MMM') as YearMonth,


                          // Right(year(TempDate),2) as YEAR_,


                          Right(year(@180:190T ),2) as YEAR,

                           

                          year(@180:190T)                                          as Year,




                          if(date(@180:190,'DD/MM/YYYY') > '$(YearBoundary3)',dual('No', 0), dual('Yes', 1)) as  [Purchase > 3yr No Sales],


                          if(date(@168:179,'DD/MM/YYYY') > '$(YearBoundary3)',dual('No', 0), dual('Yes', 1)) as  [No Sales > 3yr],


                          if(date(@180:190,'DD/MM/YYYY') > '$(YearBoundary2)',dual('No', 0), dual('Yes', 1)) as  [Purchase > 2yr No Sales],


                          if(date(@168:179,'DD/MM/YYYY') > '$(YearBoundary2)',dual('No', 0), dual('Yes', 1)) as  [No Sales > 2yr],



                          if(date(@180:190,'DD/MM/YYYY') > date(@168:179,'DD/MM/YYYY'), dual('No', 0), dual('Yes', 1)) as  [Pur date more recent then last invoice date],


                          if(date(@168:179,'DD/MM/YYYY')>'$(YearBoundary2)' or date(@180:190,'DD/MM/YYYY')>  '$(YearBoundary2)','0','1') as  [FLAG_2YR],


                          if(date(@168:179,'DD/MM/YYYY')>'$(YearBoundary3)' or date(@180:190,'DD/MM/YYYY')>  '$(YearBoundary3)','0','1') as  [FLAG_3YR],


                          //


                          if(date(@168:179,'DD/MM/YYYY')>'$(YearBoundary3)' or date(@180:190,'DD/MM/YYYY')>  '$(YearBoundary3)','0','1')

                          & ' / ' &

                          if(date(@168:179,'DD/MM/YYYY')>'$(YearBoundary2)' or date(@180:190,'DD/MM/YYYY')>  '$(YearBoundary2)','0','1') as  [FLAG_2YR_3YR],


                          if(date(@168:179,'DD/MM/YYYY')>'$(YearBoundary3)' or date(@180:190,'DD/MM/YYYY')>  '$(YearBoundary3)','1')

                          & ' / ' &

                          if(date(@168:179,'DD/MM/YYYY')>'$(YearBoundary2)' or date(@180:190,'DD/MM/YYYY')>  '$(YearBoundary2)','1') as  [FLAG_2YR_3YR_],


                          //   


                          if(date(@168:179,'DD/MM/YYYY')>'$(YearBoundary3)' or


                            date(@180:190,'DD/MM/YYYY')>  '$(YearBoundary3)' or


                            date(@168:179,'DD/MM/YYYY')>'$(YearBoundary2)' or


                            date(@180:190,'DD/MM/YYYY')>  '$(YearBoundary2)',


                            '0',


                            if(date(@168:179,'DD/MM/YYYY')<='$(YearBoundary3)' or


                              date(@180:190,'DD/MM/YYYY')<='$(YearBoundary3)' or


                              date(@168:179,'DD/MM/YYYY')<='$(YearBoundary2)' or


                                date(@180:190,'DD/MM/YYYY')<='$(YearBoundary2)', '1', null()))

                                 

                              as [FLAG_2YR_3YR_F],


                          @1:11T as [PRODUCT_CODE_INV],


                          // @1:11T as [product code],


                          // @12:42T as [desc],


                          @12:42T as [DESC_INVENTORY_TABLE],



                          if (subfield(@12:42T,' ',1)='HAKKO','HAKKO'

                          ,if (subfield(@12:42T,' ',2)='TS1070', 'TS'

                          ,if (subfield(@12:42T,' ',4)='5M', '5M'

                          ,if (subfield(@12:42T,' ',2)='TS1070', 'TS1070'

                          ,if (subfield(@12:42T,' ',3)='CABLE', 'CABLE'

                          ,if('Brand'='','','OTHER')))))) as [TS],



                          ApplyMap('BrandMap', subfield(@12:42T,' ',2), 'No') AS Product_Class_,



                          // subfield(@12:42T,' ',1) as Brand_inv,


                          if (subfield(@12:42T,' ',1)='HAKKO','HAKKO'

                          ,if (subfield(@12:42T,' ',1)='BECKHOFF','BECKHOFF'

                          ,if (subfield(@12:42T,' ',1)='OMRON', 'OMRON'

                          ,if (subfield(@12:42T,' ',1)='GE', 'GE FANUC'

                          ,if (subfield(@12:42T,' ',1)='MITSUBISHI', 'MITSUBISHI'

                          ,if (subfield(@12:42T,' ',1)='HENGSTLER', 'HENGSTLER'

                          ,if (subfield(@12:42T,' ',1)='YAMATAKE', 'YAMATAKE'

                          ,if (subfield(@12:42T,' ',1)='PHOENIX', 'PHOENIX CONTACT'

                          ,if (subfield(@12:42T,' ',1)='P&F', 'PEPPERL+FUCHUS'

                          ,if (subfield(@12:42T,' ',1)='YASKAWA', 'YASKAWA'

                          ,if (subfield(@12:42T,' ',1)='HONEYWELL', 'HONEYWELL'

                          ,if (subfield(@12:42T,' ',1)='PILZ', 'PILZ'

                          ,if (subfield(@12:42T,' ',1)='SCHAFFNER', 'SCHAFFNER'

                          ,if (subfield(@12:42T,' ',1)='REDLION', 'REDLION'

                          ,if (subfield(@12:42T,' ',1)='WEST', 'WEST'

                          ,if (subfield(@12:42T,' ',1)='VISOLUX', 'VISOLUX'

                          ,if (subfield(@12:42T,' ',1)='TDK', 'TDK'

                          ,if (subfield(@12:42T,' ',1)='TNS', 'TNS'

                          ,if (subfield(@12:42T,' ',1)='MOXA', 'MOXA'

                          ,if (subfield(@12:42T,' ',1)='AUTENTO', 'AUTENTO'

                          ,if (subfield(@12:42T,' ',1)='HMS', 'HMS'

                          ,if('Brand'='','','OTHER')))))))))))))))))))))) as [BRAND_INV_SUB],


                          if (subfield(@12:42T,' ',1)='HAKKO','HAKKO'

                          ,if (subfield(@12:42T,' ',1)='BECKHOFF','BECKHOFF'

                          ,if (subfield(@12:42T,' ',1)='OMRON', 'OMRON'

                          ,if (subfield(@12:42T,' ',1)='GE', 'GE FANUC'

                          ,if (subfield(@12:42T,' ',1)='MITSUBISHI', 'MITSUBISHI'

                          ,if('Brand'='','','OTHER')))))) as [BRAND_INV],


                          // ApplyMap('BrandMap', subfield(@12:42T,' ',1), 'OTHERS') AS BRAND_INV,

                          //    ApplyMap('BrandInvMap', subfield(@12:42T,' ',1), 'OTHERS') AS BRAND_INV_SUB,


                           

                          @43:53T as [QTY_AVA1],


                          if(right(@43:53T,1)='-', '-' & left(@43:53T, len(@43:53T)-1),@43:53T) as QTY_AVA,




                          if(right(@54:64T,1)='-', '-' & left(@54:64T, len(@54:64T)-1),@54:64T) as QTY_O,


                          (@43:53T)*(@65:75T) as D_AVA_AMT,


                          @65:75T as [COS],

                          @54:64T as [QTY_O1],

                          (@54:64T)*(@65:75T) as C_ONHAND_AMT,

                           

                          @76:81T as [S_1],

                           

                          if(right(@76:81T,1)='-', '-' & left(@76:81T, len(@76:81T)-1),@76:81T) as S1,


                          @82:87T as [S_2],

                          if(right(@82:87T,1)='-', '-' & left(@82:87T, len(@82:87T)-1),@82:87T) as S2,


                          @88:93T as [S_3],

                          if(right(@88:93T,1)='-', '-' & left(@88:93T, len(@88:93T)-1),@88:93T) as S3,


                          @94:99T as [S_4],

                          if(right(@94:99T,1)='-', '-' & left(@94:99T, len(@94:99T)-1),@94:99T) as S4,


                          @100:105T as [S_5],

                          if(right(@100:105T,1)='-', '-' & left(@100:105T, len(@100:105T)-1),@100:105T) as S5,


                          @106:111T as [S_6],

                          if(right(@106:111T,1)='-', '-' & left(@106:111T, len(@106:111T)-1),@106:111T) as S6,


                          @112:117T as [S_7],

                          if(right(@112:117T,1)='-', '-' & left(@112:117T, len(@112:117T)-1),@112:117T) as S7,


                          @118:123T as [S_8],

                          if(right(@118:123T,1)='-', '-' & left(@118:123T, len(@118:123T)-1),@118:123T) as S8,


                          @124:129T as [S_9],

                          if(right(@124:129T,1)='-', '-' & left(@124:129T, len(@124:129T)-1),@124:129T) as S9,


                          @130:135T as [S_10],

                          if(right(@130:135T,1)='-', '-' & left(@130:135T, len(@130:135T)-1),@130:135T) as S10,


                          //Averaging

                          (((((@76:81T)+(@82:87T)+(@88:93T)+(@94:99T)+(@100:105T)+(@106:111T)+(@112:117T)+(@118:123T)+(@124:129T)+(@130:135T)+(@136:141T)+(@142:147T))/12)*(@65:75T))) as [B_COGS],


                          num(((@76:81T)+(@82:87T)+(@88:93T)+(@94:99T)+(@100:105T)+(@106:111T)+(@112:117T)+(@118:123T)+(@124:129T)+(@130:135T)+(@136:141T)+(@142:147T))/12) as [AVERAGE],


                          @136:141T as [S_11],


                          if(right(@136:141T,1)='-', '-' & left(@136:141T, len(@136:141T)-1),@136:141T) as S11,


                          // @142:147T as [S_12],



                          // @142:147T as [S_12],


                          @142:147T as [S_12],


                          if(right(@142:147T,1)='-', '-' & left(@142:147T, len(@142:147T)-1),@142:147T) as S12,


                           


                          // @148:157T as [PROD_CLS],


                          @191:200T as [onorder],


                          @235:285T as [desc_short],


                          @286:296T as [invc_date],


                          Date(Monthstart(@286:296T), 'DD-MMM-YYYY') as YearMonthDay1,

                           

                          @297:319T as [manuf_no],

                          @297:319T as [PART_NO_],

                          @297:319T as [PART_NO],

                           

                          @320:354T as [M_DATE_],


                          @331:358T as [PRO_CLS_],


                          Date(Monthstart(@320:354T), 'DD-MMM-YYYY') as M_DATE

                           

                          // Date(Monthstart(@344:354T), 'DD-MMM-YYYY') as YearMonthDay1



                          FROM $(vRAWPath)$(vFile3) (ansi, fix, no labels, header is 0, record is line);

                           


                          //LOAD * INLINE [

                          //    F1, LTMonths

                          //    A, 0.5

                          //    B, 1

                          //    C, 1.5

                          //    D, 2

                          //];