Import Units Usage Files Input File Format
Before importing units usage files data, make sure that the input file uses the format indicated on this topic.
Comma-Separated Values Files
Comma-Separated Values (CSV) files are files with data separated by commas. CSV processing is slightly slower than fixed-length processing.
Numbers are displayed without commas as numeric digits. The number of decimal places required for a particular field is illustrated by the examples below. Do not put a value in the place of a non-existent value.
Strings are displayed as alphanumeric characters and can be enclosed with double quotes ("). Double quotes cannot be contained within the string itself. If quotes are required, use single quotes. For a non-existent value, you can use two double quotes side by side (""), but this is not required.
Dates are displayed in the format MM/DD/YYYY. MM is a two-digit month. DD is a two-digit date. YYYY is a four-digit year. Do not put a value in the place of a non-existent value.
The following is a list of fields required in the order they should appear for each line.
| Column No | Column Name | Costpoint Field Name | Data Type | Data Length | Required / Optional | Example |
|---|---|---|---|---|---|---|
| 1 | ACCT_ID | Account | Character | 15 | R | 05030 |
| 2 | UNITS_QTY | Units Quantity | Number | 15 | R | 10000.0000 |
| 3 | ORG_ID | Organization | Character | 20 | R | 1.2.210 |
| 4 | ORG_ABBRV_CD | Organization Abbreviation | Character | 6 | O | 210 |
| 5 | PROJ_ID | Project | Character | 30 | R | 1010.003.20 |
| 6 | CLIN_ID | CLIN | Character | 10 | Required only if Price Catalog is not supplied in the input file. | NY LI |
| 7 | S_PRICE_SRCE_CD | Price Catalog | Character | 10 | Required only if CLIN is not supplied in the input file. | COOK |
| 8 | ITEM_ID | Item | Character | 50 | R | CHOC-D |
| 9 | ITEM_RVSN_ID | Item Revision | Character | 10 | R | 1 |
| 10 | SALES_TAX_CD | Sales Tax Code | Character | 6 | O | VA |
| 11 | USAGE_DT | Usage Date | Date | 10 | R | 07/31/2011 |
| 12 | TRN_DESC | Transaction Description | Character | 30 | O | Test |
| 13 | PROJ_ABBRV_CD | Project Abbreviation | Character | 6 | O | 511101 |
| 14 | Ref1_ID | Reference Number 1 | Character | 20 | O | 100 |
| 15 | Ref2_ID | Reference Number 2 | Character | 20 | O | 200 |
| 16 | UDEF_UNITS_1_AMT | User Defined Units Amount 1 | Number | 15 | O | 1.99 |
| 17 | UDEF_UNITS_2_AMT | User Defined Units Amount 2 | Number | 15 | O | 2.99 |
| 18 | UDEF_UNITS_3_DESC | User Defined Units Description | Character | 30 | O | Test |
| 19 | UDEF_UNITS_4_DT | User Defined Units Date | Date | 10 | O | 07/31/2011 |
| 20 | SRCE_PROJ_ID | Source Project | Character | 30 | R | 1010.003.20 |
| 21 | NOTES | Notes | Character | 254 | O | Use 'quotes.' |
Sample Line:
"05030",10000.0000,"1.2.210","210","1010.003.20","","COOK","CHOC-D","1","VA",07/31/2011,"Test.","","100","200",1.99,2.99,"Test.",07/31/2011,"1010.003.20","Use 'quotes.'"
Fixed-Length Files
Fixed-Length files are files with data positioned in a set range of columns for each particular field. For a non-existent value, put spaces in the range of columns for that field. Fixed-length processing is slightly faster than CSV processing.
Numbers are displayed without commas as numeric digits. The number of decimal places allowed for a particular field is illustrated by the examples below.
Strings are displayed as alphanumeric characters. Unlike CSV files, double quotes can be used.
Dates are displayed in the format MM/DD/YYYY. MM is a two-digit month. DD is a two-digit year. YYYY is a four-digit year.
The following is a list of fields required in the order they should appear for each line.
| Field | Type | Column Size | Range | Example |
|---|---|---|---|---|
| Account | Character | 15 | 1 - 15 | 05030 |
| Units Quantity | Number | 15 | 16 - 30 | 10000.0000 |
| Organization | Character | 20 | 31 - 50 | 1.2.210 |
| Organization Abbreviation | Character | 6 | 51 - 56 | 210 |
| Project | Character | 30 | 57 - 86 | 1010.003.20 |
| CLIN | Character | 10 | 87 - 96 | NY LI |
| Price Catalog | Character | 10 | 97 - 106 | COOK |
| Item | Character | 50 | 107 - 156 | CHOC-D |
| Item Revision | Character | 10 | 157 - 166 | 1 |
| Sales Tax Code | Character | 6 | 167 - 172 | VA |
| Usage Date | Date | 10 | 173 - 182 | 07/31/2011 |
| Transaction Description | Character | 30 | 183 - 212 | Test |
| Project Abbreviation | Character | 6 | 213 - 218 | 511101 |
| Reference Number 1 | Character | 20 | 219 - 238 | 100 |
| Reference Number 2 | Character | 20 | 239 - 258 | 200 |
| User Defined Units Amount 1 | Number | 15 | 259 - 273 | 1.99 |
| User Defined Units Amount 2 | Number | 15 | 274 - 288 | 2.99 |
| User Defined Units Description | Character | 30 | 289 - 318 | Test |
| User Defined Units Date | Date | 10 | 319 - 328 | 07/31/2011 |
| Source Project | Character | 30 | 329 - 358 | 1010.003.20 |
| Notes | Character | 254 | 359 - 612 | Use 'quotes.' |
Sample Line:
05030 10000.0000 1.2.210 210 1010.003.20 NY LI COOK CHOC-D 1 VA07/31/2011 Test 511101100...