CSV Input File Format
The Import Cash Receipts screen allows you to import cash receipts from a comma-separated values (CSV) file.
This help topic contains information on how to prepare a CSV input file that you can use import cash receipts Into Costpoint.
CSV File
A CSV file is a delimited text file that uses a commas to separate values. Each line of the file is a data record.
Each cash receipt must be in a separate line in the input file. There are separate formats for the cash receipt header and detail line records. Header records are identified by the H record type and will be imported to the CASH_RECPT_HEADER table, while detail line records are identified by the L record type and will be imported to the CASH_RECPT_TRN table.
When starting a line in the input file, you must first enter the record type as the first field to identify the record as a cash receipt header or detail line. Costpoint then imports that record to the appropriate table.
CSV Input File Format
Cash Receipt Header
Use this input file layout to enter a cash receipt header record (record type 'H'). The following table lists the entries contained in each header record of the input file.
| Column | Costpoint Field | Type | Length | Required or Optional | Format/Notes |
|---|---|---|---|---|---|
| Line Type | N/A | Character | 1 | Required | H for header |
| Receipt Date | RECPT_DT | Date | 10 | Required | MM/DD/YYYY |
| Receipt ID | CASH_RECPT_NO | Number | 10 | Optional | Can be system-generated |
| Bank Deposit No. | BANK_DEPOSIT_NO | Alphanumeric | 15 | Optional | |
| Fiscal Year | FY_CD | Alphanumeric | 6 | Optional | If null, value from screen is applied |
| Period | PD_NO | Number | 5 | Optional | If null, value from screen is applied |
| Subperiod | SUB_PD_NO | Number | 5 | Optional | If null, value from screen is applied |
| Pay Currency Code | PAY_CRNCY_CD | Alphanumeric | 3 | Optional | Enter value if Multicurrency is activated |
| Rate Group ID | RATE_GRP_ID | Alphanumeric | 6 | Optional | Enter value if Multicurrency is activated |
| Currency Date | PAY_CRNCY_DT | Date | 10 | Optional | Enter value if Multicurrency is activated |
Cash Receipt Detail Lines
Use this input file layout to enter a cash receipt detail line (record type 'L'). The following table lists the entries contained in each record of the input file.
| Column | Costpoint Field | Type | Length | Required or Optional | Format/Notes |
|---|---|---|---|---|---|
| Line Type | N/A | Character | 1 | Required | L for line |
| Line Source Code | S_LINE_SOURCE_CD | Character | 1 | Required | I for invoice
M for miscellaneous T for transfer |
| Receipt ID | CASH_RECPT_NO | Number | 10 | Required | |
| Line No. | LN_NO | Number | 5 | Required | |
| Invoice ID | INVC_ID | Alphanumeric | 15 | Optional | If null, UNK invoice is created |
| Customer ID | CUST_ID | Alphanumeric | 12 | Required | |
| Check ID | CR_CHECK_ID | Alphanumeric | 40 | Optional | |
| Transaction Amount | TRN_AMT | Decimal | 17, 2 | Required | |
| Transaction Description | TRN_DESC | Alphanumeric | 30 | Required | |
| Discount Taken Amount | DISC_TAKEN_AMT | Decimal | 17, 2 | Required | |
| Cash Account Description | CASH_ACCT_KEY | Optional | If null, default is provided | ||
| Bank Abbreviation | BANK_ACCT_ABBRV | Alphanumeric | 6 | Optional | |
| Account ID | ACCT_ID | Alphanumeric | 15 | Required | |
| Org ID | ORG_ID | Alphanumeric | 20 | Required | |
| Project ID | PROJ_ID | Alphanumeric | 30 | Optional | |
| Reference 1 | REF1_ID | Alphanumeric | 20 | Optional | |
| Reference 2 | REF2_ID | Alphanumeric | 20 | Optional | |
| Project Account Abbreviation | PROJ_ACCT_ABBRV_CD | Optional | |||
| Finance Charge | FINCHG_RCV_AMT | Decimal | 17, 2 | Optional | |
| Expense Report ID | EXP_RPT_ID | Optional | |||
| Bank Account Amount | BANK_AMT | Decimal | 17, 2 | Optional | |
| Line Notes | NOTES | Alphanumeric | 250 | Optional |