Crosstable load with variable number of columns

[marcus_malinow]

Hi,

I am loading a crosstable from excel with a pretty typical format. Across the top I have cities, and along the side I have measures. I'd like to be able to write my script in such a way that as new cities are added to the spreadsheet, I don't need to rewrite the load script. Is this possible?

Excel file looks something like this:

	London	Dublin	Toronto
Cash	10	5	5
VAT	.2	.23

Script looks something like:

CrossTable(London, Data)
LOAD F1,
     London,
     Dublin,
     Toronto
FROM .\data.xlsx (ooxml, embedded labels, table is Sheet1);

Thanks,

Marcus

[Gysbert_Wassenaar · Accepted solution]

If you use * instead of listing the field names it won't matter how many columns there are.

CrossTable(City, Data)
LOAD *
FROM .\data.xlsx (ooxml, embedded labels, table is Sheet1);

[marcus_malinow]

Haha that's great, feel foolish for not just giving that a go earlier.

Thanks!