Purpose

This document provides an overview of the Scan Data Template and a set of instructions on how to use and save the template to a file in the proper Core-CT format. It also explains how to import a Physical Inventory extract file into the Template as the basis for creating a manual scan file.

The Scan Data Template is a tool that allows agencies without scanner hardware to use Core-CT Asset Management to perform Physical Inventory. The use of this functionality traditionally requires an agency to have scanner hardware to produce a scan data file in Core-CT format (i.e. CSV, or Comma Separated Values). The Scan Data Template enables agencies to manually produce the data file.

Prerequisites

The following guidelines must be followed for the Template to produce a valid scan data file:

  • Data entered into fields should match the type and length indicated at the top of each column.
  • Make sure the length of the values you enter are less than or equal to the maximum size
    of each field, which is also indicated at the top of each column.
  • Do NOT modify the formatting of the Excel templates or fields in each of the templates.

Procedure

Prepare: Download the Scan Data Template from the Core-CT Website: Save the Excel file.

Part 1: (Optional - Go to Part 2: Enter New Scan Data Entries if skipping this section) Import a list of tag numbers from the extract file. (To create the extract file, see the WLA: Core-CT Asset Management > Performing Physical Inventory > Process Procedures > Loading, Matching, Reconciling Physical Inventory Information) As the physical inventory is performed, any missing rows can be added and extra rows deleted to accurately reflect the status of assets.

To import an extract file into the PI Scan Template, follow these steps:

1. Open the Core-CT provided Scan Data Excel Template. If you are prompted by the box below,
click the Enable Macros button.

2. The PI Scan Template spreadsheet is displayed.


3. Click on ToolsProtectionUnprotect Sheet… to allow the import.

4. Click on DataImport External DataImport Data…

5. The Select Data Source window displays.


6. Change the Files of type field to All Files (*.*) to view all files. Select the extract file you want to import (e.g., extract.dat or extract.txt). Click .
7. The Text Import Wizard – Step 1 of 3 window displays.

8. Keep the default values (as shown above). Ensure the Original Data Type field is set to Delimited. Click .

9. The Text Import Wizard – Step 2 of 3 window displays.

10. In step 2 of the wizard, all checkboxes should be unchecked except the Comma box. Set the Text Qualifier to the double quote character (“). Click .

11. The Text Import Wizard – Step 3 of 3 window displays.

12. The first column in the Data Preview box will be highlighted. Change the Column data format to Text for the first column.

13. Select all of the remaining columns in the preview box. To do this, first click the second column, which will highlight it. Then move the horizontal scroll to the far right side. Hold down the Shift key, click the last column on the right, and release the Shift key. All columns except the first should be highlighted. If not, scroll to the far left, click the second column on the left, and repeat this step.

14. Select the Text option in the Column data format box, then click .

15. The Import Data window displays.

16. Change the Existing worksheet field to =$A$2. This will ensure the data being imported is placed below the Template column headings. Click the button.

17. The External Data Range Properties dialog box displays. If the External Data Range Properties box does not display, then go to Step 19.

18. Uncheck the Adjust column width checkbox, and then Save query definition box. Click . The Import Data dialog box re-displays.

19. click in the Import Data window. The data import is finished. The spreadsheet should now be filled with a list of tag numbers from the extract file data. You should save the spreadsheet before going any further (see section

Part 3: Save your data while working on it).

20. Highlight columns A through S. Click on Format > Column > Unhide.
21. Delete unneeded rows. The Extract may include a greater scope of assets than is expected to be inventoried. You should review the imported tag numbers to ensure that the user expectations and the extract are in agreement. To delete a row, right click the number in the far left column next to the row to be deleted. The row is highlighted and a drop down box appears. Select the Delete command from the drop down menu.


22. Having the additional column information may assist you if you have difficulty locating an asset.

See the following section, Part 2: Enter new manual data entries, for more information about each field.

Part 2: Enter New Manual Data Entries

Enter data into the Template as you would for any MS Excel spreadsheet. Each row is synonymous to a single asset recorded by a hand-held scanner. As you find an asset while performing the physical inventory, record it with a new entry in the Template.
To add a new entry, enter data in the following columns as described:

Field / Description/Instructions
Tag number* / Enter the asset’s tag number.
Dept ID** / If you are performing Physical Inventory based on departments (rather than locations), enter the ‘Dept ID’ value. Most agencies will not use this column.
Location ID** / Confirm or enter the Core-CT location code where the asset was found.
Review?* / If you feel the asset or the asset PI entry should be reviewed for any reason, set this column to ‘Y’; otherwise, set it to ‘N’. The reason for the review can be indicated in the ‘Comments’ column.
Match?* / This column must be set to the capital letter ‘O’ if the asset is found.
PI Operator* / Enter your user ID (e.g. ‘JSMITH’)
Comments / Enter any text you would like to associate with this scan entry. The ‘Manual Review?’ column should be set to ‘Y’ if comments are entered, so the comments will be reviewed in Core-CT during PI processing. For example, the asset may need a new tag sticker or may be in need of repair.

* The four columns marked with a star are required in each entry.
** The Dept ID column is only required when performing PI by department.
** The Location ID is only required when performing PI by location, but may be
recorded when performing PI by Dept.

Part 3: Save your data while working on it

You can continue to enter and maintain the information as needed throughout the Physical Inventory process to get the data ready for processing in Core-CT. Please be sure to save your work frequently and make backups.
• Saving - As you are working on your Conversion Template, save the file as you normally would, using File  Save. You may receive the following message. If you do, click OK and the ‘Save As’ dialog box will be displayed.


The Template provided by Core-CT is in Excel format, so this will automatically
save the file with an ‘.xls’ extension. It is suggested you save the file with the Physical Inventory ID and date of inventory as part of the filename. (For example, DMV might save their Template as ScanData_DMVM1-PI1_May05.xls.)
• Make a Backup Copy of Your Spreadsheets
Always be sure to make a backup copy of your data. The spreadsheet contains the data that will be loaded into Core-CT and represents your current asset physical inventory. Follow your agency’s procedures for maintaining backup copies of agency production information. This might include such procedures as backing up data to tapes, burning copies to CD’s, copying files to backup storage devices, copying files to backup directories, and/or sending copies to off site storage facilities.

Part 4: Exporting the information as a scan data file in the Core-CT accepted format

This section provides a set of instructions on how to save the Template in the comma separated file format (.CSV) accepted by Core-CT AM Physical Inventory.
1. Save your data and back it up before continuing, as described in the section Part 3:
Save your data while working on it.
2. You will have to modify the spreadsheet before saving it to a scan data file. So, save to another, new temporary working copy of the spreadsheet as described in the section Part 3 so you do not accidentally modify the original Template data. Append the word temp to the file to differentiate it from the original copy (e.g., ScanData_DMVM1-PI1_May05temp.xls).

You should now have a copy of the original data saved (e.g., ScanData-DMVM1-PI_May05.xls) for backup and a copy of the data currently open (e.g., ScanData_DMVM1-PI1_May05temp.xls) to be modified and exported.
3. Once you complete the data entry and have saved a copy of your Template spreadsheet, you need to modify the Template by removing the column headings. To delete the column headings, click on the column heading row, row 1, so that the entire row is highlighted.


4. If you have not already done so, click on Tools ProtectionUnprotect Sheet to enable row deletions. Then click on EditDelete. This will delete the header row. Save your file again by clicking on FileSave.


5. To save the data files in the Core-CT CSV format, click on FileSave As.
The window that is displayed will default to a Save as type of Microsoft Excel Workbook (.xls). Change this value to CSV (Comma Delimited) (*.csv) as shown below. Make sure the File name value now ends with an extension of .CSV instead of .XLS. Also, you may want to change the filename to indicate the date the file was saved. (For example, this DMV file will be saved as ScanData_DMVM1-PI1_051005.csv.)


6. Click . If you receive the warning message shown below, click .


7. Close the Template by clicking on FileClose. You may receive another message asking if you want to save the file. It should already be saved, but click . This file should now be ready to upload to Core-CT in order to continue the Physical Inventory process.


8. If you receive the warning message again, shown below, click .

9. You have successfully saved a .CSV format scan data file. Follow the process for uploading the scan data file (e.g. ScanData_DMVM1-PI1_051005.csv) to Core-CT in order to continue with the AM Physical Inventory process. (See the WLA: Core-CT Asset Management > Performing Physical Inventory > Process Procedures > Loading, Matching, Reconciling Physical Inventory Information)

10. The temporary working spreadsheet (e.g., ScanData_DMVM1-PI1_May05temp.xls) may now be deleted from your computer.

Updated 1/5/2007