txt file with chaotic header, etc

[John1Chr]

Hi all,

Yes, I'm trying to import a rather chaotic but systematic type text file into an access table with fields and records. I really wanted to get this thing off the ground b4 I applied but I haven't been able to. The forum that I posted it in is

http://www.dbforums.com/showthread.php?t=1619435.

I couldn't find anything similar that was done in this forum or the other.

 

[TheAceMan1]

How are ya BrGenCAJ . . .

Without some [blue]sample data[/blue] what can anyone do?

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[John1Chr]

Aceman1,

It's nice to talk to a programming wizard.

I have a sample of the text file at that website that I provided above.

 

[SkipVought]

So what VBA code do you have so far?

Where are you stuck?

Skip,

_[red][/red]

 

[SkipVought]

Do you also have the Table columns that you are maping into? I'd build a table that describes the mapping and categorizes the types of rows in the source txt file.

Skip,

_[red][/red]

 

[SkipVought]

I think that these are your types of records...
[tt]
H1 REPORT ID: FPRG
H2 RUN DATE: 02/24/2007
H3 RUN TIME: 04:20:57
X
X
H4 SORT ORDER: AGENCY, FEDERAL PROJECT NUMBER, MAPS P
H5 AGENCY: T79 TRANSPORTATION DEPT PUR
X
H6 JOB JOB RESP START END JOB JOB3
H7 NUMBER P I ORGN DATE DATE STAT P I
X -------- --- ---- ------ ------ ---- ---- -------
X
D1 FEDERAL PROJECT NUMBER: ARCH001
X
D2 MAPS PROJECT: CVBEX CONVERSION
D3 MAPS PROJECT/SUBPROJECT/PHASE: CVBEX04C FE
X 0
D4 T53730A P 2130 070194 102000 C Y PEDEST
[/tt]
where Hn records are headings and Dn are detail data.

Each type can be parsed in a similar way for mapping.

Skip,

_[red][/red]

 

[John1Chr]

Skip,

Yes, Federal Project Number is the field name and ARCH001 is the detail Maps Project is the field name and CVBEX is the detail and Maps Project/Subproject/phase is the field name and the detail is CVBEX04C and status is the field and the detail is C and AGPR Purge ind is the field and Y is the detail resp orgn is the field and 5000 is the detail and on and on.....for the header.

In addition, the detail for the job number header is job number from the top and T53730A is the detail for that field and job P I is the header and P is the detail....and each Maps project could have more than one job number.....it goes on like that.


This report is set up kinda goofy but maybe there is a way to import it with VB somewhat smooth?

Is this hard to pull

 

[SkipVought]

This is not necessarily a goofy report. It is par for the course. I have seen worse. But it is a pain in the posterior.

So what VBA code do you have so far?

Where are you stuck?


Skip,

_[red][/red]

 

[John1Chr]

Skip,

I haven't written any code for it yet...was kinda hoping that something similiar would be out in cyberspace and maybe I could tweak it. I'm a novice with code. I float better with SQL. Actually, I imported it into excel and thought that maybe I could sort it out and maybe isolate the clutter. It would be possible if it weren't for that bleepin' maps project header thingy

 

[SkipVought]

"...was kinda hoping that something similiar would be out in cyberspace ..."

General!!! Really???? Who's army are you in???

OK. I assume that ALL the data is in Column A

1. In Excel number the lines sequentially top to bottom. in column B

2. Sort on column A

Delete ALL header columns except 1 set of H6 & H7

Now you have D1 thru D1 records and one set of headers. Hows that for a start?

Skip,

_[red][/red]

 

[John1Chr]

Honestly, I feel like I'm doing latrine duty with a tootbrush.

Yeah, I remember software that I used in a previous job it was called Monarch were you could just highlight the areas in a report and it would dump into excel real easy.

I actually tried separating out the field but I lose the relation between the maps project and the job number. Not sure I totally understand what you are saying...can we do that with 1,200 pages?

I might try writing a function or something tomorrow and try to get it off the ground. Maybe, something that reads the spaces and loops through and keeps the relation from the header with the detail. Not sure, right now...maybe after PT in the morning it will become clearer?

 

[SkipVought]

"...the maps project ..."

has nothing to do with mapping the data from your report to your database table, except it happens to be one of the pieces of data that you must map!

Have you mapped your data?

for instance...
[tt]
Rec No Field Name From Thru TableField
D1 Fed Proj Nbr 15 50
D2 Maps Proj
D2 Status
D2 AGPR PURGE IND
D2 RESP ORGN
D2 PROJ TYP
D2 TH LAST ACTION
D3 Maps Proj/Sub
.......
[/tt]
Each Record Type has a FIXED FORMAT. So for that type of record, the From Thru column will be the same. You can use that data for parsing the record.

