Crosstable - multiple excel sheets issue

[#1]

Hi all,

I have an excel file with several sheets (one per month) updated monthly with the last six months of sales values and one column with units for the last month.

EXAMPLE:

Description	Code	Aug	Sep	Oct	Nov	Dic	Jan	Total	Units
product a	39152	4		1	5	4	4	18	4
product B	39153	13	3	11	25	16	1	69	22
product C	39164	6	5	3	2	2	4	22	4
product d	39165	7	2	7	3	5	5	29	23
prouct e	39166						47	47	16
product f	39167	144	97	113	112	122	97	685	178

From each sheet I have to upload the value of the last month (sales) and the value in the UNITS column.

How can I upload the sales values in all sheets as a crosstable? How can I identify the Units column as part of the current month? in this example, the units are from Jan.

Next month I'll have a similar file with columns from Sept to Feb. and Units values for Feb

Thanks.

[adamdavi3s · Accepted solution]

This worked for me with your data, with the exception that you need to make sure the column headings are consisten e.g. units and Units

ODBC CONNECT32 TO [Excel Files;DBQ=E:\data_YTD.xlsx];

tables:
SQLtables;

months:
load *, month(makedate(Year,Month,'01')) as loadmonth,makedate(Year,Month,'01') as loaddate;
load TABLE_NAME, mid(TABLE_NAME,2,4) as Year, num(mid(TABLE_NAME,7,2)) as Month
resident tables;


drop table tables;

FOR i = 0 to NoOfRows('months')-1
  LET sheetName = purgechar(purgeChar(peek('TABLE_NAME', i, 'months'), chr(39)),chr(36));
  LET columnName = purgeChar(peek('loadmonth', i, 'months'), chr(39));
  LET loadDate = purgeChar(peek('loaddate', i, 'months'), chr(39));


  Sales:
  LOAD Desc,Code,Units, $(columnName) as data, '$(loadDate)' as date
  FROM  (ooxml, embedded labels, table is [$(sheetName)])
  WHERE len(Desc)>0;
NEXT;


drop table months;

[#3]

Hi Adam, 

Thank you very much for your reply.

Let me see if I understand correctly.  This is creating the months and dates from the names of the excel sheets in the file, extracting them them from the TABLE_NAME table?

One question, I am getting an error:

Field not found - <jul>

Sales: 

  LOAD Desc,Code,Units, jul as data, '01/07/2016' as date 

  FROM (ooxml, embedded labels, table is [2016-07]) 

  WHERE len(Desc)>0

This is for every month.

Do the Months in the file have to match the MonthNames variable?

SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic'?

Thanks

[#4]

Hi Adam,

The error was due to difference in the file  vs. the MonthNames

I changed them and it worked perfectly.

Thank you very much for your assistance,

Regards.

Ramon

[adamdavi3s]

Hi Ramon,

Excellent news, I am glad it works for you, it was a fun puzzle.

Kind regards

Adam