Solved

DataType when Impoting

Posted on 2004-08-03
86
1,328 Views
Last Modified: 2012-06-27
Hi,

I have a DTS package that drop/create/import an excel file into SQL.

The data in Excel looks like this: (They are in the same column)

9999999xxxxx
                            1.01E+13
                            2.01E+14
8x8x8888xxxx

Some of the data are string, some are numeric, but I want to treat them as string, so in creating table step in DTS,
I create the field using nvarchar(255), but for some reason, only those numerix values got inserted, not the string
type. I changed the type in excel itself to text (so the data align to the left), but the string data is still missing.

Any thoughts?

Thanks


0
Comment
Question by:mcrmg
  • 38
  • 30
  • 6
  • +4
86 Comments
 
LVL 7

Expert Comment

by:natloz
ID: 11706773
In VB.Net the same thing happens...it has to do with OLEDB drivers reading the first 20 or so rows of an Excel file and assuming the whole document this way...one person suggested changing the Excel file to a workbook. Maybe try this out.
Otherwise I am not sure what you can do.
0
 

Author Comment

by:mcrmg
ID: 11706815
I changed it to workbook 4.0, what would be the selection for Data Source in DTS?  Thanks
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 11706817
The ODBC drivers use the first 8 rows to determine a column datatype in an Excel data source
So if the first 8 rows in a column look like numbers, the driver will expect the following records to be numbers too.
This will even override Excel's column datatypes

I think you can use the Excel T() function to turn the columns into text.
0
 

Author Comment

by:mcrmg
ID: 11706844
Can you give me a simple example?  Thanks
0
 
LVL 7

Expert Comment

by:natloz
ID: 11706926
I am not entirely sure which Datasource you would try...probably the Excel one still.
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 11707059
Here's a link to a related question with a possible solution
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20089000.html

Experimenting the T() function, will come back asap
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11707369
why not just CAST or CONVERT the colums to varchar within the DTS transformation?
0
 
LVL 9

Expert Comment

by:apirnia
ID: 11707836
You can use CAST and CONVERT on that coloumn. Open the dts, right click on the gray line beteween the Db and the Excel >> Options. On the first tab you can do a CAST or CONVERT.
0
 

Author Comment

by:mcrmg
ID: 11707846
I use this:

      DTSDestination("DestField") = CONVERT(char(100),DTSSource("SourceField"))

It gives me Type mismatch: 'CONVERT'
0
 

Author Comment

by:mcrmg
ID: 11707888
>>You can use CAST and CONVERT on that coloumn. Open the dts, right click on the gray line beteween the Db and the Excel >> Options. On the first tab you can do a CAST or CONVERT.

I see "Exception file.............", what is the next step?  Thx
0
 
LVL 9

Expert Comment

by:apirnia
ID: 11707961
Try something like this:

select CAST(DateField1 as Numeric), DateField2, DateField3 from ...
0
 
LVL 9

Expert Comment

by:apirnia
ID: 11707983
0
 

Author Comment

by:mcrmg
ID: 11707998
Should I type it in execption file box???
AND
I have more than 100 fields from that excel file, so, basically, retype them all?  Thax

0
 
LVL 9

Expert Comment

by:apirnia
ID: 11708061
Dont retype anything....
All fields should already show up there. Just apply the convert to that field you want as Numeric.


