Help formating XLS to ASCII file for IRS 1099 submission
Posted on 2007-03-23
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.