Tricky Import

[slynch401k]

I have a comma deliminated txt file that I am trying to import into a table. The problem is that random comments are in the text file. For example:

11113, Yes, No
;Comment Hello
12411,No,No
14325,Yes,No
;Comment Whatever

So I think that I need to actually read the file line by line and if the line starts with a ; delete it and add a comma and put it on the previous line so that I get the following:

11113, Yes, No ;Comment Hello
12411,No,No
14325,Yes,No ;Comment Whatever

Then output the file as a new text file so that it is in order and import it as a standard comma deliminated file.

Any ideas as to approaches would be appreciated. I can find alot of code on how to import text files and I will take a stab at it but no one has code that encounters this.

Thanks in advance

Steve

 

[orna]

Import the file to a temp table, delimited by comma, and then go through the records and insert them to your table and deal with the field that starts with ";"

 

[slynch401k]

That's not going to do me any good as I think it would be too sloppy. I would have to add another column at then end of the table on import and then try to send the next line that has the comment field back one row to this field and their is not a way to reference it.

I am thinking someone has dealt with it before and I can modify their code.

 

[Rabbie]

That's not going to do me any good as I think it would be too sloppy. I would have to add another column at then end of the table on import and then try to send the next line that has the comment field back one row to this field and their is not a way to reference it.

I am thinking someone has dealt with it before and I can modify their code.

You have two options.

1. Use Orna's solution. It does the job actually as you asked for in your first post. It would involve you writing some VBA code.

2. Write a free standing program to edit your data file and merge records where required.

Unless you are lucky you are going to have to provide your own fix for this issue.

Try searchig this forum and the web for possible solutions.

 

[slynch401k]

OK, here's the solution. Since each of the rows that I need start with a number, then basically I need to start a new row at each row where the row starts with a number.

This works. On to the next issue. Guy's - thanks for the replies.

TIA

Steve

Public Sub ParseFile()

Dim InFile As String
Dim OutFile As String
Dim bDoPrint As Boolean: bDoPrint = False
Dim sInString As String: sInString = ""
Dim sOutString As String: sOutString = ""

InFile = "C:\test.txt"
OutFile = "C:\testOutput.txt"

Dim iFile As Integer: iFile = FreeFile
Open InFile For Input Access Read Shared As #iFile

Dim oFile As Integer: oFile = FreeFile
Open OutFile For Output Access Write Lock Read Write As #oFile

Do Until EOF(iFile)
Line Input #iFile, sInString

If OutputLine(sInString) And bDoPrint Then
Print #oFile, sOutString
sOutString = ""
End If

bDoPrint = True

'Use one of the two following statements
'depending on whether or not you need to
'add a comma between the strings.
sOutString = sOutString & "," & sInString
'sOutString = sOutString & sInString
Loop

If bDoPrint Then Print #oFile, sOutString
Close

End Sub

===================================================

'This function basically states what the non unique row starts with

Private Function OutputLine(Text As String) As Boolean
OutputLine = 0 <> InStr(1, "1 2 3 4 5 6 7 8 9 ", Mid(Text, 1, 1), vbTextCompare)
End Function