4 Replies Latest reply: Jul 27, 2012 4:02 PM by diego GOmez RSS

    Excel Import CrossTable with Date column headers issue

    Adam Weisser

      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:

       

       

      AccountID1/11/20111/12/20111/01/20121/02/2012
      50000592.01403.21341.71383.54
      5702012091.5428311.87208022464.86
      570301585.65-826.7284.11742.61
      57310658.14853.252265.675696.31
      57190226.82189.0993.18289.41
      57010544936.79286851.96270210.77234297.24
      5700057562.58196048.0159365.7162376.22
      5704056896.3257235.561815.5562512.36
      5705016393.0565603.8952262.0923759.06
      570606636.50967.38-45.23
      570702864.1421768.755600.1118810.41
      570901425.972364.821374.281613.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