topic Re: Read row as header in SQL select (pwd protected spreadsheet) in QlikView

Read row as header in SQL select (pwd protected spreadsheet)

Fri, 11 Jul 2014 19:15:32 GMT

Hi,

I have a password protected EXCEL spreadsheet. I supplied the pwd, had the file open, built ODBC connection and successfully connected to the file with SQL SELECT. However the header I wanted was not the 1st row so the Header became Row 1 (Report 1, F2, F3, ....) and Row 2 and below became data.

How do I get the value of the Row 4 (EIN, WORKER NAME, LEGAL ENTITY) and make it as header? In non-encrypted EXCEL we can use Embedded Label but what do I do here?

Re: Read row as header in SQL select (pwd protected spreadsheet)

its_anandrjs — Fri, 11 Jul 2014 19:52:25 GMT

If as you describe you want to load data from the Fourth row then check the load script and in load script add line header is 3 lines

LOAD

EIN,

[WORKER NAME],

[LEGAL ENTITY]

FROM

Data.xlsx

(ooxml, embedded labels, header is 3 lines, table is Sheet1);

Then you get fields EIN, [WORKER NAME], [LEGAL ENTITY] with data

Employee Id	Employee Name	Oracle Entity
123	ABC	535
456	DEF	535

Let me know if it works for you.

Re: Read row as header in SQL select (pwd protected spreadsheet)

Fri, 11 Jul 2014 19:59:08 GMT

Hi Anand,

Thanks but with password protected spreadsheet, I must use ODBC and read file with SQL SELECT. I couldn't specify header line.

LOAD
Report Title 1,

F2,
F3
;
SQL SELECT *
FROM `$(vPath)\File.xlsx`.`'sheet1$'`;

Re: Read row as header in SQL select (pwd protected spreadsheet)

its_anandrjs — Fri, 11 Jul 2014 20:23:51 GMT

Ok then you can try another way i paste the sample load script try to load data this ways.

ODBC CONNECT TO [Excel Files;DBQ=D:\Data.xlsx];

T1:

LOAD `Report Title 1`,

F2,

F3,

RowNo() as Rid;

SQL SELECT `Report Title 1`,

F2,

FROM `D:\Data.xlsx`.`Sheet1$`;

NoConcatenate

LOAD *

Resident T1

Where Rid > 2 and Len(`Report Title 1`) > 0;

DROP Table T1;

Re: Read row as header in SQL select (pwd protected spreadsheet)

its_anandrjs — Fri, 11 Jul 2014 20:36:09 GMT

Another way is

ODBC CONNECT TO [Excel Files;DBQ=D:\Data.xlsx];

T1:

LOAD `Report Title 1`,

F2,

F3,

RowNo() as Rid;

SQL SELECT `Report Title 1`,

F2,

FROM `D:\Data.xlsx`.`Sheet1$`;

NoConcatenate

LOAD

`Report Title 1` as EIN,

F2 AS [WORKER NAME] ,

F3 AS [LEGAL ENTITY],

Rid

Resident T1

Where Rid > 2 and Len(`Report Title 1`) > 0;

DROP Table T1;

Re: Read row as header in SQL select (pwd protected spreadsheet)

rwunderlich — Fri, 11 Jul 2014 22:41:24 GMT

Do you have to use the names on line 4 dynamically? Or are they fixed. If fixed, you can do it like Anand suggested but I think a bit simpler like:

LOAD

`Report Title 1` as EIN,

F2 as [WORKER NAME],

F3 as {LEGAL ENTITY],

WHERE Recno() > 6

SQL SELECT

`Report Title 1`,

F2,

FROM `D:\Data.xlsx`.`Sheet1$`;

If you need to deal with dynamic/changing names, let me know and I'll dig up the code for that.

-Rob

Re: Read row as header in SQL select (pwd protected spreadsheet)

Tue, 15 Jul 2014 14:44:47 GMT

Thanks Anand and Rob. The field names will be dynamic so hardcoding is impossible. The row of the header could change too. The header could be row 4, row 5, or row 6. This is messy and thank you in advance!

- Jess