DataType when Impoting

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.

I changed it to workbook 4.0, what would be the selection for Data Source in DTS?  Thanks

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.

Can you give me a simple example?  Thanks

I am not entirely sure which Datasource you would try...probably the Excel one still.

Here's a link to a related question with a possible solution
https://www.experts-exchange.com/questions/20089000/bug-Excel-not-importing-properly-into-SQL-server.html

Experimenting the T() function, will come back asap

why not just CAST or CONVERT the colums to varchar within the DTS transformation?

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 use this:

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

It gives me Type mismatch: 'CONVERT'

>>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

Try something like this:

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

About Cast and Convert:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

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

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....

<<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"))

It gives me Object expected error........

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

wow........ @_@

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

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
                         

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

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?

>>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.........

Can you just convert the Field in excel to Numeric befor importing it??

actually, I need is text type, and I tried that to covert it into text, but did not work...

"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...

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.

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.

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

I have

MS Java VM build 5.0.3810

Which is the same as mine, so that's not the problem.

I will upgrade my sqwl and get back to you..Thx

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.

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*

Take a look at

http://support.microsoft.com/?id=814462

I do not even have this folder "OLAP Server"
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server\Uninstall Info

Errmmm... have you actually got OLAP installed on your machine? Sorry I have to ask that. : )

I was looking at the same thing, guess what, I didnt......duh.........

In the meantime, what is this service for?  Thanks

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)

OLAP is a multi-dimensional view of the data, used for analysing large amounts of data. That's about the extent of my knowledge. : )

okay, it gives me the same error msg.,..

Can you give me the exact error message. Does it tell you exactly where it occurs?

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

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.

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

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....  

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?

And what errors are you getting now? Presumably not "Object required" or whatever it was.

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

Your excel data looks a bit screwy. How did it get like that? Did it come from some export with strange delimeters?

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

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?

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

"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????

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.........

Okay, I never use bcp before.   (sorry)

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

Thx

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.

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

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.

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

OK...

    -servername

should be

   -Sservername or -S servername

It looks like "excel" is mis-typed in

    c:\sql_mapping\execl_source.csv

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

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.

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

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.

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

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

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.

duh.....

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

That means you don't have the same number of columns on the file as in the database table.

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

crescendo , how does BCP import the CSV file without a format file? (or specifying a field/row terminator)?

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.

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.

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

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

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.

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

lol.....not that I would be expecting anything anyway...Crescendo goes above and beyond!

Thank you.........guys