Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
FFBB1983
Contributor III
Contributor III

Xls files loading issue

Hi Everyone, 

I have the attached .xls. I have been trying to 1) write a script and 2)drag and drop for the autoload. But I can't load in either way. This file appears to be normal when I open from MS Excel. However, when I  save this file as .xlsx, everything is working well.

I saw the trick about "ooxml" and "biff" in the community. I think I didn't make it wrong for this case. I will have to take xls as this is the format from our supplier daily. It's not fun to convert the format before I load every day.

The following are the error msg I got and my script. Any help is very appreciated!!

image.png

 

for each file in filelist('lib://BUSINESS_DROP/LSU3PL\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, 

"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

 

1 Reply
FFBB1983
Contributor III
Contributor III
Author

Capture.PNG

 

This is the error message I got from the drag and drop view. If anyone knows how to solve this problem. Please help!