You might have to do a ActiveX transform on the numeric column and add a numeric convert function to it--something like cint(inputcolumn).
If you double click on the line that connection your excel connection and your sql connection, it will pull up the transformation properties....Click on the transformations tab.  Click on the line that connects the fields that you want to do the convert on--delete this line (you're going to change it from a column copy to an active x script).  After you delete the line, click on the source field and the destination field you want to convert.  Click the "New" button.  Choose ActiveX script....
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11708067
<<DTSDestination("DestField") = CONVERT(char(100),DTSSource("SourceField"))>>

Sorry, just realised what you are doing and where. The transform uses VBScript, so CONVERT is not valid, so use CStr instead, as in:

DTSDestination("DestField") = CStr(DTSSource("SourceField"))
0
 

Author Comment

by:mcrmg
ID: 11708537
It gives me Object expected error........
0
 
LVL 1

Expert Comment

by:bobsterboy
ID: 11709405
Hi,

I've had to deal with this hundreds of times and the MS "help" doesn't work consistently.

The way I get around it is to open an Excel object, and iterate through the columns and rows.  It's the only way I'm aware of to get around the problem of the stupid ODBC "conversion".  Another way I've used is to insert a row into the excel file that contains a string in each field for the first row, not including the headers.  If you want the code, it's kind of convoluted, but it works like a charm.

Option Compare Database
Option Explicit


Public pstrItemHdr()      As String
                                                    'These are all set by the function btcSetAllOffSets
Public Const pintCOLOFFSET      As Integer = 1           'Offset the number of columns to the first valid row
Public Const pintROWOFFSET      As Integer = 1           'Offset the number of rows to the header row
Public Const pintDATAROWOFFSET  As Integer = 2     'Offset the number of rows to the first data row
                                       ' 1 if no headers, 2 if headers
                                                    'Set the data file path constant

Public Const pstrFILEPATH       As String = "\\YourServer\YourShare\YourFolder"
Public Const pstrDESTPATH       As String = "\\YourServer\YourShare\YourFolder\YourProcessedFolder"
Public Const pstrCONNECTIONSTRING As String = ""
Public Const pintNumCols        as Integer = 27  'Set this to the number of known columns

Public Const pstrCompanyID          As String = ""


'*****************************************************************************
'       This procedure opens an excel spreadsheet and stores the values in a SQL table
'       Pre- reqs:
'           Excel must be loaded on this machine
'           Valid SQL Login/Database/Table structure must exist
'           Spreadsheet must be stored in known folder
'           Spreadsheet must be in this format:
'
'
'               1.  Data starts in Row after row that contains the A row value of "Vendor"
'               2.  Item Class Headers start in the row that contains the A row value of "Vendor"
'               3.  No blank rows exist after Data row.
'               4.  No blank columns exist after column .
'               5.  The last column header is labeled "CK DIGIT"
'               6.  No limits on number of rows, but first blank row in A column signifies end.
'               7.  Column headers are limited to "A - Z", no more than 19 Item Classes.

'
Function OpenExcelZola()

Dim objXL           As Excel.Application
Dim objWKBK         As Excel.Workbook
Dim objWorkSheet    As Excel.Worksheet
Dim lngNumRows      As Long
Dim intNumCols      As Integer
Dim objActCell      As Excel.Range
Dim i               As Integer
Dim intRow          As Integer
Dim intCol          As Integer
Dim strValue        As String
Dim strTrxAmt       As String
Dim strVendorID     As String
Dim strVendorName   As String
Dim strTrxDate      As String
Dim strInvNum       As String
Dim strItemCost()   As String
Dim strAcctNum()    As String
Dim objFSO          As FileSystemObject
Dim objADPFolder    As Folder
Dim objADPFile      As File
Dim objADPData      As TextStream
Dim strOldSheetName As String
Dim lngOldNumRows   As Long
Dim strInsertSQL    As String
Dim strCompanyID    As String
Dim strFullPath     As String
Dim strDescription  As String

On Error GoTo ErrHandler

            'This allows multiple files to be imported.  
    Set objFSO = New FileSystemObject
    Set objADPFolder = objFSO.GetFolder(pstrFILEPATH)
    For Each objADPFile In objADPFolder.Files
        If objADPFile.Type = "Microsoft Excel Worksheet" Then
            If TypeName(objXL) = "Nothing" Then
                Set objXL = New Excel.Application
            End If
            If TypeName(objWKBK) <> "Nothing" Then
                objWKBK.Close False
                Set objWKBK = Nothing
            End If
            Set objWKBK = objXL.Workbooks.Open(objADPFile.Path)
            For Each objWorkSheet In objWKBK.Worksheets

                                              'Allows for multiple worksheets to be read
                objWorkSheet.Activate                       'Activate the worksheet so it can be acted upon
                btcSetAllOffSets objWorkSheet
                                                            'Get Number of Rows in this spreadsheet
                lngNumRows = btcGetNumRows(objWorkSheet)
'                If lngNumRows = 0 Then
'                    lngNumRows = lngOldNumRows              'Set variables to be able to Move back to last spreadsheet
'                    Exit For                                'Leave this loop, we'll only be working on 1 worksheet at a time
'                Else
                    lngOldNumRows = lngNumRows
                    strOldSheetName = objWorkSheet.Name
                                                            'Get Number of Columns for this worksheet

                    intNumCols =                       'btcGetNumCols(objWorkSheet)
'                End If
                                                            'Get next Worksheet
                                                            'Start pulling the values from the Spreadsheet
                objWKBK.Worksheets(strOldSheetName).Activate
                Set objWorkSheet = objWKBK.ActiveSheet
                strAcctNum = btcAccounts(objWorkSheet, intNumCols)
                ReDim strItemCost(intNumCols) As String         'Redimension and clear the array
                intNumCols = intNumCols ' - 1                     ' Since I'm using this value more than once, evaluate it once
                lngNumRows = lngNumRows + pintDATAROWOFFSET     ' since I'm using this value more than once, evaluate it once
               
                For intRow = pintDATAROWOFFSET To lngNumRows    ' Clear variables for next record
                    strVendorID = ""
                    strTrxDate = ""
                    strInvNum = ""
                    strDescription = ""
                    strTrxAmt = ""
                    ReDim strItemCost(intNumCols) As String

                                                'This is hard coded for these data values,
                                                'You need to adjust these to your dataset
                    For intCol = 1 To intNumCols                'Set values based on which column is active
                                                                'Replace any apostrophes with double apostrophes
                        strValue = Replace(CStr(objWorkSheet.Range(Chr(64 + intCol) & intRow)), "'", "''")
                        Select Case intCol
                            Case 1                              'Vendor Name Column 1
                                strVendorName = Replace(strValue, "'", "''")
                            Case 2                              ' Vendor ID  Column 2
                                strVendorID = strValue
                                If strVendorID = "" And strVendorName <> "" Then
                                    strVendorID = btcGetVendorIDFromName(strVendorName)
                                End If
                            Case 3                              ' Date Column 3
                                strTrxDate = strValue
                            Case 4                              ' Invoic e Number Column 4
                                strInvNum = strValue
                            Case 5                              ' Invoice Description
                                strDescription = strValue
                            Case 6                              ' Total Column
                                strTrxAmt = strValue
                            Case Else                           ' All inventory items  Columns 6 - 25 (Possible)
                                strItemCost(intCol) = strValue
                        End Select
                    Next intCol                                 'Get next column value
                    i = 0
                    strInsertSQL = ""                           'Reset Insert SQL String
                    While i < UBound(strItemCost)
                        If strItemCost(i) <> "" And strItemCost(i) <> "0" And Left(strAcctNum(i + 1), 2) <> "CK" Then
                            If strInsertSQL <> "" Then
                                strInsertSQL = strInsertSQL & " "
                            End If
                            'If strVendorID = "" Then
                            '    strInsertSQL = strInsertSQL & "INSERT btctblPMImportDEAD (CompanyID, VendorID, InvDate, InvNum, Description, TrxAmt, DebitAmt, CreditAmt, DebitAcct, CreditAcct)"
                            'Else
                                strInsertSQL = strInsertSQL & "INSERT btctblPMImport (CompanyID, VendorID, InvDate, InvNum, Description, TrxAmt, DebitAmt, CreditAmt, DebitAcct, CreditAcct)"
                            'End If
                            strInsertSQL = strInsertSQL & " VALUES ('" & pstrCompanyID & "', '" & strVendorID & "', '"
                            strInsertSQL = strInsertSQL & strTrxDate & "', '" & strInvNum & "', '" & strDescription & "', "
                            strInsertSQL = strInsertSQL & Abs(strTrxAmt) & ", " & Abs(strItemCost(i)) & ", " & Abs(strItemCost(i)) & ", '"
                            If Left(strTrxAmt, 1) <> "-" Then                    'If the amount is negative, reverse the accounts
                                strInsertSQL = strInsertSQL & strAcctNum(i + 1) & "', '" & pstrOFFSETACCOUNT & "' )"
                            Else
                                strInsertSQL = strInsertSQL & pstrOFFSETACCOUNT & "', '" & strAcctNum(i + 1) & "' )"
                            End If
                        End If
                        i = i + 1
                    Wend
                    If strInsertSQL <> "" And btcRowNotInHistory(strVendorID, strInvNum, pstrCONNECTIONSTRING) Then
                        btcWriteDataToTable strInsertSQL            'Write Data to table
                    End If
                Next intRow                                     'Get next Row
            Next objWorkSheet
        End If
        If TypeName(objWKBK) <> "Nothing" Then
            objWKBK.Close False
            Set objWKBK = Nothing
        End If
        strFullPath = pstrDESTPATH & "\" & objADPFile.Name
        While objFSO.FileExists(strFullPath)
            strFullPath = strFullPath & "1"
        Wend
        objADPFile.Move strFullPath
    Next objADPFile                                         'Get next Spreadsheet
   
ExitSub:
    If TypeName(objActCell) <> "Nothing" Then
        Set objActCell = Nothing
    End If
    If TypeName(objWorkSheet) <> "Nothing" Then
        Set objWorkSheet = Nothing
    End If
    If TypeName(objWKBK) <> "Nothing" Then
        objWKBK.Close False
        Set objWKBK = Nothing
    End If
    While TypeName(objXL) <> "Nothing"
        objXL.Quit
        Set objXL = Nothing
    Wend
    If TypeName(objFSO) <> "Nothing" Then
        Set objFSO = Nothing
    End If
    If TypeName(objADPFolder) <> "Nothing" Then
        Set objADPFolder = Nothing
    End If
    If TypeName(objADPFile) <> "Nothing" Then
        Set objADPFile = Nothing
    End If
    If TypeName(objADPData) <> "Nothing" Then
        Set objADPData = Nothing
    End If
    Exit Function

ErrHandler:
    MsgBox Err.Description & " Error Number: " & CStr(Err.Number)
    Resume ExitSub
End Function
Function btcGetNumRows(objWorkSheet_i As Excel.Worksheet) As Long
Dim objActCell  As Excel.Range
Dim lngNumRows  As Long
dim i as integer
                
                                        'Set this to 1 if no header row, 2 if header row

    Set objActCell = objWorkSheet_i.Range("A" & CStr(pintDATAROWOFFSET  ))
    lngNumRows = 0                                  'Reset the row counter
    While objActCell.FormulaR1C1 <> ""              'If the cell has a value, continue counting
        lngNumRows = lngNumRows + 1                 'Count the number of rows
                                                    'Move to next row and keep counting
        Set objActCell = objWorkSheet_i.Range("A" & CStr(pintDATAROWOFFSET + lngNumRows))
    Wend
   
    btcGetNumRows = lngNumRows

End Function

                                    'You don't HAVE to use this, but you can if you want
                                    ' to be able to have your columns "float" or "grow"
Function btcGetNumCols(objWorkSheet_i As Excel.Worksheet) As Long
Dim objActCell  As Excel.Range
Dim intNumCols  As Integer
On Error GoTo ErrHandler

   
                                                'Go to the Header row and count the number of columns
    Set objActCell = objWorkSheet_i.Range(Chr(64 + intNumCols + pintCOLOFFSET) & CStr(pintROWOFFSET))
    intNumCols = pintCOLOFFSET                  'Reset the column counter
    ReDim pstrItemHdr(pintCOLOFFSET + 1) As String   'Match the column number with the array index (arrays start with 0, I'm leaving that blank and starting with 1
                                                'As long as the field doesn't contain "CK DIGIT" and until the column is less than Z
    While Left(objActCell.FormulaR1C1, 2) <> "CK" And objActCell.FormulaR1C1 <> "" And intNumCols < 26
                                                'Stores the header names for Item Class
        pstrItemHdr(intNumCols) = objActCell.FormulaR1C1
        intNumCols = intNumCols + 1             'Increment the number of columns
                                                'Match the number of columns with the array dimension
        ReDim Preserve pstrItemHdr(intNumCols) As String
                                                'Move to next Column
        Set objActCell = objWorkSheet_i.Range(Chr(64 + intNumCols) & CStr(pintROWOFFSET))
    Wend
    btcGetNumCols = intNumCols - 1

ExitSub:
    If TypeName(objActCell) <> "Nothing" Then
        Set objActCell = Nothing
    End If
    Exit Function
ErrHandler:
    MsgBox "Error: " & Err.Description & " Error Number: " & Err.Number
    Resume ExitSub

End Function

Sub btcSetAllOffSets(objWorkSheet_i As Excel.Worksheet)
Dim objActCell        As Excel.Range
Dim i                 As Integer
dim strHeaderName       as string
On Error GoTo ErrHandler
   
                'this routine is to be able to allow a "dynamic" header row.  It allows the end
            ' user to screw up the file by adding blank rows.  I'm assuming that there should be
            ' only 25 blank rows, but you could assume more.
            'I'm also looking for a specific header row, named Vendor.  You'll have to change this
            'variable to work for your spreadsheet.  If you always know that your header row is
            'Row Number 1, you don't have to use this, just set i to 1.                              
    For i = 1 To 25
        Set objActCell = objWorkSheet_i.Range("A" & CStr(i))
        If objActCell.FormulaR1C1 = strHeaderName Then
            Exit For
        End If
       
    Next i
    pintROWOFFSET = i                           'Offset the number of rows to the header row
    pintACCTROW = i - 1                         'Set the Account Number row number
    pintDATAROWOFFSET = i + 1                   'Set the Data Row Number Offset
                                                'Get the Column Offset
   
            'This part also allows for multiple columns as long as one of the header fields is named TOTAL.
            'If you know that the column numbers are fixed, then just set i to 1
    Set objActCell = objWorkSheet_i.Range("A" & CStr(i))
    For i = 1 To 25                             'Limit the Columns to A - Y
        Set objActCell = objWorkSheet_i.Range(Chr(64 + i) & CStr(pintROWOFFSET))
        If objActCell.FormulaR1C1 = "TOTAL" Then 'If the field Contains "TOTAL", The next column is the first Transaction Amount field
            Exit For
        End If
    Next i
    pintCOLOFFSET = i + 1
   
ExitSub:
    If TypeName(objActCell) <> "Nothing" Then
        Set objActCell = Nothing
    End If
    Exit Sub
ErrHandler:
    MsgBox "Error: " & Err.Description & " Error Number: " & Err.Number
    Resume ExitSub

End Sub



Sub btcWriteDataToTable(strCmdText_i As String)
Dim objConn As ADODB.Connection
Dim x       As Long
Dim objCmd  As ADODB.Command
On Error GoTo ErrHandler

    Set objConn = New ADODB.Connection
    objConn.Open pstrCONNECTIONSTRING
   
    Set objCmd = New ADODB.Command
    Set objCmd.ActiveConnection = objConn

    objConn.BeginTrans
   
        objCmd.CommandText = strCmdText_i
        objCmd.Execute
   
    objConn.CommitTrans


ExitSub:
    If TypeName(objConn) <> "Nothing" Then
        objConn.Close
        Set objConn = Nothing
    End If
    If TypeName(objCmd) <> "Nothing" Then
        Set objCmd = Nothing
    End If
    Exit Sub
ErrHandler:
    MsgBox "Error: " & Err.Description & " Error Number: " & CStr(Err.Number)
    Resume ExitSub
   
End Sub
0
 

Author Comment

by:mcrmg
ID: 11709454
wow........ @_@

according to ee Hilaire, it will check first 8 rows.  If I put a dummy data in first row, will it work?  Thanks
0
 
LVL 1

Expert Comment

by:bobsterboy
ID: 11709852
Yup,  it actually checks the first 8 rows for a string.  Once it hits a string, it's a string.  It doesn't care about anything else.  If it hits 7 numbers and 1 string, it's a string.  They do it for efficiency, but as you can see by my verbose code, it's not very efficient with mixed data.
The stupid part is that it returns a null for any datatype that doesn't match those first 8 rows.  I can't imagine how much data has been munged over the years without people knowing because there's no warnings, just no data.

To write a string in each column, here's an (abbreviated) bit of code.

Public Const pstrFILEPATH       As String = "\\YourServer\YourShare\YourFolder"
Public Const pstrDESTPATH       As String = "\\YourServer\YourShare\YourFolder\YourProcessedFolder"
Public Const pstrCONNECTIONSTRING As String = ""
Public Const pintNumCols        as Integer = 27  'Set this to the number of known columns
Dim objXL           As Excel.Application
Dim objWKBK         As Excel.Workbook
Dim objWorkSheet    As Excel.Worksheet
Dim lngNumRows      As Long
Dim intNumCols      As Integer
Dim objActCell      As Excel.Range
Dim i               As Integer
Dim intRow          As Integer
Dim intCol          As Integer
Dim strValue        As String
Dim strTrxAmt       As String
Dim strVendorID     As String
Dim strVendorName   As String
Dim strTrxDate      As String
Dim strInvNum       As String
Dim strItemCost()   As String
Dim strAcctNum()    As String
Dim objFSO          As FileSystemObject
Dim objADPFolder    As Folder
Dim objADPFile      As File
Dim objADPData      As TextStream
Dim strOldSheetName As String
Dim lngOldNumRows   As Long
Dim strInsertSQL    As String
Dim strCompanyID    As String
Dim strFullPath     As String
Dim strDescription  As String

On Error GoTo ErrHandler

            'This allows multiple files to be imported.  
    Set objFSO = New FileSystemObject
    Set objADPFolder = objFSO.GetFolder(pstrFILEPATH)
    For Each objADPFile In objADPFolder.Files
        If objADPFile.Type = "Microsoft Excel Worksheet" Then
            If TypeName(objXL) = "Nothing" Then
                Set objXL = New Excel.Application
            End If
            If TypeName(objWKBK) <> "Nothing" Then
                objWKBK.Close False
                Set objWKBK = Nothing
            End If
            Set objWKBK = objXL.Workbooks.Open(objADPFile.Path)
            For Each objWorkSheet In objWKBK.Worksheets
                                                                        'Allows for multiple worksheets to be read
                objWorkSheet.Activate                       'Activate the worksheet so it can be acted upon
    objWorkSheet.Rows("1:1").Select
    Selection.Insert Shift:=xlDown

                for i = 1 to intNumCols
                         


0
 
LVL 1

Expert Comment

by:bobsterboy
ID: 11709920
DOH, hit the stupid submit key.

continuing on...
           objWorksheet.Range(Chr(64 + i) & "1").formulaR1C1 = "STRING"
         next i
         objWorksheet.close

    If TypeName(objActCell) <> "Nothing" Then
        Set objActCell = Nothing
    End If
    If TypeName(objWorkSheet) <> "Nothing" Then
        Set objWorkSheet = Nothing
    End If
    If TypeName(objWKBK) <> "Nothing" Then
        objWKBK.Close TRUE
        Set objWKBK = Nothing
    End If
    While TypeName(objXL) <> "Nothing"
        objXL.Quit
        Set objXL = Nothing
    Wend
    If TypeName(objFSO) <> "Nothing" Then
        Set objFSO = Nothing
    End If
    If TypeName(objADPFolder) <> "Nothing" Then
        Set objADPFolder = Nothing
    End If
    If TypeName(objADPFile) <> "Nothing" Then
        Set objADPFile = Nothing
    End If
    If TypeName(objADPData) <> "Nothing" Then
        Set objADPData = Nothing
    End If
    Exit Function

0
 
LVL 9

Expert Comment

by:crescendo
ID: 11710000
I said:  DTSDestination("DestField") = CStr(DTSSource("SourceField"))


You said:  It gives me Object expected error........

I don't mean to be rude, but you are substituting the field names for the real ones, aren't you?
0
 

Author Comment

by:mcrmg
ID: 11710083
>>I don't mean to be rude, but you are substituting the field names for the real ones, aren't you?

lOl

Yes, I did.........
0
 
LVL 9

Expert Comment

by:apirnia
ID: 11710113
Can you just convert the Field in excel to Numeric befor importing it??
0
 

Author Comment

by:mcrmg
ID: 11710135
actually, I need is text type, and I tried that to covert it into text, but did not work...
0
 
LVL 34

Expert Comment

by:arbert
ID: 11710150
"It gives me Object expected error........ "

Usually you get this, like was said above, if you mistype the name, OR you didn't actually select the columns in the input/output for the activeX transformation (you can't just type them in)....


