Greeting,

I’m trying to submit a required state report as a text in certain format. I used Unidata to pull the required field as flat file and import the data to Excel 2007 successfully. The file has about 25 column (FName, MI, LName, SSN, DOB etc.)

The state wants, let’s say 5 spaces between FName and MI, and 4 spaces between MI and LName and 4 spaces between LName and SSN ,3 spaces between SSN and DOB etc.

What is the best way to accomplish the formatting?

Thank you in advance.

OCm

In Excel there isn't a "best way" to do this kind of manipulation. Excel isn't really built to do this kind of work, so your approach is like using a toothbrush to adjust your television... It might be possible, but it probably won't be a "best way" in anybody's opinion.

You may be able to do what you've described by creating formulas to construct a column that contains the text formatted to meet the state requirements. Once you get the column correctly built, you'll need to copy the contents of that column into a text editor like Notepad, then save the results. This will do the task you need done, but it won't be pretty!

Depending on how much work you want to do and how much learning you are willing to do there are other options. If you really need to reformat one flat text file into another text file format, then I'd strongly suggest investigating a free tool called GAWK which is a simple tool designed to make mangling text files simple. GAWK doesn't have a GUI, but it is the best fit for the problem you've described (least work to produce exactly what you want). Check out the printf() function and you ought to be on your way.

There are a number of reporting tools that can approximate what you've described. The report writer in MS-Access doesn't work well for me when laying out character based output like a file format, but I know some people that swear by it (while I swear at it).

-PatP

Pat,

Thank you for your quick reply. Per your suggestion, I’ve attempted to download the utility following the link you provided. The file is called ‘UnxUpdates.zip’ and while trying to run it, I see a lot of application with .exe and wasn’t sure:

1. Which file from the list to pick
2. using these tools, how to go about getting the task accomplished

Is there any supporting user manual for it?

Please let me know.

Thanks,

OCM

This suggestion is swinging a bigger hammer to make the "bootstrap process" of getting GAWK, it's documentaiton, and the supporting files loaded onto a machine. This solution is overkill, but it is comparatively easy to do compared to figuring things out from scratch.

If you want the background (explanation) to help understand what these tools are, where they came from, and how very many people have contributed to them you can find more details at GetGnuWin32 – Maintaining a Gnuwin32 Package archive (and this is a good bit of background if you have time to digest it). If you just want to "get the show on the road", go to Automated gnuwin32 download tool - Browse Files at SourceForge.net and commence!

-PatP

Thanks PatP, for your reply & the link you provided was very helpful.

Regards,

OCM