Help formating XLS to ASCII file for IRS 1099 submission

Our Finance department needs to submit tax information electronically.  I need to convert spreadsheet data into an ASCII file.  The ASCII file will string together multiple records, but each must be exactly 750 characters long.  The first character of each record will identify the type of information to follow, which of course must conform to standards specified.

For example, one type of record requires data in following positions: 1, 2-9,10-30,31-49,50-57,58-499,500-507,508-748,749-750.  

I've been provided an xls spreadsheet containing the required information.  I expect to export each sheet within the workbook to a simple text or csv format, but the trouble I'm having is maintaining data in the format required.  I need a way to merge each row of data while conforming to location within the record.

So essentially I need a practical method to append all records together so the file can be submitted.

Any ideas?  Unfortunately I need this soon. Isn't always when you're swamped that other stuff comes at you out of the blue?  Any help would be appreciated.

Form is 1099 for electronic submission, IRS publication 1220 (p1220.pdf) describes it.



 

safebetAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
write a simple vba macro to append each cell into the desired position by padding/trimming the data
in the actual cell ...

then write the results to a txt file...


you could even record the format / position information into another worksheet and use that to controll the
record formatting...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
safebetAuthor Commented:
Thanks, that was ultimately the approach taken - however I enlisted the help of someone more VBA proficient than myself.  But I appreciate the quick response and am sure if I had time to struggle through the process myself you probably would have assisted.  

Grade "A"!  

Appreciate the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Tax / Financial Software

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.