crescendo's solution is what we usually use.  Coding to the Excel object model works too, but you end up having to load Excel on the server--not a good idea...
0
 

Author Comment

by:mcrmg
ID: 11710179
DTSDestination("DestField") = DTSSource("SourceField")

When I click on "TEST", it runs okay.


But once I put this in:
DTSDestination("DestField") = CStr(DTSSource("SourceField"))

It gives me that error.
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11710202
You shouldn't be getting an Object Expected error if all the column names match up. The DTSSource column names come from the first row of the spreadsheet. If you have just clicked through a standard DTS setup you certainly shouldn't be getting that error. I just tried it and it worked fine.

Could you try it again, just to make sure? It only takes a minute and I'm pretty confident it will do what you require.
0
 

Author Comment

by:mcrmg
ID: 11714279
I just did, but still, without cstr(), it works fine.........
Dos it have something to do with version of SQL?

I have 8.00.760 (intel x86)

Thanks
0
 
LVL 9

Assisted Solution

by:crescendo
crescendo earned 350 total points
ID: 11714363
<<Dos it have something to do with version of SQL?>>

I doubt it, if you have the ability to do a transform then CStr is one of the oldest built-in functions around.

You do seem a bit out of date, though, mine is 8.00.818 (SP3)

Just a thought, the transform script language is VBScript, which is part of the VM that ships with Internet Explorer. You haven't uninstalled IE have you?

