Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a number of Excel docs in a folder with naming convention of FileName_YYMMDD.
The problem is that the docs may contain a different number of sheets i.e. Doc1 has Sheet1/Sheet2
while Doc2 has Sheet1/Sheet2/Sheet3 etc.
I need to create a table of Doc name and Sheets belonging to that Doc.
Then I want to load the sheets from their associated docs in a loop statement.
Anyone any ideas?
Thanks
then u have to change a=1 to 4
thanks
rohit
i want automatically,without changing in script. How do i do ???
Hi All,
If you want to load all the sheets from all excel files in a folder, the below code should do the work for you.
Let us assume that the folder is "C:\temp" and you need to load all the sheets from each of these files. The script is below.
for each File in filelist ('C:\Temp\*.xls')
ODBC CONNECT TO [Excel Files;DBQ=$(File)];
tables:
SQLTables;
DISCONNECT;
FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39));
Data:
LOAD * FROM $(File) (biff, embedded labels, table is $(sheetName)) where right('$(sheetName)',1) = '$';
NEXT
DROP Table tables;
next File
Let me know whether this works for you.
Best Regards,
Sajeevan
Hi ,
im using 'xlsx' format then how do i that ???
Hi Sajeevan,
I did, its working perfectly.
Thanks,
Antony.
Hi Antony,
Did it work on "xlsx" or "xls" format?
If it was on "xlsx" what changes did you make to the script? Please share this.
Thanks,
Sajeevan
Hi Sajeevan,
I have one query, if i want to load latest excel file in my application then how do i do ?
Example :
Data_Sep.xlsx;
Data_Oct.xlsx;
Data_Nov.xlsx;
I need to load Data_Nov.xlsx, if it is Dec month then it should load Data_Dec.xlsx.
Thanks,
Antony.
hi saleevan
your code working fine but i am not able to understand
sheetName = purgeChar(peek('TABLE_NAME', i, 'tables')
why you use this
can you please explain this in detail..
or is there any other solution for that line so i can use in place of that
thanks ®ards
rohit
Hi Rohit,
PurgeChar will delete the unwanted characters.
Peek will return the fieldvalue from TABLE_NAME field in table call 'tables'.
You can always refer to the help file to understand these better.
Why are you insisting that you want to use another solution - is there a reason?
Regards,
S A J E E V A N
hi sjeevan
thak you for explainig me all those thing
regards
rohit