What controls the format of dates in an import file created by OC-Import?

Background

Office Connector Import does not require you to enter and format date values using any specific format. This is because date values will be automatically re-formatted using the correct format when the import file is created. This provides you with the maximum flexibility in how you wish to have your information displayed while also maintaining compatibility with the import features.

The format of dates in an import file that is required by Sage 300 Construction and Real Estate is determined by a setting that is found under the Tools menu in the Options window. The Date Format selection determines the expected date format for both data-entry as well as importing. The selection made in this window is stored in a file named TS.INI that is located in your Sage 300 Construction and Real Estate data folder. Office Connector Import is designed to automatically apply this format (if possible) when creating the import file.

Entering Dates

Office Connector Import will interpret a value as a date if it was entered as a date value in the worksheet. For example, if you enter a value of 5/31/2012 in a cell, Excel automatically interprets that as a date (based on it matching your Windows Regional Settings) and formats it as a date. This can be checked via the Format Cells window where you will see that a Date format has been applied. Note that date formats proceeded with an asterisk (*) automatically adapt to your Windows Regional Settings. Office Connector Import will also accept text values that contain dates (values formatted as text or entered with a proceeding single-quote). Text values will be interpreted as a date, again, by applying the Windows Regional Settings date format to interpret the value.

Office Connector Import - Date Reformatting

When Office Connector Import creates an import file, it will automatically reformat date values so that they are compatible with the date format required by Sage 300 Construction and Real Estate. To do this, it will first try to use the Date Format selection that is identified in the TS.INI file. For this to work, a Sage 300 Construction and Real Estate data folder must be associated with the workbook. This would normally be accomplished by making a connection to your Sage 300 Construction and Real Estate database via Office Connector Query or Office Connector Write. The associated data folder path is stored by Office Connector Query/Write in a custom property of the workbook and then used by Office Connector Import to determine the location of the TS.INI file.

In the event that no data folder is associated with the workbook, Office Connector Import will use the date format identified in your Windows Regional Settings. Specifically, the Short Date format will be applied. If the Short Date format found in Windows Regional Settings does not match the Date Format in Sage 300 Construction and Real Estate (under Tools / Options), you have two options:

Option 1 - Change the Short Date Format in Windows Regional Settings

You will need to change the date format so that it matches the date format identified in Sage 300 Construction and Real Estate under Tools / Options.

  • Windows Vista and Higher: Click the Start menu, select Control Panel, and then click Clock, Language, and Region. Then click Region and Language.

Option 2 - Manually associate a data folder path with your workbook.

This will tell Office Connector Import where to find the TS.INI file that identifies the correct date format.

  1. Open the Document Properties window of the workbook
    • Excel 2007: Click the Office Button, then click Prepare and then Properties. Then, in the bar (below the ribbon) click the Document Properties drop-down and click Advanced Properties.
    • Excel 2010 and above: Click the File menu and then click Info. Then (toward the right), click the Properties drop-down and click Advanced Properties.
  2. Click the Custom tab.
  3. Check to see if the list of Properties (at the bottom) already contains an entry named TimberlineDataFolder. If so, click on it.
  4. In the Name box, type: TimberlineDataFolder.
  5. In the Value box, type the path of your Sage 300 Construction and Real Estate data folder.
  6. Click Add (or Modify if the entry already existed)
  7. Click OK

NOTE - The JC Estimates import file format does not look to either the TS.INI file or to Windows Regional Settings for the date format. This file format uses a fixed date format that Office Connector Import will automatically use.

Cart

"In today's economic times, it is imperative that we are confident in the information we provide to our company and our customers. To achieve this, we rely on Office Connector. Office Connector enables Excel to instantly gather and write information that would otherwise take hours or even days to assemble."

B. Morse, The Gorman Group