Type CScript /? at the command prompt and you should see a list of options. If you get that, then you have the VM. You can also go

http://home.wanadoo.nl/jheroen/VM/check.htm

to check the build of your VM. But don't click on any of their links...
0
 

Author Comment

by:mcrmg
ID: 11714389
I have

MS Java VM build 5.0.3810
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11714409
Which is the same as mine, so that's not the problem.
0
 

Author Comment

by:mcrmg
ID: 11714471
I will upgrade my sqwl and get back to you..Thx
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11714478
I just had an idea. Perhaps you have a NULL in your data. Replace the CStr line with:

      If IsNull(DTSSource("JobStatus")) Then
            DTSDestination("JobStatus") = DTSSource("JobStatus")
      Else
            DTSDestination("JobStatus") = CStr(DTSSource("JobStatus"))
      End If

This will leave NULLs as they are, but convert non-NULL values to text.
0
 

Author Comment

by:mcrmg
ID: 11714691
When I try to upgrade Analysis Services SP3......it gives me "An error occurred during the move data process:-145"

By the way, the new way you mentioned above did not work........   >_<


*sigh*
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11714812
0
 

Author Comment

by:mcrmg
ID: 11714939
I do not even have this folder "OLAP Server"
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server\Uninstall Info


0
 
LVL 9

