topic Re: Need to import blank lines from Excel Workbooks in QlikView

Need to import blank lines from Excel Workbooks

athompsonhp — Tue, 02 Sep 2014 16:33:14 GMT

I have a Vendor who uses Excel to deliver their data. The challenge is they use the row number in Excel as the day of the month and where there was zero business that row is blank. QlikView removes blanks when importing from Excel which creates the challenge since non-business days are not consistent month-to-month.

Is this a bug? Or is there a switch to tell QlikView to fetch all rows from the Excel file?

I would appreciate any suggestion.

Enjoy!

Re: Need to import blank lines from Excel Workbooks

simondachstr — Tue, 02 Sep 2014 16:48:28 GMT

If you save it as .csv file you'll be fine. Seems to me that QlikView removes the blank rows to have the most optimal load. I guess this is something that is communicated via the .xlsx metadata which in itself probably doesn't seem to see much sense & value to keep the empty rows.

Re: Need to import blank lines from Excel Workbooks

athompsonhp — Tue, 02 Sep 2014 16:56:16 GMT

Thanks for your comment.

The actual download process is I am going to their site and saving the file as a download. Actually I am using QlikView to do this process. Remember blank lines do mean something they represent the days where zero business has occurred. Further the Excel workbook contains 12 sheets one for each month of the year. So a direct save to CSV would not work since the workbook contains multiple sheets.

Re: Need to import blank lines from Excel Workbooks

JonnyPoole — Tue, 02 Sep 2014 17:02:03 GMT

Saw the same thing. However using ODBC connection to the same excel spreadsheet worked.

To create the ODBC connection:

1. first open the excel document and highlight the rows/columns in the set and right click -> define name

2. Then open odbc administrator and create a new system DSN using the excel odbc driver

3. select the workbook you are using with the defined name range

4. in qlik, use the odbc connection to create the load

you may also want to add a new field:

Load

recno() as RecordNumber

....to create a unique identifier for the field. You can do that as part of the load statement

Re: Need to import blank lines from Excel Workbooks

simondachstr — Tue, 02 Sep 2014 17:06:09 GMT

Learned something new!

Re: Need to import blank lines from Excel Workbooks

senarath — Wed, 03 Sep 2014 03:28:19 GMT

Hi All,

Can't we use a mapping load and replace null with any string.such as 'No sales' ?

Mapping Load

Null(),

[No Sale],

Autogenerate 1

thanx

Re: Need to import blank lines from Excel Workbooks

evan_kurowski — Wed, 03 Sep 2014 04:45:08 GMT

Hello Andy, Here's a recent example of how to detect completely blank rows in a spreadsheet.

You can see a blank spreadsheet row loaded via ODBC will be assigned a RecNo() and Null() values across all fields.

A blank spreadsheet row via Table Files is ignored and there does not seem to be a way to reference its position in the spreadsheet by RecNo().

So if you're doing a data grid and you want to ensure a lookup in cell A100 matches across QlikView and Excel, then ODBC will preserve whitespace (for instance when multiple sections of a table repeating on a single spreadsheet are broken up by cosmetic spacer rows, or the data or header contain blanks for some reason)