3 Replies Latest reply: Oct 24, 2018 3:44 AM by Gabor Tarnoczai RSS

    why is it not concatenating?

    zhen pan

      I am trying to concatenate all my files in a folder like this :

       

      for each Dir in dirlist ('$(vDataFilePath)'&'\*' )

      trace $(Dir);


      for each file in FileList('$(Dir)/*.*')


      trace 'file is' $(file);

       

         A:

          load

      *

          from [$(file)]

         (ooxml, embedded labels, header is 7 lines, table is [F Data]);

      next file;


      When I load data, i got error messages below, what would be the problem.


      the files all have the same number of field names and the field names are the same.

      Also how to store the files into different qvd based on the file name.

      for example:

      file name is CLP2 - 2016 Dec 31 Tax Estimate Allocations.xlsm

      I want to get everything before year,like: CLP2 - 2016.qvd, CLP2 - 2017.qvd However the name CLP2 is not fixed it can be longer sometimes.

       

       

       

      Started loading data

      lib://ZHPAN (ad_zhpan)/Data/C

      'file is' lib://ZHPAN (ad_zhpan)/Data/C/CLP2 - 2016 Dec 31 Tax Estimate Allocations.xlsm ' 

      A << F Data

      Lines fetched: 140

      'file is' lib://ZHPAN (ad_zhpan)/Data/C/CLP2 - 2017 Dec 31 Tax Estimate Allocations.xlsm '

      A-1 << F Data Lines fetched: 111 'file is' lib://ZHPAN (ad_zhpan)/Data/C/CLP2 - 2018 Jul 31 Tax Estimate Allocations.xlsm '

      A-2 << F Data

      Lines fetched: 116 $Syn 1 = PTDI_PTRNUM+PTDI_PTRKEYID+PTDI_OKID1+PTDI_OKID2+PTDI_NAME1+PTDI_NAME2+PTDI_DOMFOR+PTDI_USRESID+PTDI_ENTTYPE+FDK1_BOYPPCT+FDK1_BOYLPCT+FDK1_BOYCAP+FDK1_EOYPPCT+FDK1_EOYLPCT+FDK1_EOYCAP+FDK1_NONRECRSE+FDK1_QLNONRECRSE+FDK1_RECRSE+FDK1_BEGCAP+FDK1_CONTR+FDK1_PTRINC+FDK1_WITHDWLS+FDK1_ENDCAP+FDK1_GAINPROP+FDK1_FINAL+FDK1_AMEND+FDK1_ORDINC+FDK1_RRE+FDK1_OTHRENT+FDK1_GRNTPAY+FDK1_INTINC+FDK1_ORDDIV+FDK1_QUALDIV+FDK1_ROY+FDK1_NSTGL+FDK1_NLTGL+FDK1_COLGL+FDK1_1250GL+FDK1_1231GL+FDK1_OTRPORT+FDK1_INVOLCONV+FDK1_1256+FDK1_MNGCOSTREC+FDK1_CANDEBT+FDK1_OTHINC+FDK1_179DED+FDK1_CASHCONT50+FDK1_CASHCONT30+FDK1_NCASHCONT50+FDK1_NCASHCONT30+FDK1_CGPROP50+FDK1_CGPROP20+FDK1_CONT100+FDK1_INVINTEXP+FDK1_DEDROYINC+FDK1_59E2EXP+FDK1_DEDPORT2+FDK1_DEDPORTOTH+FDK1_AMTMEDINS+FDK1_EDUASSBEN+FDK1_DEPCAREBEN+FDK1_PREPEREXP+FDK1_COMREVIT+FDK1_PENIRA+FDK1_REFOREXPDED+FDK1_DOMPRODACT+FDK1_QUALPRODINC+FDK1_EMPW2WAGE+FDK1_OTHDED+FDK1_SELFEMPEARN+FDK1_FARMFISHINC+FDK1_NFARMINC+FDK1_42J5PRE08+FDK1_OTHPRE08+FDK1_42J5POST07+FDK1_OTHPOST07+FDK1_RREEXP+FDK1_OTHRRECR+FDK1_OTHRENTCR+FDK1_UNDISCGCR+FDK1_FUELCR+FDK1_OPPCR+FDK1_DISACCCR+FDK1_EZRCECR+FDK1_RESACTCR+FDK1_SSMEDCR+FDK1_BACKUPWH+FDK1_OTHCR+FDK1_COUNTRY+FDK1_GROSSINC+FDK1_PTNRINC+FDK1_PASSIVE+FDK1_GENERAL+FDK1_OTHER+FDK1_INTEXP+FDK1_OTHER2+FDK1_PASSIVE2+FDK1_GENERAL2+FDK1_OTHER3+FDK1_FTAXPAID+FDK1_FTAXACC+FDK1_TAXRED+FDK1_FTRADEGROSS+FDK1_EXTINCEXC+FDK1_OTHFTRANS+FDK1_86DEPADJ+FDK1_ADJGL+FDK1_DEPLETION+FDK1_OGGINC+FDK1_OGGDED+FDK1_OTHAMT+FDK1_TAXEXEINT+FDK1_OTHTAXEXE+FDK1_NDEDEXP+FDK1_CMSEC+FDK1_737+FDK1_OTHPROP+FDK1_INVINC+FDK1_INVEXP+FDK1_FUELCRINF+FDK1_QUALREHEXP+FDK1_BASISEPROP+FDK1_RECAP42J5+FDK1_RECAPOTH+FDK1_RECAPINVCR+FDK1_RECAPOTHCR+FDK1_LBILT+FDK1_LBIIFM+FDK1_DIS179DED+FDK1_RECAP179DED+FDK1_INTEXPPTNR+FDK1_453L3INF+FDK1_453ACINF+FDK1_1260BINF+FDK1_INTPRODEXP+FDK1_CCFNQW+FDK1_DEPLOG+FDK1_AMORTREFORE+FDK1_UBTI+FDK1_PRECONTGL+FDK1_108IINFO+FDK1_NETINVINC+FDK1_OTHINFO+FDK1_TAXINC+FDK1_ANALYSISPTYPE+FDK1_CORP+FDK1_DISENTITY+FDK1_ESTATE+FDK1_FIDUCIARY+FDK1_GRANTORTRUST+FDK1_INDIV+FDK1_LLC+FDK1_NOMINEE+FDK1_PSHIP+FDK1_IRA+FDK1_SCORP+FDK1_TRUST+FDK1_CYINCDEC_1+FDK1_CYINCDEC_2+FDK1_CYINCDEC_3+FDK1_CYINCDEC_4+FDK1_CYINCDEC_5+FDK1_CYINCDEC_6+FDK1_CYINCDEC_7+FDK1_CYINCDEC_8+FDK1_CYINCDEC_9+FDK1_CYINCDEC_10+FDK1_CYINCDEC_11+FDK1_CYINCDEC_12+FDK1_CYINCDEC_13+FDK1_CYINCDEC_14+FDK1_CYINCDEC_15+FDK1_ORDIPASSIVE_1+FDK1_ORDINONPASSIVE_1+FDK1_GOVTINTINC_5+FDK1_ISSDISUS_5+FDK1_ISSDISFN_5+FDK1_MRKTDISUS_5+FDK1_MRKTDISFN_5+FDK1_OTHINTINC_5+FDK1_OTHINTINCFN_5+FDK1_DIVINCUS_6A+FDK1_DIVINCFN_6A+FDK1_DIVINCUS_6B+FDK1_DIVINCFN_6B+FDK1_OTHERSHTCG_8+FDK1_SHTCGDIS_8+FDK1_OTHERLTCG_9+FDK1_LTCGDIS_9+FDK1_988NONMAJINCLOSS_11A+FDK1_988MAJINCLOSS_11A+FDK1_987INCLOSS_11A+FDK1_SWAPINCAG_11A+FDK1_SWAPINCNONAG_11A+FDK1_11ACUSTOM_1+FDK1_1256TRAD+FDK1_1256INVEST+FDK1_ORDINCTRAD+FDK1_STCAPGAINLOSS+FDK1_SHTCGLDIS+FDK1_LTCAPGAINLOSS+FDK1_LTCGLDIS+FDK1_11FCUSTOM_1+FDK1_GOVTINTINC_11F+FDK1_ORGDISUS_11F+FDK1_ORGDISFN_11F+FDK1_MKTDISUS_11F+FDK1_MKTDISFN_11F+FDK1_OTHINTINC_11F+FDK1_OTHINTINCFN_11F+FDK1_DIVINCNONQUAL+FDK1_DIVINCNONQUALFN_11F+FDK1_DIVINCQUAL+FDK1_DIVINCQUALFN_11F+FDK1_988NONMAJINCLOSS_11F+FDK1_988MAJINCLOSS_11F+FDK1_987INCLOSS_11F+FDK1_TRADBUSEXP+FDK1_OTHTRADBUSEXP+FDK1_ORDINCMTMELEC+FDK1_SWAP111F+FDK1_SWAP211F+FDK1_11FCUSTOM_2+FDK1_11FCUSTOM_3+FDK1_INVINTEXPINT+FDK1_INVINTEXPTRAD+FDK1_MGMTFEE+FDK1_SWAP113K+FDK1_SWAP213K+FDK1_13KCUSTOM_1+FDK1_13KCUSTOM_2+FDK1_13KCUSTOM_3+FDK1_13KCUSTOM_4+FDK1_13WCUSTOM_1+FDK1_13WCUSTOM_2+FDK1_13WCUSTOM_3+FDK1_13WCUSTOM_4+FDK1_USWTHTAX+FDK1_15PCUSTOM_1+FDK1_UBTIORD+FDK1_UBTISTCAP+FDK1_UBTILTCAP+FDK1_DIVDED+FDK1_FORQUALDIV+FDK1_EXPUSGOVTOBL+FDK1_INTUSGOVTOBL+FDK1_USGOVTDIVINC+FDK1_QUALDIVUBTI+FDK1_TOTFORDIV+FDK1_BONUSDEP2011+FDK1_BONUSDEP2012+FDK1_BONUSDEP2013+FDK1_BONUSDEP2014+FDK1_BONUSDEP2015+K1SUITEEND+FDK1_BONUSDEP2016

      Creating search index Search index creation completed successfully

      App saved

      Finished successfully

      0 forced error(s)

      1 synthetic key(s)

       

        • Re: why is it not concatenating?
          Gabor Tarnoczai

          Hi Zhen,

           

          It seems one or multiple file has different column though.

          You can check it at data model viewer. You should see a table name A and a table name A-1 and A-2, the tables are linked by a synthetic table. The rest of the fields are the difference.

           

          The files are excel, may be there are empty columns, that are used in the files. In this case the columns are loaded with @1, @2 or A, B,C fieldname and all data is null.

           

          I recommend you to delve into those files.

           

          G.

            • Re: why is it not concatenating?
              zhen pan

              Thanks Gabor,

              I Listed all fields that I need one by one and that resolved the problem also

              I also tried

               

              set VConcat=;

              A:

                 $(VConcat)

              load * from

              $(files)

                (ooxml, embedded labels, header is 7 lines, table is [Federal Data]);

              set VConcat=Concatenate;

                

              next file;

               

              but the cloud does not recognize this it will show you red, but if i run it, it can be concatenated without error.

              Maybe a bug ? not sure.

               

              thanks for you help!

                • Re: why is it not concatenating?
                  Gabor Tarnoczai

                  Hi Zhen,

                   

                  I observed similar issue with the data load editor as well, it highlight as syntax error, but the engine can run it.

                  For example: let test = 'test123 ''abc'' test 123'  - in this case the ' character was exited in string, but the editor parse it as error.

                   

                  Don't worry about it, if it runs correctly.

                   

                  G.