Expert Comment

by:crescendo
ID: 11715018
Errmmm... have you actually got OLAP installed on your machine? Sorry I have to ask that. : )
0
 

Author Comment

by:mcrmg
ID: 11715051
I was looking at the same thing, guess what, I didnt......duh.........

In the meantime, what is this service for?  Thanks
0
 

Author Comment

by:mcrmg
ID: 11715140
I did what the link you gave me said to upgrade..... with OLAP :)

But the version is still I have 8.00.760 (intel x86)
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11715178
OLAP is a multi-dimensional view of the data, used for analysing large amounts of data. That's about the extent of my knowledge. : )
0
 
LVL 9

Assisted Solution

by:crescendo
crescendo earned 350 total points
ID: 11715295
I didn't give you a link, did I? (frantically scans back...)

No, you should be OK with the version you have as far as CStr is concerned.

OK, here's a way to test if the problem is with the data or the language. Leave all the transforms untouched, but just after Function Main() add:

    Dim SomeVar, SomeOtherVar
    SomeVar = CStr(SomeOtherVar)

and run the import. If it complains now, we know its the language or O/S, rather than the data.

   
0
 

Author Comment

by:mcrmg
ID: 11715417
okay, it gives me the same error msg.,..
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11715793
Can you give me the exact error message. Does it tell you exactly where it occurs?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:mcrmg
ID: 11716341
This is the code I use, I take out the rest.
function Main()
{


Dim SomeVar, SomeOtherVar
    SomeVar = CStr(SomeOtherVar)

      return(DTSTransformStat_OK);
}

