Multiple Excel files for input
Posted: Mon Mar 21, 2005 7:38 am
I haven't tried it yet but I thought I'd post here for someone to give me a sanity check and tell me I'm not over-complicating things :D
What we're trying to achieve is loading multiple excel spreadsheets into a SQL server database. The formats are the same but the names are different.
I'm thinking of creating an Excel ODBC pointing to a file, something like Source.xls
Somehow counting how many files there are, then looping through each one of them. After the job runs, I rename the Source.xls file and move it to an archive folder, take the next file, rename it to Source.xls and then the loading-job executes. The loop continues until the data folder is empty.
Is this the best way of doing it and is everything I'm thinking of doing here actually possible ? Especially counting how many files there are in the folder... don't have any clue how I'm going to achieve this yet.
The reasoning behind the loop is that I won't know how many source files there'll be, and I really don't want to create hundreds of ODBC connections, one for every excel file.
Any help/comments will be appreciated.
Thanks
Morne
What we're trying to achieve is loading multiple excel spreadsheets into a SQL server database. The formats are the same but the names are different.
I'm thinking of creating an Excel ODBC pointing to a file, something like Source.xls
Somehow counting how many files there are, then looping through each one of them. After the job runs, I rename the Source.xls file and move it to an archive folder, take the next file, rename it to Source.xls and then the loading-job executes. The loop continues until the data folder is empty.
Is this the best way of doing it and is everything I'm thinking of doing here actually possible ? Especially counting how many files there are in the folder... don't have any clue how I'm going to achieve this yet.
The reasoning behind the loop is that I won't know how many source files there'll be, and I really don't want to create hundreds of ODBC connections, one for every excel file.
Any help/comments will be appreciated.
Thanks
Morne