I am trying to find a workaround for a formatting issue I'm having. I
download data from a financial system that comes out as an ASCII fixed format
file that we save as a .txt file in notepad. We then use an import macro in
an Access db to upload this financial info to our db. So, here's my
question, the method with which we download will be going away, to be
replaced by a web-based query that delivers the information either as an
Excel file or a CSV text file. I need to make the data it delivers look
exactly like the formatted data I get from the financial system. Is there
any way to somehow capture the exact formatting from the download and apply
that to the future data we'll be getting via query? If not, any other
suggestions other than re-writing the Access import macro?

CSV IS a particular form of text format...

It means Comma Separated Values where in different fields are separated by
commas...

If the CSV output matches the format you get your txt file now, then you
should be good to go... otherwise you might need minor tweaking.

If it is being developed by another group for you then you can ask them to
give it in the same format as you have now... you will just need to change
the extension from CSV to TXT then...



"Decembersonata" wrote:

I am trying to find a workaround for a formatting issue I'm having. I
download data from a financial system that comes out as an ASCII fixed format
file that we save as a .txt file in notepad. We then use an import macro in
an Access db to upload this financial info to our db. So, here's my
question, the method with which we download will be going away, to be
replaced by a web-based query that delivers the information either as an
Excel file or a CSV text file. I need to make the data it delivers look
exactly like the formatted data I get from the financial system. Is there
any way to somehow capture the exact formatting from the download and apply
that to the future data we'll be getting via query? If not, any other
suggestions other than re-writing the Access import macro?

I don't speak Queries, but after the data is in Excel, you have a few ways that
you could save the fixed width text file.

Saved from a previous post:

There's a limit of 240 characters per line when you save as .prn files. So if
your data wouldn't create a record that was longer than 240 characters, you can
save the file as .prn.

I like to use a fixed width font (courier new) and adjust the column widths
manually. But this can take a while to get it perfect. (Save it, check the
output in a text editor, back to excel, adjust, save, and recheck in that text
editor. Lather, rinse, and repeat!)

Alternatively, you could concatenate the cell values into another column:

=LEFT(A1&REPT(" ",5),5) & LEFT(B1&REPT(" ",4),4) & TEXT(C1,"000,000.00")

(You'll have to modify it to match what you want.)

Drag it down the column to get all that fixed width stuff.

Then I'd copy and paste to notepad and save from there. Once I figured out that
ugly formula, I kept it and just unhide that column when I wanted to export the
data.

If that doesn't work for you, maybe you could do it with a macro.

Here's a link that provides a macro:
http://google.com/groups?threadm=015...0a% 40phx.gbl

Decembersonata wrote:

I am trying to find a workaround for a formatting issue I'm having. I
download data from a financial system that comes out as an ASCII fixed format
file that we save as a .txt file in notepad. We then use an import macro in
an Access db to upload this financial info to our db. So, here's my
question, the method with which we download will be going away, to be
replaced by a web-based query that delivers the information either as an
Excel file or a CSV text file. I need to make the data it delivers look
exactly like the formatted data I get from the financial system. Is there
any way to somehow capture the exact formatting from the download and apply
that to the future data we'll be getting via query? If not, any other
suggestions other than re-writing the Access import macro?

--

Dave Peterson