Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI All,
I am having months data in the Excel Sheet.
Every month it will added one more Sheet With new month, need to be loaded in a Single load dynamically.
Please find the attached excel, Can any help me on this..
Regards,
Helen
like this? :
Hi Helen,
When you say that there will be a new sheet every month do mean a new sheet (tab) will be added to this Excel file or will there be a new file for the month?
If there is going to be a new file every month then it's very easy. If all the columns are identical for each month then put all the files into one folder. It's good practice to add the path to this folder to the script as a variable:
LET vxlsFolder = 'insert path here ';
Orders:
LOAD OrderID,
ProductID,
Product,
CustomerID,
EmployeeID,
OrderDate,
Margin,
Sales,
Cost,
Quantity
FROM
$(vxlsFolder)\*.xls
(biff, embedded labels, table is Orders$);
table is Orders$ here means that the script will extract from a sheet called Orders like you have in the file you uploaded.
Save your files to your folder and create your script to read one of them. When it's working then edit the line
$(vxlsFolder)\FilenameYouUseToTest.xls;
to
$(vxlsFolder)\*.xls
replacing the filename with '*'.
The script will then concatenate all the lines from the files into a single table Order.
Good luck
Andrew
every month one extra sheet in the same excel
Regards,
Helen
Helen,
In that case I'll refer you to this: Load Multiple excel sheets using For loop
S there is an update only once a month the simpler thing may be to make separate files from each sheet.
Kind regards
Andrew
Tried , Iam getting error, Kindly check once
LET vStartSheetNumber = 1;
LET vEndSheetNumber = 50;
LET vExcelFileName = 'Data';
// Generate Empty table
Data:
LOAD
'' AS Sno,
'' AS Name,
'' as [Sales Value]
AutoGenerate(0);
FOR index = vStartSheetNumber TO vEndSheetNumber
Concatenate(Data)
LOAD
*
FROM [$(vExcelFileName).xlsx]
(ooxml, embedded labels, table is [Page $(index)]);
NEXT
Helen,
What are the names of your sheets? This script will only work if your sheets are 'Page 1', 'Page 2', ...etc.
Regards
Andrew
my sheet names will be of months, plz check in the excel
Your excel sheet doesn't tell me the names of the sheets you want to load data from. You say sheets will be of months.
'Jan17' or 'Jan 17' or ' January 2017' or '01-17' or ...
attached the Data excel above , plz check
like this? :