Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi.
I am running into problems when I try to import an Excel 2010 file as a CrossTable that has dates along the column headers.
Excel looks like this:
AccountID | 1/11/2011 | 1/12/2011 | 1/01/2012 | 1/02/2012 |
50000 | 592.01 | 403.21 | 341.71 | 383.54 |
57020 | 12091.54 | 28311.87 | 20802 | 2464.86 |
57030 | 1585.65 | -826.72 | 84.11 | 742.61 |
57310 | 658.14 | 853.25 | 2265.67 | 5696.31 |
57190 | 226.82 | 189.09 | 93.18 | 289.41 |
57010 | 544936.79 | 286851.96 | 270210.77 | 234297.24 |
57000 | 57562.58 | 196048.01 | 59365.7 | 162376.22 |
57040 | 56896.32 | 57235.5 | 61815.55 | 62512.36 |
57050 | 16393.05 | 65603.89 | 52262.09 | 23759.06 |
57060 | 6636.5 | 0 | 967.38 | -45.23 |
57070 | 2864.14 | 21768.75 | 5600.11 | 18810.41 |
57090 | 1425.97 | 2364.82 | 1374.28 | 1613.88 |
Script looks like this:
CrossTable(BalMonth, Balance)
LOAD AccountID,
40848,
40878,
40909,
40940
FROM
Book2.xlsx
(ooxml, embedded labels, table is Sheet3);
And when the script runs the resultant data looks like this:
AccountID | BalMonth | Balance |
---|---|---|
50000 | 40848 | 40848 |
57000 | 40848 | 40848 |
57010 | 40848 | 40848 |
57020 | 40848 | 40848 |
57030 | 40848 | 40848 |
57040 | 40848 | 40848 |
57050 | 40848 | 40848 |
57060 | 40848 | 40848 |
57070 | 40848 | 40848 |
57090 | 40848 | 40848 |
57190 | 40848 | 40848 |
57310 | 40848 | 40848 |
50000 | 40878 | 40878 |
57000 | 40878 | 40878 |
57010 | 40878 | 40878 |
57020 | 40878 | 40878 |
57030 | 40878 | 40878 |
57040 | 40878 | 40878 |
57050 | 40878 | 40878 |
57060 | 40878 | 40878 |
57070 | 40878 | 40878 |
57090 | 40878 | 40878 |
57190 | 40878 | 40878 |
57310 | 40878 | 40878 |
50000 | 40909 | 40909 |
57000 | 40909 | 40909 |
57010 | 40909 | 40909 |
57020 | 40909 | 40909 |
57030 | 40909 | 40909 |
57040 | 40909 | 40909 |
57050 | 40909 | 40909 |
57060 | 40909 | 40909 |
57070 | 40909 | 40909 |
57090 | 40909 | 40909 |
57190 | 40909 | 40909 |
57310 | 40909 | 40909 |
50000 | 40940 | 40940 |
57000 | 40940 | 40940 |
57010 | 40940 | 40940 |
57020 | 40940 | 40940 |
57030 | 40940 | 40940 |
57040 | 40940 | 40940 |
57050 | 40940 | 40940 |
57060 | 40940 | 40940 |
57070 | 40940 | 40940 |
57090 | 40940 | 40940 |
57190 | 40940 | 40940 |
57310 | 40940 | 40940 |
The column header data is replacing the data fields.
I can get around this by having text in the column headers.
Anybody seen this?
Cheers
Adam
No, haven't seen this so far.
But I was able to reproduce your issue in QV11 IR, even with a simple LOAD ( No Crosstable LOAD). Strange.
Hi Adam, try with this:
tmp:
CrossTable(AccountID, Balance)
LOAD AccountID as BalMonth,
[57020.000000],
[57030.000000],
[57190.000000],
[50000.000000],
[57060.000000],
[57090.000000],
[57310.000000],
[57070.000000],
[57050.000000],
[57000.000000],
[57040.000000],
[57010.000000]
FROM
[Book2.xlsx]
(ooxml, embedded labels, table is Sheet3, filters(
Transpose()
));
data:
noconcatenate load subfield(AccountID,'.',1) as AccountID,
BalMonth,
Balance
resident tmp;
drop table tmp;
Regards
Adam,
It would really help if you can upload the excel file. That way it doesn't have to be recreated by anyone that might want to help you.
Bill
CrossTable(BalMonth, Balance)
LOAD *
FROM
Book2.xlsx
(ooxml, embedded labels, table is Sheet3);
have a problem with the column names that are numbers, the solution is to replace the fields with *