Structure CSV Files for the American Express Receipt Import Wizard

Some time ago, American Express changed the format of their CSV export for a “solo user” account such that it is no longer accepted by the American Express receipt import wizard.

This tip explains how to format a CSV file that the wizard will accept. This format can be used to import ANY receipts whether from AMEX or another credit card provider. Interestingly the SuiteAnswers answer id 18055 partially explains this topic but does not provide enough detail to actually make it work.

“Solo” file format

• Must be in CSV format.
• NO HEADING OR TOTAL ROWS ALLOWED.
• The columns of the file should be:

1.     A date in MM/DD/YYYY format

2.     The literal text “Reference:”

Note that no real reference number is needed…just the word Reference with a colon. If you include a number after the colon, it will still work, but oddly, the number is not stored anywhere on the receipt.

3.     The amount REPRESENTED AS A NEGATIVE NUMBER

It will be a positive value on the resulting receipt.

4.     A text value that will become both the Description and the first line of the Notes field in the receipt.

Yes – for some reason it is used twice.

5.     A second text value that will become the second line of the Notes field.

 

Here is an example of the data as it might look in Excel:

9/18/2023	Reference:	-27.72	Description 1	Description 2
9/14/2023	Reference:	-6.99	Description 1	Description 2
9/14/2023	Reference:	-9.99	Description 1	Description 2
9/9/2023	Reference:	-10	Description 1	Description 2

 

Here is how it might look in Notepad:

9/18/2023,Reference: ,-27.72,Description 1,Description 2

9/14/2023,Reference: ,-6.99,Description 1,Description 2

9/14/2023,Reference: ,-9.99,Description 1,Description 2

9/9/2023,Reference: ,-10,Description 1,Description 2