Excel 255 character limit for a cell? [Resolved]

[OneSource]

Hi.

I'm transferring data from an Excel spreadsheet to a SQL Server DB via ADO. The code works as expected, but all of the data is not transferred over if a cell contains more than 255 characters - in other words, it is truncated. Does anyone know why this is happening? Better yet, a solution for the problem!

Thanks,
OneSource

[Ecniv]

At a guess the Sql server field is set to 255 max... either that or excel is as crap as I thought and its not letting you do it.

Vince

[OneSource]

The SQL Server field has a size of 4000 characters, so that rules out option 1! That only leaves option 2: "excel is as crap as I thought and its not letting you do it."

Thanks anyway.
OneSource

[RobDog888]

How are you transfering it to SQL? Could it be that you may be
using a variable of string type of default length 255? If you are
using the variable, then just declare it of a greater length. I
thought of this since you say that Excel has more than 255 chars
in a cell. This would lead me to believe that Excel can handle the
larger length. So it must be in your transfer code.

Ps. Excel can handle the larger length. Just checked.
Can you post some of your transfer code?

HTH

[OneSource]

Thanks for your response.

I was able to get around the problem by using a string array and a loop.

Public Sub Main()
    Dim intLength As Integer
    Dim strUpdateText() As String
    Dim bytCtr As Byte
 
    ReDim strUpdateText(0)
    
    intLength = Len(Cells(1, 1).Text)
    
    Do
        strUpdateText(UBound(strUpdateText)) = Mid(Cells(1, 1).Text, (bytCtr * 255) + 1, 255)
        bytCtr = bytCtr + 1
        ReDim Preserve strUpdateText(UBound(strUpdateText) + 1)
        intLength = intLength - 255
    Loop Until intLength < 0
 
    For bytCtr = 0 To UBound(strUpdateText) - 1
        If bytCtr = 0 Then
            'Update with strupdatetext(0)
            strSQL = "UPDATE CultureProperties SET PropertyValue = '" & strUpdateText(bytCtr) & "' " & _
                     "WHERE refID = " & CInt(Cells(intRowCtr, 1)) & " AND Culture = 'fr'"
        Else
            'Append with current text and strupdatetext(bytctr)
            strSQL = "UPDATE CultureProperties SET PropertyValue = PropertyValue + '" & strUpdateText(bytCtr) & "' " & _
                     "WHERE refID = " & CInt(Cells(intRowCtr, 1)) & " AND Culture = 'fr'"
        End If
        'Debug.Print strUpdateText(bytCtr)
    Next
End Sub

I know it's cludgy, but it works. But I think you are right. I was using the default string type. When you refer to declaring the variable at greater length, do you mean something like this:

Dim strSQL$(5000)

?

Anyway, I was trying to figure this out for a co-worker and saw the problem on her PC. When I just tried to transfer the data using a string variable declared in the conventional way (Dim strUpdateText As String) on my PC, it worked! I don't know why it would work on mine and not hers.

Anyway, thanks again!

[RobDog888]

Thanks, and kind of what I meant. The way your have it is as a
string array. My way creates a string buffer of a length of 5000
characters. Then use Trim to remove any trailing spaces that you
don't need.

Dim strString As String * 5000

[OneSource]

Thanks!

[RobDog888]

No prob.