XLS Loading

[FFBB1983]

I found this is very strange to load the attached .xls file. It is all good when I use MS Excel to open this file. But I got an error message when created a new App and I loaded this file from the Data Load Editor. 

the following is the script I wrote,

for each file in filelist('lib://BUSINESS_DROP/LSU3PL\DAILY\Daily_XLS\*')

CONCATENATE(SALES_TMP)
LSU3PL_DAILY_SALES:
LOAD 'LSU3PL' AS DataSource,
'RETAIL' AS Channel,
'GROSS SALES' AS SalesType,
filetime() AS FileTime,
filename() as FileName,
"Bill-to Name" AS BillToName,
"Sold-to
(Postal Code)" AS PostalCode,
"Product Description" AS ProductDescription,
"Document #" AS OrderNumber,
"Invoice
Date" AS InvoiceDate, //// KEEP

"Qty
Invoiced" AS InvoicedQty,
"Total" AS TotalSales

FROM [$(file)]
(biff, embedded labels, header is 2 lines, table is Sheet1$)
WHERE NOT ISNULL("Qty
Invoiced")
;

next file

[Yoshidaqlik]

HI

Do you already have a table named SALES_TMP before the loop code?

case YES:

for each file in filelist('lib://BUSINESS_DROP/LSU3PL\DAILY\Daily_XLS\*')

CONCATENATE(SALES_TMP)
LOAD 'LSU3PL' AS DataSource,
'RETAIL' AS Channel,
'GROSS SALES' AS SalesType,
filetime() AS FileTime,
filename() as FileName,
"Bill-to Name" AS BillToName,
"Sold-to
(Postal Code)" AS PostalCode,
"Product Description" AS ProductDescription,
"Document #" AS OrderNumber,
"Invoice
Date" AS InvoiceDate, //// KEEP

"Qty
Invoiced" AS InvoicedQty,
"Total" AS TotalSales


FROM [$(file)]
(biff, embedded labels, header is 2 lines, table is Sheet1$)
WHERE NOT ISNULL("Qty
Invoiced")
;

next file

case NO

for each file in filelist('lib://BUSINESS_DROP/LSU3PL\DAILY\Daily_XLS\*')

LSU3PL_DAILY_SALES:
LOAD 'LSU3PL' AS DataSource,
'RETAIL' AS Channel,
'GROSS SALES' AS SalesType,
filetime() AS FileTime,
filename() as FileName,
"Bill-to Name" AS BillToName,
"Sold-to
(Postal Code)" AS PostalCode,
"Product Description" AS ProductDescription,
"Document #" AS OrderNumber,
"Invoice
Date" AS InvoiceDate, //// KEEP

"Qty
Invoiced" AS InvoicedQty,
"Total" AS TotalSales


FROM [$(file)]
(biff, embedded labels, header is 2 lines, table is Sheet1$)
WHERE NOT ISNULL("Qty
Invoiced")
;

next file

[FFBB1983]

I do have "SALES_TMP" in the loop. What did you change in your Case YES?

Make it simple, in a brand new APP, I couldn't load from the following script.

SALES_TMP:
LOAD
"Invoice
Date" AS InvoiceDate,
"Qty
Invoiced" AS InvoicedQty,
"Total" AS TotalSales

FROM [lib://BUSINESS_DROP/LSU3PL\DAILY\Daily_XLS\zsalescatl-20190902-223525.xls]
(biff, embedded labels, header is 2 lines, table is Sheet1$)
;

[rwunderlich]

I've seen this problem before. If you open the file in excel and re-save it, the problem will likely go away. 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[FFBB1983]

This is what I am doing every day. I resave the .xls file as .xlsx file and load without any issue. 

But what makes it frustrated is I receive this file from distributor every day and I have to convert it manually... 

It will make my life so much easier if you can get the scripts to load this file without any conversion.

[marcus_sommer]

An alternatively might be to load the excel per odbc. Also possible is to use a macro to automate the task - it might be done directly with Excel or just with vbs and it may be triggered through a windows task or directly from Sense with EXECUTE (AFAIK you will need to use the legacy mode for it).

- Marcus

[rwunderlich]

The file you are receiving from your distributor is actually a .zip file. Either run a batch step to unzip the file or ask your distributor to send an uncompressed version. 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[FFBB1983]

Hi Rob, thanks for looking at this case. The file I received is not a zip file. It is in the .xls format and I attached it in my very first post.

The loading is fine when I convert it from the .xls to the .xlsx. Just curious about how to load this  .xls file by using the script. The strange thing is my .xls loading script works well for the .xls from other distributors, but not working for this one. When I test this file by MS Excel, it is a totally normal excel file.

[rwunderlich · Accepted solution]

The file you posted is in the compressed (zip) format. It just has a xls extension. Excel is smart enough to detect that it is a compressed file and will decompress it.  Qlik will not. 

Asking excel to save the file will save the uncompressed version (27kb) which Qlik can read. 

You can test this by renaming the file to have a .zip extension and then extract it using any extract tool.

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[FFBB1983]

Thanks Rob! This makes sense!!