Reformating a file

J

Jack

I am trying to use ACCESS to reformat a file. I will try to make explain it
as best as I can with an example below. Because the ACCT number doesn't
display on every record, I am having a difficult time trying to get it to
look like my desired OUTPUT. Any suggestions?

Input:

Key ACCT YEAR
01 J123
02 1999
03 2001
04 J234
05 1997
06 1997
07 1998
08 J345
09 1999
10 2000

My desired output is below:

Key ACCT YEAR
01 J123 2001
02 J234 1998
03 J345 2000

Thanks,
Jack
 
A

Andi Mayer

I am trying to use ACCESS to reformat a file. I will try to make explain it
as best as I can with an example below. Because the ACCT number doesn't
display on every record, I am having a difficult time trying to get it to
look like my desired OUTPUT. Any suggestions?

Input:

Key ACCT YEAR
01 J123
02 1999
03 2001
04 J234
05 1997
06 1997
07 1998
08 J345
09 1999
10 2000

My desired output is below:

Key ACCT YEAR
01 J123 2001
02 J234 1998
03 J345 2000
Click to expand...

What's the "a File"? what is the delimiter ?

Assumung you are talking about a txt file.

read it into an Array
split every line into another Array
if the ACCT is empty then
leave the the output line and change only the YEAR
else
make a new output line
 
J

Jack

Yes it is a text file and it not delimited at all. This is a one-time file
reformat I am trying to do. I have never created an array in access before.
Any suggestions on how this is done or where I might find some examples on
how this is done? I am assuming it needs to be done in VB?
 
A

Andi Mayer

Yes it is a text file and it not delimited at all. This is a one-time file
reformat I am trying to do. I have never created an array in access before.
Any suggestions on how this is done or where I might find some examples on
how this is done? I am assuming it needs to be done in VB?
Click to expand...

for a one-timer I would suggest to open it in notepad

replace the blanks with ";"
Attention:
first the empty ACCT (8 to 10 blanks)
then the two blanks
01;J123
02 ;;1999
03 ;; 2001
04 J234
05 ;;1997
06 ;;1997
07 ;; 1998

this gives you a nice delimited file

now try to import it in access or Excel

BTW: How big is the file?
I ask because I think my explanations will take me more time that to
change it for you (I have written a programm to import pricelists in
various formats and could "missuse" it for this file")
 
J

Jack

The text file is 1.7 million records.

Andi Mayer said:
for a one-timer I would suggest to open it in notepad

replace the blanks with ";"
Attention:
first the empty ACCT (8 to 10 blanks)
then the two blanks
01;J123
02 ;;1999
03 ;; 2001
04 J234
05 ;;1997
06 ;;1997
07 ;; 1998

this gives you a nice delimited file

now try to import it in access or Excel

BTW: How big is the file?
I ask because I think my explanations will take me more time that to
change it for you (I have written a programm to import pricelists in
various formats and could "missuse" it for this file")
Click to expand...
 
A

Andi Mayer

The text file is 1.7 million records.
Click to expand...
zip it and send it to me, dont forget the to write "manfred" in the
first line, otherwise this mail never reaches me
 
Top