Information and support

Format of a downloaded CSV file

The information available varies according to account type. The following table shows the format of information according to account type.

Caisse, Folio, PCA, Date, Sequence, Description, Cheque, Withdrawal, Deposit, , , , , Balance

Caisse, Folio, RS, Date, Sequence, Description, , Withdrawal, Deposit, , , , , Balance

Caisse, Folio, TS, Date, Sequence, Description, , Withdrawal, Deposit, , , , , Balance

Caisse, Folio, CS, Date, Sequence, Description, , Withdrawal, Deposit, , , , , Balance

Caisse, Folio, LN, Date, Sequence, Description, , , , Interest, Capital paid, , Reimbursement, Balance

Caisse, Folio, LC, Date, Sequence, Description, , , , Interest, , Advance, Reimbursement, Balance

Visa #### #### #### ####, , , Date, Sequence, Description, , , , , , Advance, Reimbursement

Field formats

The following table provides information on the format of each field (type, length, number of decimal places and a brief description).

Field	Type	Length	Decimal	Description
Institution	C	25	No	Abbreviated name of caisse or credit card
Folio	C	7	No	Folio number
Type_account	C	5	No	Type of account: PCA, RS, TS, LN, LC, CS
Date	C	10	No	Format: YYYY/MM/DD
Sequence	N	3	0	Sequential number for sorting
Description	C	90	No	Description of transactions
Cheque	N	8	0	Cheque number (PCA)
Withdrawal	N	13	2	Withdrawal amount (PCA,RS,TS,CS)
Deposit	N	13	2	Deposit amount (PCA,RS,TS,CS)
Interest	N	13	2	Interest (LN,LC)
Capital paid	N	13	2	Capital paid (LN)
Advance	N	13	2	Cash advance (LC, credit card)
Reimbursement	N	13	2	Reimbursement (LN,LC, credit card)
Balance	N	13	2	Balance

Note:

• The fields of a character ("C") type are placed between quotation marks ("").
• The fields of a numerical type ("N") are not placed between quotation marks.
• Absent fields are marked by "".
• Decimals are set apart by a period (.)
• Type of account corresponds to personal chequing, regular savings, etc.
• The sequential number numbers the transactions for a caisse, folio, account and date.
• The order of sorting or the sorting key is made up of the fields: Caisse, Folio, Account, Date, Sequence.

Example of transactions:

"C.P. Ste-Edith","27656","PCA","1997/06/16",1,"Withdrawal at counter",125.75,"","","","","",678.65

"C.P. Ste-Edith","27656","PCA","1997/06/17",1,"Deposit at counter","",654.87,"","","","",2774.65

"Visa 4540 1234 1234 1234","","","1997/06/17",1,"Suzy Shier","","","","",124.66,"",2774.65

Templates available for download

We have designed templates (models) into which you can insert your first downloaded file. This ensures that you'll have a good definition of the data in the software program of your choice. This way, you won't have to worry about correctly defining the column widths in your spreadsheet or the data type in your database.

Templates are available in the following formats:

• Microsoft Excel (XLS) and Lotus 1-2-3 (ZIP, 1 KB) - This link will open in a new window.
• Microsoft Access 2003 (MDB) (ZIP, 12 KB) - This link will open in a new window.
• Microsoft Access 97 (MDB) (ZIP, 32 KB) - This link will open in a new window.
• Borland dBase and other compatible software programs (DBF) (ZIP, 1 KB) - This link will open in a new window.

You may download the template of your choice by clicking on the name of the software that you use from the previous list.

If the software you use does not accept the above templates, please let us know the name of your software so we may meet your needs.

Using templates

First, you'll need to download the template that corresponds to the software you use. Then, download your transaction summary in CSV format.

We suggest that you always keep a previous version of your working document. To do so, save your file under another name before starting to import any data. That way, if you make a mistake, you won't lose all the information.

Lotus 1-2-3, here's how to proceed: (detailed procedure)

1. From the File menu, select Open.
2. Specify the downloaded CSV file containing your statement.
3. If you do not see your CSV file, type *.* as the file name so that Lotus displays all files regardless of their extension.
4. Make sure that the file type is Text.
5. Click Open to activate the Import Wizard.
6. Every separator indicates the beginning of a new column.
7. The type of data must be set as Delimited.
8. Click OK.
9. Select all data (from columns A to N) and click Copy.
10. Open the template.
11. Place the cursor after the last line in Column A.
12. Click Paste.
13. Save the template.

Microsoft Excel, here's how to proceed: (detailed procedure)