The logic would be something like this...

Select Case Left(AnyRecord,8)
  Case "  JOB   "   'D1 record
    'now here's where you would parse this type of rocord and map the data accordingly to your database.
  Case " NUMBER "   'D2 record

  Case "FEDERAL "   'D3 record

  Case "MAPS PRO"   'D4 record

  Case "    MAPS"   'D5 record

End Select

Of course, this would be within a loop, reading each row in your sheet.

Put that tooth brush away, and get to the heavy lifting, SIR!

Skip,

_[red][/red]

 

[Remou]

Some code, but mainly for my amusement, I doubt if it will give you the the confidence that the semi-manual conversion suggested by SkipVought will. In addition, all records must be similar to the sample records - I have not allowed for any errors. However, you may wish to consider a similar set-up for future use.

Sub ImportFile()
Dim fs As FileSystemObject
Dim fin As TextStream 'Object
Dim fout As TextStream 'Object

Set fs = CreateObject("Scripting.FileSystemObject")

strFileIn = "C:\Docs\header_text.txt"
strFileOut = "C:\Docs\Header_Text_Import.txt"

Set fin = fs.OpenTextFile(strFileIn, ForReading)
Set fout = fs.CreateTextFile(strFileOut, True) 'Overwrite

Do While fin.AtEndOfStream <> True

lin = fin.ReadLine

If Trim(lin) Like "MAPS PROJECT:*" Then
'Map project line
    strLineOut1 = lin
End If

If Trim(lin) Like "MAPS PROJECT/SUBPROJECT*" Then
'Map subproject line
    If intC > 0 Then
        'Write line if no detail
        fout.WriteLine strLineOut1 & strLineOut2
    End If
    'Save line for detail check
    strLineOut2 = lin
    intC = intC + 1
End If

If Trim(lin) Like "T####*" Then
    'Write detail line
    fout.WriteLine strLineOut1 & strLineOut2 & lin
    intC = 0
End If

Loop

'No final detail line, so write subproject line
If intC > 0 Then
    fout.WriteLine strLineOut1 & strLineOut2
End If

fout.Close
fin.Close
Set fin = Nothing
Set fout = Nothing
Set fs = Nothing
End Sub

 

[CautionMP]

BrGenCAJ,
I started down the same path as Remou, this routine simply takes a file in the format you provided ([tt]C:\header_text.txt[/tt]) and creates a second file ([tt]C:\header_text_norm.txt[/tt]) that is in a normalized format that you can then link to/import using the appropriate Access wizard.

Before you run make sure the files listed in bold are changed to match your real environment.

[green]'Note: The wildcard (*) at the end of each string is for the Like statement[/green]
[navy]Const[/navy] gstrPagHea [navy]As String[/navy] = "*REPORT ID:*"
[navy]Const[/navy] gstrFedProNum [navy]As String[/navy] = "FEDERAL PROJECT NUMBER:*"
[navy]Const[/navy] gstrMapPro [navy]As String[/navy] = "MAPS PROJECT:*"
[navy]Const[/navy] gstrMapProSub [navy]As String[/navy] = "    MAPS PROJECT/SUBPROJECT/PHASE:*"

[navy]Public Sub[/navy] Normalize_File()
[navy]On Error GoTo[/navy] Normalize_File_Error
[navy]Dim[/navy] blnCapture [navy]As Boolean[/navy]
[navy]Dim[/navy] lngFileIn [navy]As[/navy] Long, lngFileOut [navy]As Long[/navy]
[navy]Dim[/navy] strFedProNum [navy]As[/navy] String, strMapPro [navy]As[/navy] String, strMapProSub [navy]As String[/navy]
[navy]Dim[/navy] strBuffer [navy]As String[/navy]

lngFileIn = FreeFile
[navy]Open[/navy] [b]"C:\header_text.txt"[/b] [navy]For Input As[/navy] #lngFileIn
lngFileOut = FreeFile
[navy]Open[/navy] [b]"C:\header_text_norm.txt"[/b] [navy]For Output As[/navy] #lngFileOut
[navy]Do[/navy]
  [navy]Line Input[/navy] #lngFileIn, strBuffer
  [navy]If[/navy] Trim(strBuffer) = "" [navy]Then[/navy]
    [green]'Ignore blank line[/green]
  [navy]ElseIf[/navy] strBuffer [navy]Like[/navy] gstrPagHea [navy]Then[/navy]
    blnCapture = [navy]False[/navy]
  [navy]ElseIf[/navy] strBuffer [navy]Like[/navy] gstrFedProNum [navy]Then[/navy]
    blnCapture = [navy]False[/navy]
    strFedProNum = strBuffer
  [navy]ElseIf[/navy] strBuffer [navy]Like[/navy] gstrMapPro [navy]Then[/navy]
    blnCapture = [navy]False[/navy]
    strMapPro = strBuffer
  [navy]ElseIf[/navy] strBuffer [navy]Like[/navy] gstrMapProSub [navy]Then[/navy]
    blnCapture = [navy]True[/navy]
    strMapProSub = strBuffer
  [navy]ElseIf[/navy] blnCapture = [navy]True Then[/navy]
    [green]'This is where the data is written to the output file[/green]
    [navy]Print[/navy] #lngFileOut, strFedProNum & strMapPro & strMapProSub & strBuffer
  [navy]End If[/navy]
[navy]Loop Until[/navy] EOF(lngFileIn)

Normalize_File_Exit:
Reset
[navy]Exit Sub[/navy]

Normalize_File_Error:
[navy]Stop[/navy]
[navy]End Sub[/navy]

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)

 

[SkipVought]

Remou,

"...Actually, I imported it into excel ..."

Assuming ALL the data is in Column A beginning with A1 in Sheet1

Dim r as range
for each r in Sheet1.Range(Sheet1.Cells(1, "A"), Sheet1.Cells(Sheet1.UsedRange.rows.count, "A"))
Select Case Left(r.value,8)
  Case "  JOB   "   'D1 record
    'now here's where you would parse this type of rocord and map the data accordingly to your database.
  Case " NUMBER "   'D2 record

  Case "FEDERAL "   'D3 record

  Case "MAPS PRO"   'D4 record

  Case "    MAPS"   'D5 record

End Select
Next

Skip,

_[red][/red]

 

[John1Chr]

I'm impressed by the varity of answers here. I'll get back on this one asap. Celebrating father's day right now. I like the "mapping out" that Skip outlined and that's the one I'm going to try first. Remou's will loop right after the job number so that should coordinate with the right related Maps numbers I'm thinking. I'll try them all, though.

 

[SkipVought]

Since you're not a code guru, I'm giving you a solution that you can walk thru and code most of it yourself using the macro recorder in Excel.

We will loop thru the sheet, per my posted code and then parse each record type like this example setup...

SETUP:
The easiest ones will be the D4 type records. COPY H6, H7 and the following X rowes. Paste in A1 on Sheet2.

Turn on your macro recorder and then Data > Text to columns... FIXED WIDTH. The parsing manager should mark the natural divisions. If any of these divisions are not to your liking, adjust it. Finish and turn off the macro recorder. Ths is 90% of the code you will need to get the data from your report into this table format.

This report logically should map into more than one table, just looking at the data relationship. Still waiting for the MAPPING, as this will dictate how the parsing design should proceed.

Skip,

_[red][/red]

 

[John1Chr]

Skip,

I believe that this is what you were asking for. I hope I got it all right the first time..if not I'm thinking I can go back and tweak it or retry very easily. One thing, not every maps project will have a job number and many of the maps projects can have more than one of their own related job numbers.

mapping

For the maps Project
federal project number 25 - 31 it is always 7 characters

maps federal project number 15 - 19 always 5 characters not sure what that conversion is behind it but I know by the CV that it is a conversion we can drop the word conversion

Status 61 it is always one character only either a C for close or an O for open

agpr purge ind 80 it is always one character Y for Yes or N for No

resp org 93 - 96 always is 4 characters

proj typ 108, 109 always 2 characters

MAPS PROJECT/SUBPROJECT/PHASE 36 - 43 always 8 characters

federal project number 73 - 80 space between is not necessary actually it is always 7 characters

FED PURGE IND 98 always 1 character

MAPS PROJ PRG IND 120 always 1 character



For the job detail
Job number: 1 - 8 typically 5 character but can vary

job p i: 10 i think it is always 1 character

resp org 13 - 16 always 4 character

start date 19 - 24 always a date format

end date 26 - 31 ditto - like above

job stat 33 always 1 c for close and o for open

job 3 pi 38 always 1
job description 43 - 75 this one varies

jp type 76 - 77 always 2 character, I beleive

state project number 78 - 88 this one is never greater than 10, I believe but it can vary and be less than 10

id document agency number 95 - 115 can vary usually empty

remaining amount 117 - 129 Or the end

 

[SkipVought]

That is FORMAT.

Mapping is a relationship between the source (format) and destination, in your case a database/table(s)

Skip,

_[red][/red]

 

[TheAceMan1]

BrGenCAJ . . .

[blue]Nice Job![/blue] . . . deserves a [fuchsia]pinky! . . .[/fuchsia]

See Ya! . . . . . .

Be sure to see FAQ219-2884: