Importing from Microsoft Excel®

Spreadsheet Considerations

 Introduction  Previous Topic  Next Topic

Excel spreadsheets generally contain embedded formatting codes that must be removed prior to being saved as a delimited text file. The remaining topics in this chapter describe how to remove these formatting codes from the 1099MISC_EXAMPLE spreadsheet shown in Figure 1 below.

 

Excel Spread Sheet

 

Figure 1 - Sample Spreadsheet (1099MISC_EXAMPLE.xls)

 

note Notes

 

•Each field of this spreadsheet resides within its own column. Ensure that each field of your spreadsheet resides within its own column as well. If necessary, the Text to Columns selection on Excel's Data menu could be used to partition a single column into distinct columns. For example, if your spreadsheet contains a single column containing CITY, STATE ZIP, Excel's Text to Columns feature could be used repeatedly to separate this column into three distinct columns, | CITY | STATE | ZIP |. 
•The order of the columns is irrelevant since the primary objective of IMU is to map your source file to a text file that is compatible with Account Ability, regardless of column order.
•Row 1 contains descriptions, not actual data. Although not necessary, including descriptions in row 1 will facilitate the mapping procedure. IMU refers to row 1 as the Header record. If you decide to include a header record, make sure that it does not span more than one row.
•This spreadsheet contains only 7 columns (A-G). Clearly, this is less than the number of fields on an actual tax form (e.g. Form 1099-MISC also has boxes for Other Income, Nonemployee Compensation, etc...). It is not necessary to include a column for each field of a tax form since IMU will supply the missing defaults.
•If your spreadsheet contains aggregate rows (e.g. totals, sub-totals, etc.), remove them. You must ensure that each row of your spreadsheet, with the possible exception of row 1, contains recipient information only.
•If you examine the Zip and Tax ID columns in Figure 1 you will notice an invalid Zip code (8701 is missing a leading 0) on row 1 and an invalid Tax ID (98129999 is missing a leading 0) on row 2. Apparently, Excel has dropped the leading zero in both fields. The reason for this is that Excel regards these columns as numeric so leading zeros are insignificant. The next topic describes how to overcome this obstacle.