Solved

Help formating XLS to ASCII file for IRS 1099 submission

Posted on 2007-03-23
2
1,857 Views
Last Modified: 2013-12-11
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.



 

0
Comment
Question by:safebet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 18781077
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
 

Author Comment

by:safebet
ID: 19102115
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

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question