Using recordset to read a csv file- Problem with values-VBForums
Results 1 to 9 of 9

Thread: Using recordset to read a csv file- Problem with values

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2009
    Posts
    5

    Using recordset to read a csv file- Problem with values

    Hello!

    Im opening a csv file using adodb.
    No problems opening, but when i try to retrieve a value from a column that has 10 or more characters, it just dont return.
    But it only happens with numbers. Imagine i have a column with value 1234567890
    objRecordset.Fields.Item(0).Value returns null
    but if its 123456789, it returns the number.
    Same happens with plain text. Allways return the all text.

    How can i set to return more than 9 characters?

    Thanks in advance

  2. #2
    VBaholic & Loving It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    GetWindowRect()
    Posts
    12,068

    Re: Using recordset to read a csv file- Problem with values

    You may want to show us your connection string. Sounds like that field is being interpreted as Long vs string. What does objRecordset.Fields(0).Type return?
    Insomnia is just a byproduct of, "It can't be done"

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum. Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts


    {Alpha Image Control} {Memory Leak FAQ} {GDI+ Classes/Samples} {Unicode Open/Save Dialog} {Icon Organizer/Extractor}
    {VBA Control Arrays} {XP/Vista Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  3. #3

    Thread Starter
    New Member
    Join Date
    Aug 2009
    Posts
    5

    Re: Using recordset to read a csv file- Problem with values

    Set m_objCollection = New Collection

    Set m_objConnection = New ADODB.Connection
    Set m_objRecordset = New ADODB.Recordset

    m_objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & App.Path & ";" & _
    "Extended Properties=""text;HDR=YES;FMT=Delimited"""

    m_objRecordset.Open "SELECT * FROM " & strFicheiro, m_objConnection, adOpenStatic, adLockOptimistic, adCmdText

    and the type is 3, where the text column is 202

  4. #4
    VBaholic & Loving It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    GetWindowRect()
    Posts
    12,068

    Re: Using recordset to read a csv file- Problem with values

    type 3 = adInteger, type 202 = adVarWChar
    Is this a csv you create? If so, when you write the csv values, surround them with "" characters, i.e., Chr$(34). That should force the interpretation to string vs numeric, assuming string is the intent.
    Insomnia is just a byproduct of, "It can't be done"

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum. Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts


    {Alpha Image Control} {Memory Leak FAQ} {GDI+ Classes/Samples} {Unicode Open/Save Dialog} {Icon Organizer/Extractor}
    {VBA Control Arrays} {XP/Vista Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  5. #5

    Thread Starter
    New Member
    Join Date
    Aug 2009
    Posts
    5

    Re: Using recordset to read a csv file- Problem with values

    this csv comes from excel.
    how can i change that? this "little" problem is ruinning my app :S

  6. #6
    VBaholic & Loving It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    GetWindowRect()
    Posts
    12,068

    Re: Using recordset to read a csv file- Problem with values

    You may want to consider creating/using a Schema.INI file, discussed here on MSDN. You can also search this forum for Schema.ini

    Of course you always have the option of manually reading/processing the file without the use of ADO.
    Insomnia is just a byproduct of, "It can't be done"

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum. Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts


    {Alpha Image Control} {Memory Leak FAQ} {GDI+ Classes/Samples} {Unicode Open/Save Dialog} {Icon Organizer/Extractor}
    {VBA Control Arrays} {XP/Vista Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  7. #7

    Thread Starter
    New Member
    Join Date
    Aug 2009
    Posts
    5

    Re: Using recordset to read a csv file- Problem with values

    im already using a schma.ini file because our csv are delimited by ( and not (,)

    [Conv.csv]
    Format=Delimited(


    but, how can i use the schema.ini file to change the data type?

  8. #8
    VBaholic & Loving It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    GetWindowRect()
    Posts
    12,068

    Re: Using recordset to read a csv file- Problem with values

    Quote Originally Posted by MSDN
    Col1=LastName Text
    Col2=FirstName Text
    Col3=MiddleInitial Text

    Notice that you need to indicate the type of data contained in the field. In this case, all three fields contain text (string) data. Other data types you can specify in a Schema.ini file include:

    * Short
    * Long
    * Currency
    * Single
    * Double
    * DateTime
    * Memo
    Suggest taking 5 minutes and reading the entire page in the link I provided, then trying it, to see it your issue is resolved.
    Insomnia is just a byproduct of, "It can't be done"

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum. Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts


    {Alpha Image Control} {Memory Leak FAQ} {GDI+ Classes/Samples} {Unicode Open/Save Dialog} {Icon Organizer/Extractor}
    {VBA Control Arrays} {XP/Vista Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  9. #9

    Thread Starter
    New Member
    Join Date
    Aug 2009
    Posts
    5

    Re: Using recordset to read a csv file- Problem with values

    Thank you m8.
    Got it resolved

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.