topic Re: Can I load only first row in an Excel sheet? in Connectivity & Data Prep

Can I load only first row in an Excel sheet?

Tue, 22 Mar 2016 11:33:46 GMT

I would like to load the headers only into a temp table - where I can look for certain headers and then

decide what to load into where at a second stage of the import.

Re: Can I load only first row in an Excel sheet?

sunny_talwar — Tue, 22 Mar 2016 11:35:58 GMT

You can use

First 1

LOAD ....

Re: Can I load only first row in an Excel sheet?

ali_hijazi — Tue, 22 Mar 2016 11:36:10 GMT

LOAD *

FROM

[path_to_excel]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

where 1=0;

Re: Can I load only first row in an Excel sheet?

Tue, 22 Mar 2016 11:46:49 GMT

First 1

Worked just fine - ok second step would be to access that line of data and the respective columns.:-)

Re: Can I load only first row in an Excel sheet?

sunny_talwar — Tue, 22 Mar 2016 11:48:41 GMT

Is that a question or is that something you plan to do

Re: Can I load only first row in an Excel sheet?

Tue, 22 Mar 2016 12:16:00 GMT

Sorry it was a question 🙂 and I plan to do it when I found out how 🙂

Re: Can I load only first row in an Excel sheet?

sunny_talwar — Tue, 22 Mar 2016 12:19:01 GMT

You can do a CrossTable Load to convert your list of columns into a single column with multiple rows:

Table1:

First 1

LOAD *

FROM Source;

Table2:

CrossTable(Columns, Data)

LOAD 1 as Flag,

Resident Table1;

DROP Table Table1;

Now Columns field will include all the column names that are available in your Excel file.

Re: Can I load only first row in an Excel sheet?

Tue, 22 Mar 2016 12:38:13 GMT

Ending up with Columns null again - as I did with FileName... lets try the question this way instead.

What I want to achieve is some way of dynamically pick column names based on what the excel file contains and save those into a field.

(in a batch of many excel-files that are similar but not exact)

example,

file 1 contains

State

file 2 contains

Stat

file 3 does not contain any of those

in my imported data i would like to have the result

State as State (from file 1)

Stat as State (from file 2)

"N/A" or just blank as State (from file 3)

Re: Can I load only first row in an Excel sheet?

sunny_talwar — Tue, 22 Mar 2016 12:40:03 GMT

Like I mentioned on your previous post, I am working on getting the previous method implemented in Qlik Sense. I will get back to you when I have something

Re: Can I load only first row in an Excel sheet?

Tue, 22 Mar 2016 12:42:18 GMT

Yep - that could also work (using the filename as variable for what is inside the file) - but its kind of a workaround for the scenario above 🙂 - anyway iam happy with anything that solves the enigma 🙂