Data Import Template: FAQs

Click here for the link to the Data Import Templates ZIP file.

Can I add extra tabs?

Yes, you can add tabs to the spreadsheet file. Please note that what is on those tabs will not be imported into the program.

Can I change the file name?

Yes, you can change the name of the Data Import Template prior to import. However, if you save the file as a *.xlsx file and do not save it as a *.xlsm (macro) file, then the macros are not saved and the drop-downs are not available.

Can I save the template to an earlier Excel version such as an XLS?

No, the earlier file types are not compatible for import.

Is it acceptable to copy and paste into tab TR_XXXX_TemplateName_Detail?

Yes, values will be imported. However, the first column must stay as the first column (see details above).

Is it acceptable to change the column headings and formatting in tab TR_XXXX_TemplateName_Detail?

No, this will prevent an import from occurring. You need to leave the existing column headings be and also must not change the column formatting. One example is a date formatting; this must remain a date and must not be changed to general.

Can I change the formulas in the spreadsheet?

Yes, you can change the formula to meet your needs. We have added formulas to the template file to assist with data entry.

Can I add columns to the spreadsheet on the TR_XXXX_TemplateName_Detail tab?

Yes. Only the first column must remain as the first column (see details above).

If I want to copy and paste, I will not be using the macros. Is that acceptable?

Yes. However, make sure you paste the correct values when required. See tab TR_XXXX_TemplateName_Strings for the values that must be used.

Will the import create a new locator?

No, the locator must already exist for the tax year being imported. That existing locator number will be entered on the TR_Setup tab.

Can I use an existing spreadsheet as the starting point?

Yes, the setup may take some time. As an alternative, take your existing spreadsheets and insert the tabs into the template file. This will save you setup time. You can use formulas in Excel to transfer and/or concatenate the data from your existing format.

Are special characters allowed when using the data import templates?

No, special characters are not allowed. On export, an Excel file is created. If any of the following characters are in an activity name, we will modify the name of the exported file. These characters will be replaced by a hyphen in the file name.

File name layout: Locator_Activity Type_Activity Number_Activity Name_Template Name. XLSM

Examples:

A123456Y_Rental Real Estate-(SchE)_101_3344 Jefferson #2_Asset.XLSM
A123456Y_Rental Real Estate-(SchE)_101_3344-Jefferson—2_Asset.XLSM

(Space and # replaced by two hyphens)

If there is data in the return other than the activity names with the special characters, an export has a good chance of failing. Please make sure not to use the following characters.

Special characters that are not allowed are:

# Pound
% Percent
& Ampersand
{ Left bracket
} Right bracket
\ Back slash
< Left angle bracket
> Right angle bracket
* Asterisk
. Period
? Question mark
/ Forward slash
$ dollar sign
! Exclamation point
‘ Single quotes
“ Double quotes
: Colon
@ At sign
Blank spaces

Can I have a carriage return in the import?

No, carriage returns will not work.

How do blanks import?

They do not import. If you have a value in the tax return that needs to be removed or set to a blank, please change it in the tax return.

Can I use NONE for an amount field?

No, NONE will not be imported into an amount field.

Why didn’t the import finish?

There can be various reasons why an import was not performed. Select the Batch Status button, and then select details for the particular batch. The import status can be one of the following when issues exist:

locator busy, try again (a user has the return open while the import is queued)
Import Failed, Duplicate Important Field Values On XYZ Sheet (XYZ Sheet is the name of the template type containing the problem in the import file). There will be more than one identical name in the required column on the Detail tab and at least another instance on the Group or Group2 tabs. As there are at least two identically named on the Detail tab, the program does not know how to match up the same on the Group/Group2 tab. The workaround is for the user to provide unique names on the Detail tab, or the user can remove the data on the Group/Group2 tabs if unique names on the Detail tab are not possible.
invalid or missing required fields (Asset Description or Account ID, for example, is missing or invalid). A case of an invalid character in the asset description is any of the following:
- # Pound
- % Percent
- & Ampersand
- { Left bracket
- } Right bracket
- \ Back slash
- < Left angle bracket
- > Right angle bracket
- * Asterisk
- . Period
- ? Question mark
- / Forward slash
- $ dollar sign
- ! Exclamation point
- ‘ Single quotes
- “ Double quotes
- : Colon
- @ At sign
invalid setup properties (setup tab has invalid data)
parsing failed (required data missing on setup tab; missing firm ID or account number)
import complete with errors (for example, data type did not match field or data length was longer than field length)
no locators parsed (import file did not contain locators)
Import failure with a reason for the failure (wrong locator in import file, missing required values, etc.).
- Wrong locator means there is a mismatch between the template and the batch system.
- The locator number, firm, and account number must be in upper case if they contain alpha characters.
- The status details will mention Duplicate Important Field Values. This can affect the broker stmt, capital gains, K1, Schedules C & E, or PFIC templates. If there is a duplicate description on the detail tab and there is data on the group tab, this will occur. An example of this is the Name 2 with the S combination in this picture. This happens because we cannot tie out the group tab detail to the detail tab with multiple duplicate rows on the detail tab. If the ownership on the second Name 2 was a T, then it would import.
  The pseudocode behind this is as follows:
  If group tab exists and contains data,
  
    If on detail tab duplicates exist,
  
      Then do not process.
  
  If on detail tab duplicates do not exist,
  
      Then process and import.
  If a large batch of returns is submitted, intermediate statuses will be displayed.
- Required Data on Sch_K_1_Group Detail Sheet Not Defined in Sch_K_1 Sheet. This occurs on the Schedule K-1 templates. Something does not match between the group and detail sheets. For example, the ownership, activity, activity number, K-1 Name, or Federal EIN on the group tab does not have a corresponding identical match on the detail tab. Make sure your activities are sorted on the activity number column on both tabs. Users have been found to sort based upon the Sort Order for Proforma column and then lose track of their activities. After losing track of where they were, they enter an incorrect activity number or name.
  If you mismatch these and you have hundreds of K-1s in your template, it will take hours to find the problem.
- Import processes, but you cannot find the assets. This is most likely in an 1120 top consolidated return. Please import into a lower level return that feeds into the top consol. Asset detail is meant to be entered at the parent or subsidiary level.

How do I know the import finished?

The proper status is Data Import Template Import Complete. Besides the error statuses, possible intermediate statuses are:

locator found - begin processing
queued for import
queuing for import batch jobs.

Why isn’t my export finishing?

If you attempt to export data from a tax return, the tax return must be closed. If it is not closed, the export will fail. The batch message for that will be Export Failed – and the name of the template. Also, you could possibly see a message similar to Error: The process cannot access the file because it is being used by another process.

An export could also fail if the data contains invalid characters. See above for the invalid character list.

Another batch message could be Unable to Export. The most likely reason is that the data is invalid for what Excel is going to expect for a given field. In the tax return and related to the asset template, one of these fields is labeled Date placed in service (Print only). If a non-date value is entered in this return field such as VAR 2022 or 01/01/22, the value must be in the following format, such as 06/01/2022. The Excel formatting for that field is a date, and we are preventing a non-date value from being exported. Please change the data to a date in the format xx/xx/xxxx.

Import/Export/data_import_template_faqs.htm/TY2021

Last Modified: 03/20/2020

Last System Build: 10/18/2022