Easiest way to Import Multiple Spreadsheets into the same table
Hi, I am trying to found out the most simple way to import multiple spreadsheets (myspreadsheet*.xls) into the same table. I found this process relatively straight forward with importing multiple text files (myTextFile*.txt) into the same table. Any help would be greatly appreciated. Thanks in advance, Kieran. If you have found any of my posts helpful then please vote them as helpful. Kieran Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds
October 21st, 2010 6:38pm

Use Foreachloop to iterate through all the files (in a folder) and save the fully qualified name in a string variable. Then have a data flow task inside foreach loop and configure it as per your requirement. Don't forget to give a correct default value to the variable which is used to save the fully qualified name. After that select the excel connection manager and set an expression for its connection string property as "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::fullyqualifiedName] + ";Extended Properties=\"EXCEL 8.0;HDR=YES\";"Nitesh Rai- Please mark the post as answered if it answers your question
Free Windows Admin Tool Kit Click here and download it now
October 21st, 2010 6:48pm

But that doesn't cover the fact that each tab (read: "Table") in the spreadsheet may have a different name. If this is the case, you may need TWO For/Each loops. The 'outer' loop iterates over the files in the folder, and the 'inner' loop iterates of the tabs in the file. I accomplished this with a Script Component on the Data FLow that acts as a Source. Script is as follows: Dim excelFile As String = "Data Source=" + Me.Variables.FullFileName.ToString + ";Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 8.0;" 'Dim excelFile As String = Me.Connections.ExcelConnection.ConnectionString.ToString Dim cn As OleDbConnection = New OleDbConnection(excelFile) cn.Open() Dim tablesInFile As DataTable = cn.GetSchema("TABLES") Dim tableCount As Integer = tablesInFile.Rows.Count Dim currentTable As String Dim columnRestrictions(3) As String For Each tableInFile As DataRow In tablesInFile.Rows currentTable = tableInFile( "TABLE_NAME") If Len(currentTable) = InStr(currentTable, "$") Or _ Len(currentTable) = InStr(currentTable, "$") + 1 Then Output0Buffer.AddRow() Output0Buffer.TabName = currentTable End If Next cn.Close() Output0Buffer.SetEndOfRowset() Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
October 21st, 2010 7:29pm

My bad!! Yes, for each loop can be used twice with inner loop iterating through all the worksheets name. It can be used without using script task as shown here: http://cr9itesh.blogspot.com/2009/11/loop-through-excel-sheets-in-workbook.htmlNitesh Rai- Please mark the post as answered if it answers your question
Free Windows Admin Tool Kit Click here and download it now
October 21st, 2010 7:36pm

Very helpful thank you to you both. I will look more closely at your suggestions first thing in the morning GMT.If you have found any of my posts helpful then please vote them as helpful. Kieran Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds
October 21st, 2010 9:35pm

I would suggest you to use a ForeachLoop container which loops through all Excel files in a folder and use Script Component inside foreachloop container to loop through all excel tabs, this way you will have additional flexbility of including any code in script component to process both Excel 2003 and Excel 2007 files ,Update excel sheet and so on Thanks BB
Free Windows Admin Tool Kit Click here and download it now
October 21st, 2010 9:38pm

Many thanks Nitesh, http://cr9itesh.blogspot.com/2009/11/loop-through-excel-sheets-in-workbook.html tells me exactly what I need to know. It took me a while to follow these instructions with the screen shots not being aligned to the instructions. But now I have followed your instructions I have no doubt that you have enabled me to complete my task with minimum complexity. Thanks again, Kieran. If you have found any of my posts helpful then please vote them as helpful. Kieran Wood MCTS BI, PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds
October 23rd, 2010 3:58pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics