January 6, 2007 at 12:19 am
Hi,
New to DTS and dont know how to solve this ... Looking for approach how to approach this.
I have Excel file having 3 dimensional data with multible worksheets.
Eg
1. Worksheet1
CustID--------TV--------Mob--------WalkMen
0012A---------6------------------------1
0013B-------------------9---------------
2. Worksheet2
CustID--------Fridge--------VCR--------TP
0018A----------9------------------------1
0026B----------1-------------9----------3
After reading EXCEL it should store in SQL Server in a table in this format
CREATE TABLE ExcelData (
CustID VARCHAR(12),
SetsDesc VARACHAR(25)
SetsValue INT )
CustID--------SetsDesc--------SetsValue
0012A-----------TV---------------6
0012A-----------Walkmen----------1
0013B-----------Mob--------------9
0018A-----------VCR--------------9
0018A-----------TP---------------1
0026B-----------Fridge-----------1
0026B-----------VCR--------------9
0026B-----------TP---------------3
Thanking in advance for the solution
January 6, 2007 at 4:23 pm
Right click dts packages/new packages...
create a connection to excel file...and SQL server where you want to dump the data from excel...
Make three transformations
excel-sheet1------SQL.TABLE1
excel-sheet2------SQL.TABLE1
excel-sheet3------SQL.TABLE1
MohammedU
Microsoft SQL Server MVP
January 7, 2007 at 5:49 am
I have just played around a bit with importing Excel data.
The essential bit on the SQL Server side of things is this piece of code:
select * from openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=c:\tmp\Book1.xls',test)
But be ready for some surpises.
1. Thus 'test' refers to a named range (in Excel Insert/Name/Define). Can't seem to reference sheets.
2. The first row is always used to get the column names. You can't seem get around that. In your problem, the first row is essential and I don't know of a way to get at the column names returned by openrowset. Thus you'll have to insert a blank row.
3. There seems to be no way to find out how many sheets you have. Thus you would need to create another sheet pointed to by a range which contains the named ranges for all the other sheets.
4. Although not a problem in your case, I have found some strange NULLs appear when a column contains numeric data mixed with non-numeric data.
5. It is hard to render this dynamic based on the number of columns.
All this could be handled by VBA code run on the Excel workbook before importing. But this needs to be automated.
Thus I'm very disappointed by this openrowset feature specific to Excel.
DTS seems a little better because it can handle worksheets in addition to named ranges. But it too suffers from using the first row as column names.
My suggestions is that you script this which would require the presence of Excel. This would give you the flexibility of an arbitrary number of worksheets and an arbitrary number of columns.
Below you'll need to modify the 3 string constants. Then you can run it via: cscript myscript.vbs
You may:
1. want to add some additional error checking
2. delete the contents of the destination table: objConn.Execute "delete from " & strTable
The rest is up to you.
-------------------------------------------------------------------------------------------
Option Explicit
On Error Resume Next
Const strExcelFile = "c:\tmp\book1.xls"
Const strConn = "PROVIDER=SQLOLEDB;DSN=127.0.0.1;DATABASE=mydatabase;UID=myuid;PWD=mypwd"
Const strTable = "t"
Dim objExcelApp
Dim objExcelBook
Dim objExcelSheet
Dim intSheet
Dim intSheets
Dim objConn
Dim strCols(256)
Dim intRow
Dim intCol
' start Excel
Set objExcelApp = CreateObject("Excel.Application")
' open workbook file
Set objExcelBook = objExcelApp.Workbooks.Open(strExcelFile, , False)
If Err.Number <> 0 Then Abort
' connect to database
Set objConn = CreateObject("ADODB.Connection")
objConn.Open strConn
If Err.Number <> 0 Then Abort
' loop through worksheets
intSheets = objExcelBook.Sheets.Count
For intSheet = 1 to intSheets
WScript.Echo "Processing sheet " & CStr(intSheet) & " of " & CStr(intSheets)
Set objExcelSheet = objExcelBook.Sheets(intSheet)
' get identifiers from first row
For intCol = 2 to objExcelSheet.UsedRange.Columns.Count
strCols(intCol) = objExcelSheet.Cells(1,intCol).Value
Next
' loop through remaining rows
For intRow = 2 To objExcelSheet.UsedRange.Rows.Count
For intCol = 2 to objExcelSheet.UsedRange.Columns.Count
If Len(objExcelSheet.Cells(intRow,intCol).Value) > 0 Then
objConn.Execute "insert into " & strTable & " values('" & objExcelSheet.Cells(intRow,1).Value & "','" & strCols(intCol) & "'," & objExcelSheet.Cells(intRow,intCol).Value & ")"
End If
Next
Next
If Err.Number <> 0 Then Abort
Next
CloseDown
WScript.Echo "Done"
Sub CloseDown()
objExcelBook.Close
Set objExcelBook = Nothing
objExcelApp.Quit
Set objExcelApp = Nothing
objConn.Close
Set objConn = Nothing
End Sub
Sub Abort()
WScript.Echo CStr(Err.Number) & " " & Err.Description
CloseDown
WScript.Quit 1
End Sub
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply