|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
|
|
Stay one step ahead of the competition. Evaluate and give feedback
on some of the hottest web development tools on the market today.
Make your opinion heard! Click
Here
|
|
#1
|
|||
|
|||
|
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
|
|||
|
|||
|
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. |
|
#3
|
|||
|
|||
|
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. |
|
#4
|
|||
|
|||
|
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. |
|
#5
|
|||
|
|||
|
Thank you very much epl, I'll give it a try
![]() Thanks, Andrew |
|
#6
|
|||
|
|||
|
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 |
![]() |
| Viewing: Dev Shed Forums > Programming Languages - More > Visual Basic Programming > Delimited text to Access |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|