topic Re: Unknown LOAD statement error in App Development

Unknown LOAD statement error

EvanBarrick — Tue, 26 Mar 2019 13:52:04 GMT

I am attempting to run the following script:

thsSALESDATA:
BUFFER (incremental) LOAD * FROM [lib://THS Aggregate (brand_mike.smith)/*.xlsx](ooxml, embedded labels, table is THS);

STORE thsSALESDATA INTO [lib://QVD (brand_mike.smith)/THSaggregate.qvd(qvd)];

There are three files in the directory from which I am loading; they all have the same number of columns and exactly the same headers.

What could be causing this error?

Re: Unknown LOAD statement error

dplr-rn — Tue, 26 Mar 2019 14:21:15 GMT

can you share the error log/output

Re: Unknown LOAD statement error

EvanBarrick — Tue, 26 Mar 2019 14:26:01 GMT

The following error occurred:

Unknown LOAD statement error

The error occurred here:

TMsalesData: BUFFER (incremental) LOAD * FROM [lib://TMaggregate (brand_mike.smith)/*.xlsx](ooxml, embedded labels, table is TM)

The following error occurred:

Unknown LOAD statement error

The error occurred here:

Re: Unknown LOAD statement error

dplr-rn — Tue, 26 Mar 2019 14:33:48 GMT

Hard to help my friend.
but buffer incremental afik only works on text files (csvs) not xlsx
not sure if this is the cause but worth a try

Re: Unknown LOAD statement error

EvanBarrick — Tue, 26 Mar 2019 14:38:00 GMT

I have used this script for similar directories with .xlsx files several times, which is why this is puzzling

Re: Unknown LOAD statement error

dplr-rn — Tue, 26 Mar 2019 14:45:01 GMT

to identify the error i recommend Trying to load the files without the *.xlsx
if you have a lot of files there try a loop like below you can eliminate issues with the files

set vRoot = '.';
FOR Each File in filelist ('$(vRoot)'&'\*.xlsx')
Temp:
LOAD *
FROM
[$(File)]
(ooxml, no labels, table is data);
let qvdname= replace(File,'xlsx','qvd');

next File

Re: Unknown LOAD statement error

EvanBarrick — Tue, 26 Mar 2019 15:08:57 GMT

Thanks for this, where do I designate where my directory in your example?

Re: Unknown LOAD statement error

dplr-rn — Tue, 26 Mar 2019 15:09:45 GMT

vRoot

Re: Unknown LOAD statement error

dplr-rn — Tue, 26 Mar 2019 15:11:42 GMT

personally i dislike load with *.xlsx hard to pin down errors. e.g. someone put a extra space in tab name etc.
i tend to use a loop like this which will error out at the exact file which causes problem

Re: Unknown LOAD statement error

EvanBarrick — Tue, 26 Mar 2019 15:12:09 GMT

set vRoot = '.';

FOR Each File in filelist ('$(The Store Name Agg (brand_mike.smith)\*.xlsx')
Temp:
LOAD *
FROM
[$(File)]
(ooxml, no labels, table is THS);
let qvdname= replace(File,'xlsx','qvd');

next File

Re: Unknown LOAD statement error

EvanBarrick — Tue, 26 Mar 2019 15:19:15 GMT

this is how I set my script but am getting an error

set vRoot = '.';

FOR Each File in filelist ('$(The Store Name Agg (brand_mike.smith)\*.xlsx')
Temp:
LOAD *
FROM
[$(File)]
(ooxml, no labels, table is THS);
let qvdname= replace(File,'xlsx','qvd')

Re: Unknown LOAD statement error

dplr-rn — Tue, 26 Mar 2019 15:22:40 GMT

Sorry i missed next file line at the end and forgot it was qlik sense.

see below change lib://TestLoop to your connection string

set vRoot = 'lib://TestLoop';
FOR Each File in filelist ('$(vRoot)'&'\*.xlsx')
Temp:
LOAD *
FROM
[$(File)]
(ooxml, no labels, table is data);
let qvdname= replace(File,'xlsx','qvd');

next File

Re: Unknown LOAD statement error

EvanBarrick — Tue, 26 Mar 2019 15:26:09 GMT

This loads successfully with no errors. I like this format of loading in the data than what I was trying to do. Thank you

Re: Unknown LOAD statement error

dplr-rn — Tue, 26 Mar 2019 15:27:10 GMT

No problem. glad to help