Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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?

header.PNG.png

6 Replies
its_anandrjs

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 IdEmployee NameOracle Entity
123ABC535
456DEF535

Let me know if it works for you.

Not applicable
Author

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$'`;

its_anandrjs

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,

    F3

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

NoConcatenate

LOAD *

Resident T1

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

DROP Table T1;

its_anandrjs

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,

    F3

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;

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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,

    F3

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

Not applicable
Author

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