The err msg :

ActiveX Scripting Transform '<DTS Testing Transformation?': Error parsing script - Error Code :0
Error Source = MS VBScript compilation error
Error Description: Expected statement


Error on Line 0
.  < -------- This is the error msg too


Thx
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11716457
Where did the { and } come from? Those are for Javascript, and this should be VBScript.

Did you get Javascript by default or did you put that in? You need to get rid of the braces and semicolons.
0
 

Author Comment

by:mcrmg
ID: 11716675
Okay, how stupid was I!!

However, I use:
     If IsNull(DTSSource("Source")) Then
          DTSDestination("DEST") = DTSSource("SOURCE")
     Else
         DTSDestination("DEST") = cstr(DTSSource("SOURCE"))
    End If

It gave me the same results as before.  When it sees text string, it imports it as <null> or empty.

(in creating table step, I have it as nvachar (255)

thx
0
 
LVL 9

Assisted Solution

by:crescendo
crescendo earned 350 total points
ID: 11716741
Sorry, I'm getting brain-dead. : )

     If IsNull(DTSSource("Source")) Then
          DTSDestination("DEST") = DTSSource("SOURCE")   <--- IS COPYING THE NULL VALUES!!!!!!
     Else
         DTSDestination("DEST") = cstr(DTSSource("SOURCE"))
    End If

What I should have typed is:

     If IsNull(DTSSource("Source")) Then
          DTSDestination("DEST") = "Your default value goes here"
     Else
         DTSDestination("DEST") = cstr(DTSSource("SOURCE"))
    End If

So that if it's NULL, it imports some value of your choosing, otherwise it copies the source.
0
 

Author Comment

by:mcrmg
ID: 11716858
In the first record, I am very sure that there is a string value, but it treats it as NULL, if there is a numeric value, it gets inseted....  
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11716967
So did the changes above make a difference, and cause it to be inserted correctly?

When you say "the first record", do you literally mean the header row?
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11716984
And what errors are you getting now? Presumably not "Object required" or whatever it was.
0
 

Author Comment

by:mcrmg
ID: 11717109
I do not get any error now, which is a good thing :)

