Solved Read Text File Into Table Issue

[gemma-the-husky]

really you need a different process for each file type.
So if HSBC have a one format, and NatWest have another, you have to use the correct process.

Basically if a file consists of a sequence of lines in a particular order, but not all the same format.

open file
repeat
    read a line
    test the line to see what it is
    process the line
 
     'maybe build up a record based on a sequence of
    'header
    'detail
    'detail
    'detail
    'detail
    'footer

until end of file
close file

there is/was a product called monarch that could text a text/prn file, and spl.it out data from your analysis of the file structure.

 

[Cotswold]

In the base, use the Line Input Import Text button

Gr. L

I've downloaded your database twice now and and cannot see the additional/modified button [Line Input Import Button] you mention.

 

[Gasman]

Hi Lampje, As I understood from your suggestion on #20 this was your code.

Do Until EOF(iFile)
    strTextLine = strTextLine & Input(1, #iFile) ' Ex Lampje
    If Asc(Right(strTextLine, 1)) = 10 Or Asc(Right(strTextLine, 1)) = 13 Then
        MyRS.AddNew
        MyRS("Field128") = Trim(strTextLine)
        MyRS.Update
        strTextLine = ""
    End If
Loop
Close #ifile
MyRs.Close
MyDb.Close

If your code shown above is run then the last record is skipped. The database you attached on #22 has my code running and will append the last record (the sixth) in the text file. You need to add the following between Loop and Close #iFile
MyRS.AddNew
MyRS("Field128") = Trim(strTextLine)
MyRS.Update

I can only reiterate that I cannot see why that is necessary but it is.

Presumably no char 10 or 13 at the end of the file then?
Just add OR EOF(iFile) to the testing line?

 

[Cotswold]

really you need a different process for each file type.
So if HSBC have a one format, and NatWest have another, you have to use the correct process.

Basically if a file consists of a sequence of lines in a particular order, but not all the same format.

open file
repeat
    read a line
    test the line to see what it is
    process the line

     'maybe build up a record based on a sequence of
    'header
    'detail
    'detail
    'detail
    'detail
    'footer

until end of file
close file

there is/was a product called monarch that could text a text/prn file, and spl.it out data from your analysis of the file structure.

Thank Gemma, all of that isn't a problem to me, in fact it is all completed. I have also included the option to read the various OFX files.
My problem was that I couldn't see why a While Not EOF()/Loop pulled the whole file into one record, then why if I import line by line, it doesn't include the last line in the text file. These routines are all over the internet but they do not appear to work as you may expect. If you are processing in a Loop you expect all transactions to be completed within it.

 

[gemma-the-husky]

Ah. I see.

If you process a sequence as I previously gave, and if there's no footer, you only know it's the end of a record, when you get a new header record instead of a detail record so the loop becomes like below. You only know you have finished a record when you get a new header, but at the end you just get the EOF instead of a new record.

open file
repeat
    read a line
    if header then
         process last record - IF THERE IS ONE
         start a new record
   else
        detail record for current record
   end if

until end of file
close file

process the very last record - IF THE FILE WAS NOT EMPTY

now when you get to the EOF you still have the last record you were collating to process, because you never encountered a new header record.
You have to build in to the loop a couple of exceptions to

a) test and not process a previous record for the first record/start of file and
b) to process the last record because the end of file stopped you getting another header.

 

[Cotswold]

Ah. I see.

If you process a sequence as I previously gave, and if there's no footer, you only know it's the end of a record, when you get a new header record instead of a detail record so the loop becomes like below. You only know you have finished a record when you get a new header, but at the end you just get the EOF instead of a new record.

open file
repeat
    read a line
    if header then
         process last record - IF THERE IS ONE
         start a new record
   else
        detail record for current record
   end if

until end of file
close file

process the very last record - IF THE FILE WAS NOT EMPTY

now when you get to the EOF you still have the last record you were collating to process, because you never encountered a new header record.
You have to build in to the loop a couple of exceptions to

