Multi-Line txt files 1

[dpedley]

Anybody have any ideas on how I would go about importing multi-line txt files into a table? One of our systems here exports in .rpt format which is basically .txt files. Each field is fixed delimited, but the data is in multi-line format with a --> connecting the items which go together and a paragraph mark separating new records. I have been tasked with figuring out a way to import this directly into access without using any third party applications.

 

[CautionMP]

dpedley,
IMHO the easiest method is to normalize the file (all the record fields on the same line) in VBA then use the Import Wizard to get the data into your table.

A routine something like this should normalize the file so that it can be imported using the built in Access tools. Notice the input and output file name/locations in bold:

Sub NormalizeFile()
Const cContinuation As String = "-->"
Dim intFileIn As Integer, intFileOut As Integer
Dim strBuffer As String, strRecord As String
intFileIn = FreeFile
Open "[b]C:\SourceFile.rpt[/b]" For Input As #intFileIn
intFileOut = FreeFile
Open "[b]C:\DestinationFile.txt[/b]" For Output As #intFileOut
Do
  Line Input #intFileIn, strBuffer
  If InStr(1, strBuffer, cContinuation) = 0 Then
    Print #intFileOut, strRecord & strBuffer
    strRecord = ""
  Else
    strRecord = strRecord & Replace(strBuffer, cContinuation, "")
  End If
Loop Until EOF(intFileIn)
Reset
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[dhookom]

It would help to see some sample lines from the text file. If each record contains exactly the same number of lines, I import the text file into a table with an autonumber and then extract the various columns from records using a crosstab query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dpedley]

Thank you both for your suggestions. Here is what has occured. Essentially both methods failed for the same reason. Access does not see .rpt as a valid import extension. When I convert to .txt I can't determine the paragraph mark code to include it in the Const cContinuation or to separate the lines in a table import. Also part of this task is to not convert the data prior to bringing it in. Honestly I don't see how that is possible. Here are a couple truncated versions of the type of data the rpt files contain.

000020707413 Unassigned
-->Key Fob/Passcode/SID 01/13/2004 05:00:00
000020707415 Marisa Tjerandsen Enabled
-->Key Fob/Passcode/SID 01/13/2004 05:00:00
000020707416 Brian Johnson Enabled
-->Key Fob/Passcode/SID 01/13/2004 05:00:00

 

[dpedley]

Example two

Mercedes Abad JP010691
-> 000025711940 01/01/1986 00:00:00
-> 000031573533 06/22/2007 20:57:37
Samuel B Abrams JP006522
-> 000031573437 01/01/1986 00:00:00
Paula Abramson JP001977
-> 000039286826 06/23/2007 14:31:32

 

[dhookom]

How do you want to see this in a final table? Is there a paragraph mark at the end of each line (to the left of ->)?

You used both --> and ->. Is the -> a typo?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[CautionMP]

dpedley,
I assumed the wrong format:
[tt]000020707413 Unassigned -->
Key Fob/Passcode/SID 01/13/2004 05:00:00
000020707415 Marisa Tjerandsen Enabled [blue] [/blue]-->
...[/tt]

Try this for the first format. When you use the [tt]Line Input #[/tt] method you don't need to worry about the line contnuation character ([tt]vbCrLf[/tt] in Windows) since it stops reading the input file into the variable when it encounters this character.

...part of this task is to not convert the data prior to bringing it in.

When I tested this the second record didn't come out right, there seemed to be more padding (spaces) at the end of the 3rd line (see underlined text above). I don't know if this is the data or a copy/paste error.

Sub NormalizeFile()
Const cContinuation As String = "-->"
Dim intFileIn As Integer, intFileOut As Integer
Dim strBuffer As String, strRecord As String
intFileIn = FreeFile
Open "C:\SourceFile.rpt" For Input As #intFileIn
intFileOut = FreeFile
Open "C:\DestinationFile.txt" For Output As #intFileOut
Do
  Line Input #intFileIn, strBuffer
  If InStr(1, strBuffer, cContinuation) [red][b]<>[/b][/red] 0 Then
    Print #intFileOut, strRecord & strBuffer
    strRecord = ""
  Else
    strRecord = strRecord & strBuffer
  End If
Loop Until EOF(intFileIn)
Reset
End Sub

If this works a variant of the code could be used for the second format.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[dpedley]

Reply to dhookum;

Q1) goal would be to have the data in the first example come in with the code, assignment, status, type and date/time in a single record. (There are more fields, but space limitation here made the data confusing so I truncated it to a smaller sample.) The same would be true for the second example except the name and JP number would have to appear in both records listed below them.

Second question Hmm. It depends, and this leads to the other question. The rpt format I can't see it, and the data is ->. When converted to txt still can't see P-mark, but can tell its there. Data appears -->.

 

[dpedley]

reply to Caution MP.

I think this was a cut and past error as I truncated the text as described in the response to Dhookum above. Also when I use Open "C:Sourcefile.rpt"... When it gets to this line it fails and says it can't find the file. I simply changed the name to ???.txt and it could then read, but the data didn't separate in records correctly.

 

[CautionMP]

...but the data didn't separate in records correctly.

[ul][li]What is not correct?[/li]
[li]When you use the Import Wizard does the file [tt]C:\DestinationFile.txt[/tt] look close?[/li]
[li]If you look at this file in Notepad or a similar text editor do you think the data from the trucated data is causing the issue?[/li][/ul]

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[dpedley]

There is some code a the end of each line. (When I view this in Word it is a paragraph mark, but I'm not entirely sure since I can't see any code in its original format.) Anyway your code works great (mostly) at finding the --> which separates the two pieces in the first example. But I end up with one long string. The date/time and id aren't separating.

No the file comes in stacked and can't separate correctly.

No issue with trucated data. I did that to get it to fit this space.

 

[dpedley]

The second type of data has a potential of having more than one line per entity and each some fields may need to be included in more than one record. (ie: below Mercedes Abad and the rest of the data on the first line would need to show on both the 25711940 and 31573533 records.)
Also there is much more data than is listed here. I tried to attach copies of files, but I couldn't find an option to attach files. Does it exist? These mini versions are very good and seeing the actual data.
Mercedes Abad JP010691
-> 000025711940 01/01/1986 00:00:00
-> 000031573533 06/22/2007 20:57:37
Samuel B Abrams JP006522
-> 000031573437 01/01/1986 00:00:00
Paula Abramson JP001977
-> 000039286826 06/23/2007 14:31:32

 

[CautionMP]

dpedley,
It should be "one long string", when you run the Import/Link Wizard you should select Fixed Width then follow the steps to define the fields in the file.

Here is a routine for the second example. Same thing you will need to change the filenames (in bold) and all the routine does is reformat the file so you can use the standard Access Wizard to link/import the data into your database.

Sub NormalizeExample2()
Const cContinuation As String = "->"
Dim intFileIn As Integer, intFileOut As Integer
Dim strBuffer As String, strKey As String
intFileIn = FreeFile
Open "[b]C:\Example2.rpt[/b]" For Input As #intFileIn
intFileOut = FreeFile
Open "[b]C:\Example2.txt[/b]" For Output As #intFileOut
Do
  Line Input #intFileIn, strBuffer
  If InStr(1, strBuffer, cContinuation) = 0 Then
    strKey = strBuffer
  Else
    Print #intFileOut, strKey & strBuffer
  End If
Loop Until EOF(intFileIn)
Reset
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[dpedley]

Thanks so much, both of these are working great.