Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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!
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.
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!
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.