Import Text File into Access

[Fkhan]

Hello friends,

I am having a lot of trouble to make a simple VB form to import a flat text file into Access. Following are a few lines of the text file:

.S1 (ME,23,75,137;PE,41-42;S1E,63;S2E,57;S3E,54)
Uses a variety of strategies in the problem-solving process
.1.1.1.BP (ME,24;PE,22;S1E,63)
Draws pictures to represent problems
.......................................
.......................................

What I would like to have is import it into Field1,Field2 and Field3 as follows:

Field1=.S1
Field1=ME,23,75,137;PE,41-42;S1E,63;S2E,57;S3E,54
Field3=Uses a variety of strategies in the problem-solving process
Field1=.1.1.1.BP
Field2=ME,24;PE,22;S1E,63
Field3=Draws pictures to represent problems
........................

Can someone please help me? Thanks,

Farid.

[czimmerman]

If you have VB6, use the split function. First split using delimiter vbrclf. Then loop through the array you created. For every even element, split using delimiter "(". Write element 0 into field one, and left(len(element1) - 1) into field 2.

Write every odd element of the original array into element 3.

Charlie Zimmerman
http://www.freevbcode.com

[Fkhan]

Thanks for your excellent hint. I have VB 6.0, but can't find the delimiter 'vbrclf' to use in the split function ??

- Farid.

[czimmerman]

My mistake, typo. The delimiter is vbCrLf. This is just an application defined constant for Chr(13) + chr(10).

CZ

[rmills]

If the text files are going to be the same, treat them as you would a text-delimited file. Using Access import file wizard, you create a template that has the starting, size, and ending point for each field. A filler field is usually included at the end for future changes. The wizard simply counts spaces from left to right and puts info in its place. This can be imported to create a new table of into an existing table. After creation of the template, this can be coded in Access or VB using the DoCommand.TransferText acImportFixed, "name of template", table name, path and filename. Hope that this helps.

rmills
ron.mills@wcom.com