dcsimg


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Recordset help

  1. #1
    Join Date
    Dec 2009
    Posts
    2

    Recordset help

    I am trying to poulate a recordset via a csv.
    Here is my code for doing so:

    Set oConn = CreateObject("ADODB.CONNECTION")
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFilePath & ";Extended Properties=""text;HDR=No;FMT=Delimited"""

    Set oRS = CreateObject("ADODB.RECORDSET")
    oRS.Fields.add ("go")

    'Now actually open the text file and import into Excel
    oRS.Open "SELECT * FROM " & strFileName, oConn, 3, 3

    Here is a sample of the CSV data:

    LND01,DS025625,416418,5/22/2009,SHARE,917,FOODPRO,DIN,41.49,1,41.49,1,LND01,534145,3665,,,,10188
    LND01,DS025626,82281653L,4/8/2009,SHARE,2445,FOODPRO,DIN,96.1,1,96.1,1,LND01,651050,3665,,,,10334
    LND01,DS025696,1165899,5/21/2009,SHARE,2122,FOODPRO,DIN,-129.25,1,-129.25,1,LND01,651005,3690,,,,10154
    LND01,DS025697,1166151,5/22/2009,SHARE,2122,FOODPRO,DIN,5.1,1,5.1,1,LND01,651005,3660,,,,10200
    LND01,DS025698,21033913405,5/14/2009,SHARE,6622,FOODPRO,DIN,27.03,1,27.03,1,LND01,651020,3690,,,,10202
    LND01,DS025699,21033913406,5/14/2009,SHARE,6622,FOODPRO,DIN,16.76,1,16.76,1,LND01,651020,3665,,,,10188
    LND01,DS025700,21033913407,5/14/2009,SHARE,6622,FOODPRO,DIN,15.33,1,15.33,1,LND01,651020,3665,,,,10162


    Hopefully this doesn't get to messy but i wanted to give a good representation of the issue so you can replicate if necessary. My issue is with the query works fine starting out. Once I hit the second record the third column (82281653L) come s in as empty. I believe the issue is with the field type. I don't know how these get set in the recordset, but when I drill into the recordset and fields and look at this field it is set as adDouble. How i can change this so it is a string?

    Thanks for the help.

  2. #2
    Join Date
    Jun 2009
    Posts
    135
    I think you would need to surround the values with quotes in the file...



    Good Luck

  3. #3
    Join Date
    Mar 2009
    Location
    Italy - Breganze (VI)
    Posts
    120
    There are some issue about.

    Your error is because the value in 1st line is a number (416418), so the provider 'deduce' that this filed is a Numeric type, while the value in 2nd line is a string (82281653L) so the provider assume that isn't a valid type, then discard it.

    Unfortunately what vb5prgrmr suggest you not seem to solve the problem.

    The solution is to import CSV file into new TABLE in your database, but this can be achieved using SCHEMA.INI technique :
    ACC: How to Use Schema.ini for Accessing Text Data
    http://support.microsoft.com/kb/149090/en-us

    which is subordinate to some rules (case based).
    In your case the SCHEMA.INI :
    1. must contains Name, Type and Lenght (for Text field type) of each field.
    2. this file MUST exists in the same folder or CSV file.
    3. section name [mydata.csv] must be the CSV name you use (then replace 'mydata.csv' with your filename.
    The whole contents should be like this (I make it for your CSV file):
    Code:
    [mydata.csv]
    ColNameHeader=False
    Format=Delimited(;)
    MaxScanRows=0
    CharacterSet=ANSI
    col1=Field1 Text Width 10
    col2=Field2 Text Width 10
    col3=Field3 Text Width 10
    col4=Field4 DateTime
    col5=Field5 Text Width 10
    col6=Field6 Long
    col7=Field7 Text Width 3
    col8=Field8 Long
    col9=Field9 Long
    col10=Field10 Long
    col11=Field11 Long
    col12=Field12 Long
    col13=Field13 Text Width 10
    col14=Field14 Long
    col15=Field15 Long
    col16=Field16 Text Width 10
    col17=Field17 Text Width 10
    col18=Field18 Text Width 10
    col19=Field19 Long
    Please note this line
    Code:
    Format=Delimited(;)
    this is because I'm italian, on my system the comma (,) can be the decimal separator, so I cannot use it. Therefore I replaced comma ',' with ';' in your CSV file.
    Check in you system (american or english) about this and if need same thing, or use comma as well.

    Even though you don't have field header in CSV, the TABLE need fields name, to relate to fields name in SCHEMA.INI, therefore you must use them in SQL command:
    Code:
    Dim sHeader As String
    sHeader = "Field1,Field2,Field3,Field4,Field5,Field6,Field7,Field8,Field9,Field10,"
    sHeader = sHeader & "Field11,Field12,Field13,Field14,Field15,Field16,Field17,Field18,Field19"
    Now you can compose SQL command as need:
    Code:
    Dim sSQL As String
    Dim sMDBPath As String
    Dim lngImported As String
    sMDBPath = "C:\YourDatabase.MDB" ' change to your db!
    sSQL = "SELECT " & sHeader & " INTO NewTable IN '" & sMDBPath & "'"
    sSQL = sSQL & " FROM " & strFileName
        
    oConn.Execute sSQL, lngImported, adCmdText Or adExecuteNoRecords
    
    Debug.Print lngImported & " records imported."
    After imported, you can open the table into ADODB.Recordset.

    Last edited by gibra; 12-08-2009 at 07:32 AM.

  4. #4
    Join Date
    Dec 2009
    Posts
    2
    Gibra, that is freakin great and should work just fine i think. thanks so much for the help!!!!!!!!

Similar Threads

  1. Replies: 0
    Last Post: 11-13-2001, 12:34 PM
  2. Creating a Recordset
    By Scott in forum ASP.NET
    Replies: 0
    Last Post: 11-12-2001, 10:14 PM
  3. Requerying a reshaped recordset
    By Jonathan Gibbs in forum VB Classic
    Replies: 0
    Last Post: 08-09-2001, 02:34 PM
  4. Can't change field value in a recordset
    By Craig in forum VB Classic
    Replies: 5
    Last Post: 02-17-2001, 01:23 AM
  5. ?Build a Recordset from an Existing Recordset
    By Patrick in forum VB Classic
    Replies: 1
    Last Post: 10-11-2000, 11:45 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center