1. From the Data menu, select Import External Data / Import Data.
2. Specify the downloaded CSV file containing your statement.
3. If you do not see your CSV file, type *.* for file name so that Excel displays all files regardless of their extension.
4. Click on the Import button to activate the Import Wizard.
5. The data type must be set as Delimited.
6. Click Next.
7. The field delimiter must be the comma, and the text qualifier must be quotation marks.
8. It is not necessary to change the format of the columns.
9. Click Finish.
10. Select all the data (from Columns A to N) and click Copy.
11. Open the template.
12. Place the cursor just after the last line in Column A.
13. Click Paste.
14. Save the template.

Microsoft Access, here's how to proceed: (detailed procedure)

1. Open the template.
2. From the File menu, choose the Get external data option and Import.
3. Specify the name of the downloaded CSV file.
4. If you do not see your CSV file, indicate *.* as file name so that Access displays all files regardless of their extension.
5. The file type must be set as Text.
6. Click Import to activate the Import Wizard.
7. The data type must be set as Delimited.
8. Click Next.
9. The field delimiter must be the comma.
10. The text qualifier must be quotation marks.
11. Click on the Advanced button.
12. Set the Decimal Symbol as the period.
13. Click OK.
14. Click Next.
15. The data will be saved in an existing table called "transactions."
16. Click Next.
17. Click Finish to confirm the the data import.

Note:

• A query called "Transactions list" lets you view your transactions sorted according to the selected sort key.
• Another query called "Transactions by account" lets you name the account for which you want to view the transactions list.
• A statement (report) called "Transactions list" gives you a report in printable version.

Borland dBase and other compatible software programs:

• The use of templates with these programs varies a great deal.
• You cannot directly use the dBase importing facilities because the command APPEND FROM does not operate correctly with a CSV file.
• You can write a dBase program to read it using FREAD(). After extracting each field, the program should insert a record with APPEND BLANK and initialize all the fields.

Lotus 1-2-3, detailed procedure:

1. From the File menu, select Open to locate your CSV file.
2. If you do not see your CSV file, type *.* as the file name and click Enter to display all files.
3. Make sure the file type is Text.
4. Click Open to activate the Import Wizard.
5. Specify Start a new column at each.
6. The field delimiter must be the comma.
7. Click OK.
   
8. Select all data (from columns A to N) and click Copy.
9. Open the template.
10. Place the cursor after the last line in Column A.
11. Click Paste.
12. Save the template.

Microsoft Excel 2003, detailed procedure:

1. To open the CSV file containing your statement, from the Data menu, select Import External Data / Import Data.
2. If you do not see your CSV file, type *.* as the file name and click Enter to display all files.
3. Select the file containing your statement. Click Import to activate the Import Wizard.
4. The data type must be set as Delimited.
5. Click Next.
   
6. The field delimiter must be the comma.
7. The text qualifier must be quotation marks.
8. Click Finish.
   
9. It is not necessary to change the format of the columns.
10. Click Next.
    
11. Select all the data (from Columns A to N) and click Copy.
12. Open the template.
13. Place your cursor just after the last line in Column A.
14. Click Paste.
15. Save the template.

Microsoft Excel 2007, detailed procedure:

1. To open the CSV file containing your statement, click on the Data tab in the menu.
2. Click Get External Data, then From Text.
   
3. Select the CSV statement, then click on Import.
   
4. Once the Text Import Wizard window opens, select Delimited as the original data type.
   
5. Click Next.
6. The delimiter must be the comma.
7. The text qualifier must be quotation marks.
   
8. Click Finish, then click OK.

Microsoft Access, detailed procedure:

1. Open the template.
2. From the File menu, choose the Get external data option and Import.
3. Specify the name of the downloaded CSV file.
4. If you do not see your CSV file, indicate *.* as file name so that Access displays all files regardless of their extension.
5. Make sure that the file type is Text.
6. Click on Import to activate the Import Wizard.
7. The data format must be set as Delimited.
8. Click Next.
   
9. The field delimiter must be the comma.
10. The text qualifier must be quotation marks.
11. Click on the Advanced… button.
    
12. Set the Decimal Symbol as the period.
13. Click OK.
14. Click Next.
    
15. The data will be saved in an existing table called ”transactions”.
16. Click Next.
    
17. Click Finish to confirm the data import.
    

Notes concerning Access:

The screen shots are from the 2003 version. There may be some differences with other versions of the software.

Notes concerning Access 2003:

A query called "Transactions list" feature lets you view your transactions sorted according to the selected sort key.

Another query called "Transactions by account" lets you name the account for which you want to view the transactions list.

A statement (report) called "Transaction lists" gives you a report in printable version.