Unwanted data truncating from Excel

Question

When I try to do an import from an Excel document the comments get truncated. I have checked the usually issue that the Table would be limited but is set as:

Comments ... nvarchar(MAX)

Sample of the code, please note even running the code in Debug mode I can see the parameter is truncated before it even goes to the stored procedure.

 Dim excelConnectionString As String = (Convert.ToString("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=") & vFileNameFolder) + "; Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"

        '#### Upload, Rename and save file 
        '#### Open Excel to Parse here
        Dim ds As New DataSet
        Dim oleda As New OleDbDataAdapter()
        Dim cmdExcel As New OleDbCommand()
        '#### End - Open Excel to Parse here
        Dim vActionRef As String = ""

        Try
            Dim excelConnection As New OleDbConnection(excelConnectionString)
            With cmdExcel
                .CommandText = "Select * from [Portal$A1:BB9999]" 'Names we want to select and the name of the sheet 
                .CommandType = CommandType.Text
                .Connection = excelConnection
            End With
            excelConnection.Open()

            oleda = New OleDbDataAdapter(cmdExcel)
            oleda.Fill(ds, "dataExcel")

            If ds.Tables("dataExcel").Rows.Count > 0 Then

                '#### Stored procedure details
                Dim connection As SqlConnection
                Dim commandSQL As New SqlCommand
                Dim FRAUPRN As String = ""
                Dim ConnectionString As String = System.Configuration.ConfigurationManager.ConnectionStrings("SQLLocal").ToString()
                '########### End - Stored procedure details
                'Set date once 
                Dim vDate As Date
                vDate = DateTime.Now.AddDays(0)

                connection = New SqlConnection(ConnectionString)
                connection.Open()

                'Dims for error handling and checking for invalid characters
                Dim iImported As Integer

                For j As Integer = 0 To ds.Tables("dataExcel").Rows.Count - 1 ' counted rows so loop through, ignores first row with names in 

                    If (IsDBNull(ds.Tables("dataExcel").Rows(j)("UPRN"))) Then
                        'skip
                    Else
                        iImported = iImported + 1
                        'Bring the data across, the rows(i)("xxx") must match a name on the Excel sheet but DOES NOT have to be in order
                        With commandSQL
                            .Parameters.Clear()
                            .Connection = connection
                            .CommandText = "spAddCSVDataLine"  'Stored procedure here
                            If Trim(ds.Tables("dataExcel").Rows(j)("Comments")) = "0" Then
                                .Parameters.AddWithValue("Comments", " ")
                            Else
                                '   .Parameters.AddWithValue("Comments", If(IsDBNull(ds.Tables("dataExcel").Rows(j)("Comments")), "", Trim(ds.Tables("dataExcel").Rows(j)("Comments"))))
                                Dim vComments As String
                                vComments = ds.Tables("dataExcel").Rows(j)("Comments")
                                .Parameters.AddWithValue("Comments", vComments)
                                Session.Item("Comments") = Session.Item("Comments").ToString & "//" & vComments
                            End If

I have looked at similar questions such as ADO is truncating Excel data which talks about numerical issues but am struggling to find the reason why I am losing data before I export the data. 'Common sense' says excel is not passing over more than 255 characters but then this is programming!

score 0 · Answer 1 · answered Sep 24 '14 at 16:01

I've had all sorts of problems with the JET/Ace DB engine truncating and doing other sorry-ass guesses at data type. Check out this Microsoft article that talks a bit about how JET uses only the first 8 records to determine field length (http://support.microsoft.com/kb/189897/en-us). You can edit a registry setting to change how many records it will scan to determine field length, but the results still seem to be hit or miss for folks.

You might also find some luck in creating a dummy record at the top of the excel sheet that contains a comment with the maximum number of characters of any of your comments. Then just delete that one record after it comes through. Again... results seem to be mixed here.

Thanks, explains why I could not find a solution. I'm going to add some code that checks if the comments are over 240 characters (being on the safe side with trimming spaces) and flag them up at the end. Not ideal but it's going to all come from a Mobile/Tablet app in a few months. Appreciate the help. I'll try your suggestion to though, the less manual updating the better!!! — indofraiser, Sep 25 '14 at 10:52

Unwanted data truncating from Excel

1 Answers1