Is it possible to find out the name of an excel worksheet
I need to import details from Excel but the worksheet names are not always what is expected. Is there a way to determine the worksheet name using sql?
Query used to import where sheet name = data
SELECT * INTO tmp_test2
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:datatest2.xls', 'SELECT * FROM [data$]')
Thanks
Software/Hardware used:
Software/Hardware used:
ASKED:
April 17, 2008 3:52 PM
UPDATED:
April 19, 2008 7:19 AM
Answer Wiki:
If you use SSIS for the import, you can get the name of the excel spreadsheet in question by using the following code
You can get the worksheet name using the code
<pre>Dim excelFile As String
Dim connectionString As String
Dim excelConnection As OleDbConnection
Dim tablesInFile As DataTable
Dim tableCount As Integer = 0
Dim tableInFile As DataRow
Dim currentTable As String
Dim tableIndex As Integer = 0
Dim excelTables As String()
excelFile = Dts.Variables("ExcelFile").Value.ToString
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & excelFile & _
";Extended Properties=Excel 8.0"
excelConnection = New OleDbConnection(connectionString)
excelConnection.Open()
tablesInFile = excelConnection.GetSchema("Tables")
tableCount = tablesInFile.Rows.Count
ReDim excelTables(tableCount - 1)
For Each tableInFile In tablesInFile.Rows
currentTable = tableInFile.Item("TABLE_NAME").ToString
excelTables(tableIndex) = currentTable
tableIndex += 1
Next
Dts.Variables("ExcelTables").Value = excelTables
Dts.TaskResult = Dts.Results.Success</pre>
where excelFile is the variable. You can use a for each loop container to get all the excel files in a folder.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