a) test and not process a previous record for the first record/start of file and
b) to process the last record because the end of file stopped you getting another header.

Hello Gemma, I am wondering if you have looked at the start of this and #11 where I attached a database & sample text file.

The issue was that "Line Input #iFile, strTextLine" imported the whole file, even though a LF or CR were present on each text line.
By using the later routine of reading a character at a time ie: "oneChar = Input$(1, #iFile)" and processing the result allowed me to pull it line by line. However, always the last record in the file was not included. Unless I added what should be unnecessary code outside of the loop.

Everything has been working for a couple of days now but as I say I cannot quite understand why the decision in the If/Else/Endif is ignored and the EOF() is activated instead.

I have now concluded that as LINE INPUT is referred to in the Access Language Reference (ALR) is in relation to reading Sequential Files, it is not working as expected on a plain Text file. The ALR states specifically "Reads a line from a sequential file into a string". Which it clearly does not do in the case of a plain text file. I would think from my experience, neither can I read the Text file as a Random or Binary file, so haven't attempted to do so.

Using the INPUT, INPUT$ Functions provided the solution, even though again the ALR refers again to Sequential files in their explanation. However, by reading the lines character by character, as I say, is the way around it. Additionally, I can with that same code routine import a CSV File a full line at a time and then decide how to handle the data on those lines for importing to another table later.

 

[Cotswold]

You should try using FSO rather than the Access file methods.

Thanks Pat, everything is fine now. Plus I'll be doing some work to run on Linux and will probably be using that routine.

 

[gemma-the-husky]

@Cotswold

No, I hadn't looked, and I can see what the issue is now.

Funnily enough, I have just had that same issue in another project. I think Excel is the culprit.
I think a new line in Excel is just chr(10), whereas a new line in Access is Chr(13) + Chr(10) (vbCrLf) (ie vbCr and vbLf)
That's the problem that happened to me when splitting addresses in access, that had been tidied up in Excel. I thought they had just used commas in a single line of text 10,AnySteeet,SomeTown,Postcode, but they hadn't. It was only when I checked the string length that I realised there were non-printing characters within the string which were all chr(10)

