#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    3
    Rep Power
    0

    Question Delimited text to Access


    Hi there,

    I am currently working on enhancing a VB 6.0 application that used to get a fixed length text file and export that information to an Access table. However, the new file format is in a delimited text form. Now, I've never dealt with delimited text before in VB as I am a relatively new programmer. However, I have used ADO and Excel before, so I am familiar with ADO, I just don't know how to pick up delimited text fields, so any help would be muchly appreciated.

    Thanks,
    Andrew

    P.S. Here is a sample record

    "19710928"/"F"/"P7K 5Z2"

    The first field being the DOB, the second being Gender and the third being postal code.

    Thanks again
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2001
    Location
    Dublin
    Posts
    413
    Rep Power
    14
    Use Split() to turn your data into an array and iterate over the elements - performing any text to type conversions as necessary.

    You should check if there are any / characters allowed in your data or else strip the quotes from the start and end and then split using "/" (ie 3 characters, double quote then forward slash then double quote) as the delimeter... etc.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    3
    Rep Power
    0

    Question Example?


    Hi there epl,

    Thanks for the reply, however, I'm still a little confused as how to use the Split() function, can you possibly give me an example using the data I supplied above?

    Thanks again,
    Andrew

    P.S. Slashes are not allowed in the data.
    Last edited by Z24_2000; February 25th, 2003 at 09:33 AM.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2001
    Location
    Dublin
    Posts
    413
    Rep Power
    14
    I'd do it like this because each data field is wrapped in quotes:
    Code:
    Dim dataStr as String, tokenStr as String, dataArray as Variant
      Let dataStr = "19710928""/""F""/""P7K 5Z2"
      Let tokenStr = "/"
      Let dataArray = Split(Mid$(dataStr, 2, Len(dataStr) - 2), """" & tokenStr & """", -1, vbBinaryCompare)
    Without the quotes (ie just a / between items), or if I wanted to keep the quotes to remove later, I'd do this:
    Code:
    Dim dataStr as String, tokenStr as String, dataArray as Variant
      Let dataStr = "19710928""/""F""/""P7K 5Z2"
      Let tokenStr = "/"
      Let dataArray = Split(dataStr, tokenStr, -1, vbBinaryCompare)
    You can then iterate over your results in dataArray and assign them given elements accordingly.

    Hope that helps.

    (the reapeated double quotes - ""/"" - each time above are the way vb requires you to write quotes within strings, by the way)
    Last edited by epl; February 25th, 2003 at 12:48 PM.
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    3
    Rep Power
    0

    Thumbs up Thank you :)


    Thank you very much epl, I'll give it a try

    Thanks,
    Andrew
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    138
    Rep Power
    12
    You could try using the ADO text driver.
    It works on "," seperated, tab seperated and user definable character seperated.

    Code:
    Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
    "Dbq=\somepath\;" & _
    "Extensions=asc,csv,tab,txt;" & _
    "Persist Security Info=False"
    only thing you have to be wary of is the MAXSCANROWS, how the driver determines the type of the columns. I had a person with their last name True in my data and it converted the column to boolean!
    --

    ngibsonau

IMN logo majestic logo threadwatch logo seochat tools logo