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.

 

[John1Chr]

Ok...Strike one

Generally, I want the fields in the source to be the same as the in the database. I have not created any tables in the database, yet.

FEDERAL PROJECT NUMBER = FEDERAL PROJECT NUMBER (I can grab either the top or the maps project one :they should be the same.)
MAPS PROJECT = MAPS PROJECT
STATUS = PROJECT STATUS
AGPR PURGE IND = AGPR PURGE IND
RESP ORG = PROJECT ORG
PROJECT TYP = PROJECT TYPE
LAST ACTION = LAST ACTION
MAPS PROJECT/SUBPROJECT/PHASE = MAPS PROJ SUBPROJ PHASE
FED PURGE IND = FED PURGE IND
MAPS PROJ PRG IND = MAPS PROJ PRG IND

The fields for the related job numbers if the maps project in the header has any are:

JOB NUMBER = JOB NUMBER
JOB P I = JOB 1 PURGE INDICATOR
RESP ORGN = JOB ORGN NUMBER
START DATE = START DATE
END DATE = END DATE
JOB STAT = JOB STATUS
JOB 3 P I = JOB 3 PURGE INDICATOR
JOB DESCRIPTION = JOB DESCRIPTION
J/P TYPE = JOB TYPE
STATE PROJECT = STATE PROJECT
DOCUMENT ID AGCY NUMBER LINE = DOC AGCY NBR
REMAINING AMOUNT = REMAINING AMOUNT

The other header detail on the top is not as critical.

Aceman, Yeah, no thumb on that one!

 

[SkipVought]

"I have not created any tables in the database, yet."

Good. Then you can build a sheet or sheets with the data for each table (per the above setup process) and then just import into Access.

Skip,

_[red][/red]

 

[CautionMP]

BrGenCAJ,
Try the routine I posted, unless there are format differences between the real file and the sample file it should be 95% correct.

Once the routine is run (1,200 pages should take about 3 minutes) you should be able to open the output file ([tt]C:\header_text_norm.txt[/tt]) in either Excel or Access and use the text file Wizard to visually break down the file and see if it produces what you expect to see.

If it doesn't work you can circle back to the approach Skip is walking you through (sorry Skip, it just seems like this is being made harder than it needs to be since the file can be so quickly normalized in a temp file and then the wizard can take care of breaking the file up into fields).

CMP

 

[John1Chr]

Thanks! I will try that.

I told Skip earlier that I was focusing intially on using excel and he was guiding me through my more manual way of doing it. I feel that I kinda steered him that way. I'm sure that it'll work.