[edit. It took me longer than it should have to work out what was going on, because I just couldn't get to the bottom of it either. You having the same issue helped me see the real underlying issue, I think. ]

So Access doesn't read the chr(10)'s as a end of line, just a non-printing character.
You can split the full string into substrings with split, using chr(10) as a delimiter
See below

Sub readfile()

Dim fname As String
Dim fno As Long
Dim l As String
Dim lines As Long
Dim a() As String

fname = CurrentProject.Path & "\plaintext.Txt"

lines = 0
fno = FreeFile
Open fname For Input As fno
While Not EOF(fno)
    Line Input #fno, l
    lines = lines + 1
Wend
Close fno

MsgBox "read " & lines & " lines " & vbCrLf & l
'aha - just reads 1 string

'split the string using chr(10) as a delimiter
a = Split(l, Chr(10))
MsgBox UBound(a) + 1 & "  lines read"
'now 6 strings

End Sub

 

[Lampje]

@Cotswold

My excuse me, I had added the wrong database.

Gr. L

 

[Cotswold]

@Cotswold

No, I hadn't looked, and I can see what the issue is now.

Funnily enough, I have just had that same issue in another project. I think Excel is the culprit.
I think a new line in Excel is just chr(10), whereas a new line in Access is Chr(13) + Chr(10) (vbCrLf) (ie vbCr and vbLf)
That's the problem that happened to me when splitting addresses in access, that had been tidied up in Excel. I thought they had just used commas in a single of text 10,AnySteeet,SomeTown,Postcode, but they hadn't. It was only when I checked the string length that I realised there were non-printing characters within the string which were all chr(10)

[edit. It took me longer than it should have to work out what was going on, because I just couldn't get to the bottom of it either. You having the same issue helped me see the real underlying issue, I think. ]

So Access doesn't read the chr(10)'s as a end of line, just a non-printing character.
You can split the full string into substrings with split, using chr(10) as a delimiter
See below

Sub readfile()

Dim fname As String
Dim fno As Long
Dim l As String
Dim lines As Long
Dim a() As String

fname = CurrentProject.Path & "\plaintext.Txt"

lines = 0
fno = FreeFile
Open fname For Input As fno
While Not EOF(fno)
    Line Input #fno, l
    lines = lines + 1
Wend
Close fno

MsgBox "read " & lines & " lines " & vbCrLf & l
'aha - just reads 1 string

'split the string using chr(10) as a delimiter
a = Split(l, Chr(10))
MsgBox UBound(a) + 1 & "  lines read"
'now 6 strings

End Sub

Thanks for that Gemma.
The While Not EOF()/Wend is not required as I said in an earlier post. The Line Input function reads the whole file however large. You can test that by checking the Lines value on exit which should be 1. I think that its design is based on handling sequential files and as a result it will take the whole of a simple text file. Probably has the same original code from the 1980s.

The method I had in #18 used the Input() Function concatenated single characters into one line for appending into Access, worked within the If/Else/EndIf and did what I needed to do.

Then Lampje's code in #35 shortened the code with just an If/Endif. Either is fine, just what suits. Don't know why really, but I usually prefer to do If/Else/Endif, particularly when starting a new Function..

Have to admit that I had no idea about the Split() function, not in my Language Reference. Very useful.
{EDIT 20thDec} Since looked up online and discovered another 21 Functions that weren't in my Language Reference book. They are now!

 

[gemma-the-husky]

Thanks for that Gemma.
The While Not EOF()/Wend is not required as I said in an earlier post. The Line Input function reads the whole file however large. You can test that by checking the Lines value on exit which should be 1. I think that its design is based on handling sequential files and as a result it will take the whole of a simple text file. Probably has the same original code from the 1980s.

The method I had in #18 used the Input() Function concatenated single characters into one line for appending into Access, worked within the If/Else/EndIf and did what I needed to do.

Then Lampje's code in #35 shortened the code with just an If/Endif. Either is fine, just what suits. Don't know why really, but I usually prefer to do If/Else/Endif, particularly when starting a new Function..

Have to admit that I had no idea about the Split() function, not in my Language Reference. Very useful.

Well. personally, I wouldn't want to trust/assume that there are no CrLf characters that Access would recognise. It's better to use a loop, I think than get it wrong, and not actually read the whole file.. The worst case loop just iterates once, as yours is doing. But having loaded the file as one long line, it's then useful to understand why that happened. I would rather not read character by character, as it's likely to be a lot more work.

Having split the original file based on chr(10), you can then split each line based on the field separator, which might be a comma, or a pipe symbol | or even just a space, again avoiding having to read characters. The first few characters on each line may say "head", "line", "foot" or something else that can help. As long as every file from the same source is consistent you will be OK.

 

[gemma-the-husky]

Well, I'm struggling now. I assumed something, and it made an ass of me and me, rather than u and me.

Because I had the same issue, I assumed excel was inserting just a chr(10) instead of a chr(13) and a chr(10) for a new line in a cell, but that doesn't appear to be the case. Alt+Enter inserts a chr(13)chr(10), and word wrap determines whether you see the newlines or not. So I have no idea how a load of addresses appeared in an excel spreadsheet I was using with just chr(10) rather both chars. Maybe somehow they imported into access with just the chr(10)'s. Anyway, I don't need to investigate that further.

@Pat Hartman
I wasn't saying the file format was messed up. I was saying there might be blocks of text split by chr(10)'s within larger blocks split by Chr(13)chr(10)'s. If you are sure you know the file format, and you know there are only chr(10)'s then you have alternative ways of dealing with it.

The sample I downloaded from post #11 just has random text strings separated by chr(10)s.