Delimited text to Access

Dev Shed Forums Sponsor:

February 24th, 2003, 03:04 PM

Z24_2000

Junior Member

Join Date: Feb 2003

Posts: 3

Time spent in forums: < 1 sec
Reputation Power: 0

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

February 24th, 2003, 03:16 PM

epl

Contributing User

Join Date: Mar 2001

Location: Dublin

Posts: 413

Time spent in forums: 2 h 18 m 18 sec
Reputation Power: 13

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.

February 25th, 2003, 08:26 AM

Z24_2000

Junior Member

Join Date: Feb 2003

Posts: 3

Time spent in forums: < 1 sec
Reputation Power: 0

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 08:33 AM.

February 25th, 2003, 08:35 AM

epl

Contributing User

Join Date: Mar 2001

Location: Dublin

Posts: 413

Time spent in forums: 2 h 18 m 18 sec
Reputation Power: 13

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 11:48 AM.

February 25th, 2003, 09:02 AM

Z24_2000

Junior Member

Join Date: Feb 2003

Posts: 3

Time spent in forums: < 1 sec
Reputation Power: 0

Thank you :)

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

Thanks,
Andrew

February 27th, 2003, 02:27 PM

ngibsonau

Contributing User

Join Date: Feb 2003

Posts: 138

Time spent in forums: < 1 sec
Reputation Power: 11

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

Delimited text to Access

Developer Shed Advertisers and Affiliates