CSV File and Excel Format

[alexm999]

I was given a task to export data from excel to another program and the
other program requires specific input.

The input file is a comma delimited file that must be formatted as
follows.
ACCOUNT ID,CLIENT ID,TRANSACTION CODE,NAME,BANK ACCOUNT#,BANK
ROUTING#,AMOUNT,EFFECTIVE DATE,TRANSACTION STATUS,

FIELD DESCRIPTION
ACCOUNT ID Account ID found in your Browse Accounts optionMaximum 3
characters (REQUIRED KEY FIELD)
CLIENT ID Client ID assigned by you. This ID must be unique.Maximum 15
characters (REQUIRED KEY FIELD)
TRANSACTION CODE Receivables Codes27 - Checking Deduction37 - Savings
DeductionPayables/Payroll Codes22 - Checking Deposit32 - Savings
DepositMaximum 2 characters (REQUIRED KEY FIELD)
NAME Client nameMaximum 22 characters (OPTIONAL)Note: No commas allowed
in name
BANK ACCOUNT# Client's bank account numberMaximum 17 characters
(OPTIONAL)
BANK ROUTING# Client's bank routing numberMust be 9 characters
(OPTIONAL)
AMOUNT Amount of transaction (must contain the decimal point)e.g. For
Ten Dollars enter 10.00, for Seventy Five Cents enter .75Maximum 10
characters (OPTIONAL)
EFFECTIVE DATE Date transaction is due - Day onlyMaximum 2 characters
(OPTIONAL)
CLIENT STATUS Transaction StatusA - ACTIVEI - INACTIVEH - HOLDMaximum 1
character (OPTIONAL)

Sample Transactions:
A1,000000000100001,27,Import AR 1,131333675,150.00,
A2,000000000100001,22,Import AP 1,131333675,150.00,
A3,000000000100001,22,Import PR 1,131333675,150.00,
A1,000000000100002,27,Import AR 2,131333675,150.00,
A2,000000000100002,22,Import AP 2,131333675,150.00,
A3,000000000100002,22,Import PR 2,131333675,150.00,


How do I set up in excel field size? and the formating mentioned above?

 

[Frank Kabel]

Hi
some ideas:
- as heading row in your spreadsheet use the identifiers
mentioned below
- use 'Data - Validation' for restricting the entries
- save the file as CSV format

 

[Earl Kiosterud]

Alex,

You don't have to set up field sizes. When you save a file as csv or txt
(in the File Type box of the Save As dialog), it writes to a text file using
however many characters are in the cells.

If you're confident that your cells don't exceed the spec you have for each
field, you're ready to go. If you can't ensure that, then a macro could be
written to check the sheet for excessive field lengths, or another worksheet
with IF(LEN()>n, "error","") formulas could do it.

If File - Save As doesn't produce a suitable text file for some reason, try
the Text Write Program at www.tushar-mehta.com.