manipulate file before import

[tagteam]

I have a text (fixed length) I would like to manipulate before I import it. Here is what I would like to do:

• Load a list of values from a table into an array (job codes)
• Read each record in the file and if it doesnt start with a 1 ignore or remove it
• Read each line in the file and look for a specific job code (in the array) that will sit in Chars 163 - 167 and if it is one of the numbers in the array create a new file called import2
• If the job code is not in the array leave it in the original file ( or create new file called import1)

Now for the reason why:
I need to import this fixed length file but always at the end are several records that start with a number other than 1 that I dont want.
In addition, if the record (or line) has a job code that is for wheels then the import specification changes for characters 169 - 222.
I thought about pulling it all in, then using queries to separate the data, then using some kind of string length formula to change the data but the above method seems cleaner.

Does any one have an suggestions on how to do this and / or any recommendations on a better process?


Thanks

[RuralGuy]

I would use VBA and "Line Input" statements.

[ssanfu]

Building on (and agreeing with) what RuralGuy said, there are two methods. I use the 'old school' method; here is a snipped of code:

   '  open error text file - output
   EF = FreeFile
   FileToWrite = CurrentProject.Path & "\ERRORS_" & BaseName & " - ImportHours" & ".txt"
   Me.txtErrFile = FileToWrite
   Open FileToWrite For Output As #EF

This opens a text file for output. The code to open a text file for input is a little different.


The second method is using the "FileSystemObject". I am starting to transition to it.
Here are some links to help you get started.

Main page : http://www.wiseowl.co.uk/blog/s211/textstream.htm
First page : http://www.wiseowl.co.uk/blog/s210/filesystemobject.htm
Second page : http://www.wiseowl.co.uk/blog/s211/writeline.htm
Third page : http://www.wiseowl.co.uk/blog/s211/readline.htm


If you get stuck or have questions, post back...

[orange]

I agree with Allan/RG.

Another approach is to
a) create a table with the structure you want as final result.
b) create a table with 1 field (if record length is <255), use 2 fields if >255 etc
c) use table in b) as your working table
-- delete any records that don't start with 1 to remove the

always at the end are several records that start with a number other than 1 that I dont want.

d) use queries or procedures to parse the data and move it to your "final table" as needed.

I'd still go with "Line Input". Your mentioning of arrays makes me think you might be a Fortran programmer (or familiar with vba). You could put your job codes in a table and work with queries against your working table.

Good luck with your project, let us know how it goes.