Read contents of a flat file and go through validation process

Question

I am trying to automate a process where a particular type of text file goes through a data validation process.

So far I have managed to put the open the text file using file system objects and create a count output of x lines. But when i use EOF function to retrieve x - 1 lines it can't bring individual lines it thinks the whole data is one line.

I cannot by-pass this to perform further validation checks on this data. Any suggestions on how i go about in validation list of conditions on the dataset.

e.g. of a dataset:

AAA|E0071001|D|20090401010101|EC|UKDC|BP|PARTYID|1|TR01| CPD|AAA123|Test Participant A123|P|BBB456|New Participant B456|P| ER2|NAHNAH1|Test Participant|20090401|| EAD|7||| ZZZ|5|1562192240|

above appears as one line in the text file. If i extract into excel or work it splits into 5 rows as a expect, breaks by the space gaps for e.g. before CPD which becomes a line break.

using below:

Do While objTextfile.atendofstream <> True
    objTextfile.skipline
    c = c + 1
    Loop

along with filesystemsobject i have managed to calculate #rows = 5.

but if i do the following:

For i = 1 To (NumLines - 1)
    F.readline
    text = text & F.readline
Next

strLine = F.readline

it only retrieves one line and doesn't split into 5 lines.

I also want to be able to analyse each input broken by delimiters, how would i go about doing this?

AAA|E0071001|D|20090401010101|EC|UKDC|BP|PARTYID|1|TR01| CPD|AAA123|Test Participant A123|P|BBB456|New Participant B456|P| ER2|NAHNAH1|Test Participant|20090401|| EAD|7||| ZZZ|5|1562192240| — Mohammad Hussain, Feb 15 '18 at 10:36
What are you trying to do to the .txt file? What code have you tried? — Xabier, Feb 15 '18 at 10:39
Pleas [edit] and add everything into your question. Data in comments is not readable. Also add the code you already tried to achieve this. You might also read [ask]. — Pᴇʜ, Feb 15 '18 at 10:40
above is the data set which comes as a text file and it appears as one long line in the text file but if copied into excel splits into 5 rows, when I do a row count using below: i manage to get a count of 5 lines. But if i try to extract line by line using EOF, i only get one long line and a code error which suggests its end of file — Mohammad Hussain, Feb 15 '18 at 10:40
Do While objTextfile.atendofstream <> True objTextfile.skipline c = c + 1 Loop — Mohammad Hussain, Feb 15 '18 at 10:40
Please [edit] your original question to add everything from your comments don't use comments for adding code. — Pᴇʜ, Feb 15 '18 at 10:41

Xabier · Accepted Answer · 2018-02-15T11:04:34.680

The following will count the number of lines in your txt document:

Sub foo()
Dim objFSO
Const ForReading = 1
Dim objTS 'define a TextStream object
Dim fileSpec As String
Dim lineCounter As Long
lineCounter = 0

fileSpec = "C:\Test.txt" 'change the path to whatever yours ought to be
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTS = objFSO.OpenTextFile(fileSpec, ForReading)

Do While Not objTS.AtEndOfStream
    objTS.ReadLine
    lineCounter = lineCounter + 1
Loop

MsgBox lineCounter
End Sub

UPDATE:

To replace the spaces in your text file with line breaks, the following will do that:

Sub foo()
Dim objFSO
Const ForReading = 1
Const ForWriting = 2
Dim objTS 'define a TextStream object
Dim strContents As String
Dim fileSpec As String

fileSpec = "C:\Test.txt" 'change the path to whatever yours ought to be
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTS = objFSO.OpenTextFile(fileSpec, ForReading)

'objTS.ReadAll would use read all to read the whole file into memory

Do While Not objTS.AtEndOfStream
    strContents = strContents & " " & objTS.ReadLine 'Read line by line and store all lines in strContents
Loop

strContents = Replace(strContents, " ", vbCrLf)
objTS.Close

Set objTS = objFSO.OpenTextFile(fileSpec, ForWriting)
objTS.Write strContents
objTS.Close
End Sub

i have managed to calculate #lines and get the desired result. I cannot extract the lines individually. Because when I readlines it assumes theres only one line. But clearly the double space is a line break, what can i use to identify the double spade in the text file to break it into the 5 lines. — Mohammad Hussain, Feb 15 '18 at 10:53
thank you very much, obj.AtEndofStream is what i needed for this task and have now been able to what i needed. I had another question, do i need to raise a new ticket? — Mohammad Hussain, Feb 16 '18 at 20:18
Please could you mark my response as an answer, if it has helped? Thanks.. Also yeah raise another question, will have a look at it.. :) — Xabier, Feb 16 '18 at 20:58

Read contents of a flat file and go through validation process

1 Answers1