|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
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
February 24th, 2003, 03:04 PM
|
|||
|
|||
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
February 24th, 2003, 03:16 PM
|
|||
|
|||
|
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
February 25th, 2003, 08:26 AM
|
|||
|
|||
|
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.
|
|
#4
February 25th, 2003, 08:35 AM
|
|||
|
|||
|
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.
|
|
#6
February 27th, 2003, 02:27 PM
|
|||
|
|||
|
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 |
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Thank you :)
Linear Mode
