Load tables with headers and No headers challenge

[marcel_olmo]

Hi guys,

I have multiple excel sources, ones with headers, and other ones with no headers, but the same information.

I'd like to know if there's a simple way to deduce the headers by some "mapping headers" stuff in order to avoid a rename of multiple headers of multiple sources.

Here's a simple example of what I'm talking about.

Imagine I have 1 table with headers like this :

LOAD

     Date,

     [Customer ID],

     [Product ID],

     Amount

FROM

[TemplateExample.xlsx]

(ooxml, embedded labels, table is Hoja1);

And another table with no headers but the same structure :

LOAD A			as Date,
B			as [Customer ID],
C			as [Product ID],
D			as Amount

FROM

[TemplateExample.xlsx]

(ooxml, explicit labels, table is Hoja2);

How could I do it avoiding the renaming stuff? The point is that I have multiple sources and 30 fields per source.

I've attached the example of what I'm doing.

Regards, Marcel.

[Oleg_Troyansky]

This might be an over-kills, but I'd probably do something like the following:

1. Load the first line (labels) from one of the tabs that contain labels.

2. Process all the labels in a loop and generate the LOAD statement automatically, using the Labels that you just loaded

3. Process all the tabs using the Generated LOAD statement, with no labels and stripping off the accidental row of Labels using the "Conditional Delete" transformation.

This way, with a little bit of scripting, you could process any number of tabs and fields, with or without labels.

If you also have a huge number of tabs, you could get the list of all Tables by connecting to you spreadsheet using ODBC and then using the SQLTABLES command.

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Munich, Germany!

[Nicole-Smith]

Code like the following should work:

FileList:
LOAD * INLINE [
  FileName, Header, Table
  TemplateExample.xlsx, 1, Hoja1
  TemplateExample.xlsx, 0, Hoja2
];

FOR i=0 TO NoOfRows('FileList')-1
  LET vFile = PEEK('FileName', $(i), 'FileList');
  LET vHeader = PEEK('Header', $(i), 'FileList');
  LET vTable = PEEK('Table', $(i), 'FileList');

  Data:
  LOAD A AS Date,
  B AS [Customer ID],
  C AS [Product ID],
  D AS [Amount]
  FROM $(vFile) (ooxml, no labels, header is $(vHeader) lines, table is $(vTable));
NEXT

In FileList, if you have a list of all your files, a 1 if it has a header or a 0 if it doesn't, and the table/sheet to load from, you can write a loop to go through each file and load everything into your table with the same column names.

I've attached your file with the working code.

[marcel_olmo]

Thanks Nicole for your quick answer!

The point here is that I don't know if the first field is date or not, it's just an example. The only thing here is that I can get the headers of some of the tables.

Regards, Marcel.