But CSTR does not make a difference.  "The first row" is the first data row in excel not the header. In Excel, I have the following:



         Header Name  (They are in the same column)
1       9999999xxxxx
2                            1.01E+13
3                            2.01E+14
4       8x8x8888xxxx

The 2 and 4, ActiveX treats them as NULL



0
 
LVL 9

Expert Comment

by:crescendo
ID: 11717215
Your excel data looks a bit screwy. How did it get like that? Did it come from some export with strange delimeters?
0
 

Author Comment

by:mcrmg
ID: 11717347
I will need to check with my coworker, we get files from other companies every month, he cleans up the data and send me "CLEAN" files in excel........... (he is out for lunch.........will get back to you)


1       9999999xxxxx
2                            1.01E+13
3                            2.01E+14
4       8x8x8888xxxx

When I change the datatype in excel, (so, they align to the left), row 1 and 4 still treated as NULL

0
 
LVL 9

Expert Comment

by:crescendo
ID: 11717389
Can you post some real data, just a few rows, and the structure of your SQL table so that I can test it for myself?
0
 

Author Comment

by:mcrmg
ID: 11717494
This is the data I have (NOTE: the data with 'x' align to the left, rest align to the right)
myHeader
95939799999X524531XXXXX
0
1.01122E+13
1.1E+23
1E+16
1.01101E+23
0
4.44557E+23
1.10001E+19
0
0
00001200001200022200XXX8
4.45646E+23
11
5.55556E+23
0
9E+23
6.55668E+23
0
1.01001E+20
1E+21
1E+17
1223
1.1E+23
0
99881100123456012300XXXX
000008120005009000XXXXXX
1E+24



I use Import Wizard to build this DTS, it drop the table if any then create a new one.  Thx
0
 
LVL 34

Expert Comment

by:arbert
ID: 11718126
"1.1E+23" is scientific notation--the number is too large to display in the column.  Are you sure these columns aren't actually numeric????
0
 

Author Comment

by:mcrmg
ID: 11718173
I am sure this field is text string...........

The correct format should be something like this:
000008120005009000XXXXXX

For some reason, they gave us that data, we have to use it anyway.........
0
 
LVL 9

Assisted Solution

by:crescendo
crescendo earned 350 total points
ID: 11718222
Right, I finally have an explanation and a solution.

DTS does not always recognise column types correctly. In this case it thinks the data type for the source column is "double", and so it skips values it cannot convert. This is a know MS issue, see:

http://support.microsoft.com/default.aspx?scid=kb;en-us;236605

The solution is to save the Excel file as a text file, (.txt or .csv) or to create it as such in the first place, and then use BCP to import it. I just tried the following and it worked fine. I had to create the table first, I called it Table1, in the pubs database. The text file is called dts.csv

    bcp pubs..table1 in dts.csv -c -F2 -T

The "-c" tells BCP to import as text, the -F2 tells it to start at row 2 to ignore the header, and -T uses Windows authentication. You could also specify -U<user> -P<password> instead.

Hopefully, that's sorted!
0
 

Author Comment

by:mcrmg
ID: 11718497
Okay, I never use bcp before.   (sorry)

where can I run it?  
When you said pubs database, where is it?

Thx
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11718575
You run BCP from the command line (CMD.EXE).

Pubs is just a sample database installed with SQL Server, you need to use your own database and table, so just create the table and edit my command line.
0
 

Author Comment

by:mcrmg
ID: 11718830
It gives me SQL server does not exist or access denied

This is what I use:

bcp myDB.table1 in c:\sql_mapping\execl.csv -c -F2 -T

and

bcp myDB.table1 in c:\sql_mapping\execl.csv -c -F2 -U sa -P password
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11721039
OK... you need two dots between "myDB" and "table1", as in "myDB..table1". I assumed the server was on your local machine, it's obviously not, so add

   -SDBSERVER

where DBSERVER is the name of your SQL server.
0
 

Author Comment

by:mcrmg
ID: 11724976
I use this:
bcp dbname..Excel_Source$ in c:\sql_mapping\execl_source.csv -c -F2 -U sa -P password -servername

When I run it on local, it says:
SQL server does not exist or access denied

When I run it on the server, it says:
Unable to open BCP host data-file  
(I am sure I have source there)

Thx
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11725016
OK...

    -servername

should be

   -Sservername or -S servername

It looks like "excel" is mis-typed in

    c:\sql_mapping\execl_source.csv
0
 

Author Comment

by:mcrmg
ID: 11725039
Wow...have you ever sleeped??!!   :)

I got this:
Unexpected EOF encountered in BCP data-file.......

I am sure that there is data in the file

Thx
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11725081
How many columns have you got in your SQL table Excel_Source$ ? There needs to be the same number as in the text file you are importing. If you only have the one column in the Excel file, but two or more in the SQL table you will get this error.
0
 

Author Comment

by:mcrmg
ID: 11725145
To be sure, I would like to use import/export wizard to create a empty table(Excel_Source$), in the data source tab, there are two kind of CSV, which one should I be using? Thx
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11725184
But DTS will give you problems as we've seen in the posts above. Just create it manually in Enterprise Manager.

