Import text file with data on 2 separate lines

[humbleprogrammer]

Hello,

I am hoping someone can help me find a solution for this. I have reports in .txt format that I receive from outside offices. I need to import these files into Access and then match the data to existing orders. That's all fine but the report formats have the data on 2 lines like below:

orderno amount name phoneno
orderstatus

I need to update the order status info per order number but the order status is on a separate line. Does anyone know how I can import the data and somehow associate the order status with the orderno from the line directly above it? Any help would be greatly appreciated.

Thanks!

http://www.gosmart4u.com/

 

[SteveR77]

How is the text file delimited?

 

[humbleprogrammer]

Thanks for you reply. The file is fixed width with 4 spaces between each field.

http://www.gosmart4u.com/

 

[longhair]

humbleprogrammer,

is there actually a CRLF after phoneno? or does it just appear to be that the data is wrapped?

regards,

longhair

 

[humbleprogrammer]

There is actually a CRLF.

http://www.gosmart4u.com/

 

[bboffin]

Since the data is fixed format it's pretty easy to write VBA code to import the data yourself.

For example:

Dim MyString1 As String
Dim MyString2 As String
Dim strOrderno As String
Dim dblAmount As Double
Dim strName As String
Dim strPhoneno As String
Dim strOrderstatus As String

Open "TESTFILE.txt" For Input As #1	' Open file for input.
Do While Not EOF(1)	' Loop until end of file.
    Line Input #1, MyString1	' Read first line
    Line Input #1, MyString2    ' Read second line
    strOrderNo = Trim(Mid(MyString1, 1, 10))
    dblAmount = CDbl(Trim(Mid(MyString1, 15, 6)))
    strName = Trim(Mid(MyString1, 25, 20))
    strPhoneno = Trim(Mid(MyString1, 40, 12)
    strOrderStatus = Trim(Mid(MyString2, 1, 10)
    ' insert data into required table using an append query
    .
    .
Loop
Close #1	' Close file.

This code could be put behind the click event for a command button.

You will need to work out the correct values for the start column and length in each of the Mid functions by examining the actual text file. The Trim function removes any extraneous spaces.

By the way you should have a look at the txt file to see if contains any tab characters. If so these will first need to be expanded into spaces for this to work.

Let me know if you need some code to do this.