Using DTS with CSV or text files is a pain, as you have to create an ODBC data source. I wouldn't go there. BCP is much easier for text files.
0
 

Author Comment

by:mcrmg
ID: 11725217
Okay, as before, I use import/export wizard to drop/create/import excel file, the in "create" step, the col should be mached with source file.  However, since I "save as" from xls to csv.  Is there a possaiblity that some cols got deleted fruing coverting?  Thanks
0
 
LVL 34

Accepted Solution

by:
arbert earned 150 total points
ID: 11727206
"Using DTS with CSV or text files is a pain, as you have to create an ODBC data source. I wouldn't go there. BCP is much easier for text files"

Or use the built in text file connection--you don't have to create an ODBC data source...
0
 

Author Comment

by:mcrmg
ID: 11764211
Hi,

Sorry to reply this late. (working on some other reports)

I run this on my local box.

bcp dbname..Excel_Source$ in c:\sql_mapping\execl_source.csv -c -F2 -U sa -P password -S myServerName

got an err msg:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file

Ideas?

Thx
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11764530
Check that the path and name are correct. Should it be "excel..." rather than "execl..."?

Also check that the account used by the SQL Server service has permissions to read the file.
0
 

Author Comment

by:mcrmg
ID: 11765778
duh.....

now it shows different err msg:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP data-file
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11766762
That means you don't have the same number of columns on the file as in the database table.
0
 

Author Comment

by:mcrmg
ID: 11776718
For testing purpose, I just created 2 cols in both source file and table.
It still gives me:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP data-file

Thnaks
0
 
LVL 34

Expert Comment

by:arbert
ID: 11777330
crescendo , how does BCP import the CSV file without a format file? (or specifying a field/row terminator)?
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11777637
arbert:

I'm sure you're not asking that because you want to know... : )

The default terminators are usually OK, but yes, they will have to build a format file. I assumed they might read the documentation at some point. Also, if they actually tried to use BCP they would find out, and ask for help. I don't plan on busting my butt providing information that's going to get ignored.
0
 
LVL 34

Expert Comment

by:arbert
ID: 11777686
No, really, I did want to know...I don't use BCP enough to know if it would guess--I was wondering what would happen with any commas "inside" of a column if it "guessed" on the structure.
0
 

Author Comment

by:mcrmg
ID: 11777701
So, how can I generate/format CSV in right way so that BCP can read it........

Currently, I just "save as" from xls to csv...Thx
0
 
LVL 9

Assisted Solution

by:crescendo
crescendo earned 350 total points
ID: 11778373
OK...

BCP has a default column separator of tab, and a row separator of CRLF. You can change these on the command line. Tab is the most useful as commas don't get confused.

There are four ways of specifying the data type of each column:

Using -n uses the native datatypes in the database table. Therefore you have to have a table to start off with.

Using -c treats the columns as char.

Using -w treats the columns as unicode, nchar.

If you don't specify any of the above, BCP prompts you for the data type of each column. Having answered all this, it asks if you want to save the definitions in a format file. You can then specify this format file on further copies, so you don't have to do it each time.

Personally I prefer to have the table existing already, because it defines the data for you.

I should also mention that there's a TSQL equivalent to BCP, called BULK INSERT which is exactly the same in terms of functionality, but the parameters have different names, to conform to SQL syntax. BULK INSERT is useful because it can be included as part of a standard SQL batch or stored procedure. BCP and BULK INSERT are the fastest ways of copying data from text files to tables and vice versa.

<<So, how can I generate/format CSV in right way so that BCP can read it........>>

Just make sure that it's tab-delimited. It's one of the Excel "Save As" options. It says 'Text (Tab delimited) *.txt'.
0
 

Author Comment

by:mcrmg
ID: 11778628
Ok, I am getting somewhere.......  :)
That test file works fine.  Now, I use the reaL file, because I have "-" in one of the col, it says:
SQLState=37000, NativeError = 170
Line 1: Incorrect syntax near '-'

I thought it is tab delimied file??!! Thx
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11778904
You're going to have to get used to providing more information when you hit a problem. For example, it would be useful to know the command line parameters you are using, and to see the text, the database table definition and so on.

I would guess - and it has to be a guess - that you have a typo in your command line.
0
 

Author Comment

by:mcrmg
ID: 11784848
Sorry about that...............

I checked the command line, looks fine to me.

After I created TXT file form EXCEL, I use Import/Export Wizard, and select "TXT file" as source (as arbert suggested few comments back), it looks like it captured the missing data for me.

My question is, what is the advantages & disadvantage by doing the way I did and BCP?  Thx
0
 
LVL 9

Assisted Solution

by:crescendo
crescendo earned 350 total points
ID: 11789262
If DTS works, that sounds like a HUGE advantage. : )

If you were importing thousands of records, I would say that BCP is faster. I find it simpler to set up, too, just one command line and you are away. However, you're having problems with BCP and DTS works, so I'd say you have a solution........

arbert:

Note the sneaky use of 8 periods!

0
 
LVL 34

Expert Comment

by:arbert
ID: 11790352
lol.....not that I would be expecting anything anyway...Crescendo goes above and beyond!
0
 

Author Comment

by:mcrmg
ID: 11791881
Thank you.........guys
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now