To convert American Express CSV to QIF

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

To convert American Express CSV to QIF

Michael DeBusk-2
Some time back, I found that I could no longer download QIF files from
American Express. I could, however, get a CSV. The following is my
first shot at a one-liner to convert that particular CSV to QIF.

cat ofx.csv | sed -e 's/\"//g;s/Reference\:\ //g;s/ \{1,\}/ /g'
ofx.csv | awk -F, '{ print "D" $1; print "N" $2; print "T" $3; print
"P" $4; print "M" $5; print "LExpenses:&&"; print "^"; }' > amex.qif

It's not perfect, but since I can barely scratch the surface of awk
and sed, it'll do. I haven't found that it ruins anything. If anyone
can improve upon it, I'd be grateful.

(The last awk instruction to print "LExpenses:&&" creates an account
placeholder for me in each transaction. I go in and edit the QIF
afterward, replacing "&&" with the appropriate account name.)

--
()  ascii ribbon campaign     * against html e-mail
/\  www.asciiribbon.org       * against proprietary attachments
Home: http://nlphilia.com     * Blog: http://nlphilia.net
Registered Linux User #450983 * Ubuntu Counter Project #10548
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: To convert American Express CSV to QIF

David Carlson-4
On 10/27/2014 3:38 PM, Michael DeBusk wrote:

> Some time back, I found that I could no longer download QIF files from
> American Express. I could, however, get a CSV. The following is my
> first shot at a one-liner to convert that particular CSV to QIF.
>
> cat ofx.csv | sed -e 's/\"//g;s/Reference\:\ //g;s/ \{1,\}/ /g'
> ofx.csv | awk -F, '{ print "D" $1; print "N" $2; print "T" $3; print
> "P" $4; print "M" $5; print "LExpenses:&&"; print "^"; }' > amex.qif
>
> It's not perfect, but since I can barely scratch the surface of awk
> and sed, it'll do. I haven't found that it ruins anything. If anyone
> can improve upon it, I'd be grateful.
>
> (The last awk instruction to print "LExpenses:&&" creates an account
> placeholder for me in each transaction. I go in and edit the QIF
> afterward, replacing "&&" with the appropriate account name.)
>
> --
> ()  ascii ribbon campaign     * against html e-mail
> /\  www.asciiribbon.org       * against proprietary attachments
> Home: http://nlphilia.com     * Blog: http://nlphilia.net
> Registered Linux User #450983 * Ubuntu Counter Project #10548
> _______________________________________________
> gnucash-user mailing list
> [hidden email]
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
>

I can still download my American Express transactions in OFX format.  I
just pretend that I want a Quicken file.

David C
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: To convert American Express CSV to QIF

Michael DeBusk-2
On Mon, Oct 27, 2014 at 5:05 PM, David Carlson
<[hidden email]> wrote:

> I can still download my American Express transactions in OFX format.  I
> just pretend that I want a Quicken file.

I can do the same. I prefer QIF, though.

--
()  ascii ribbon campaign     * against html e-mail
/\  www.asciiribbon.org       * against proprietary attachments
Home: http://nlphilia.com     * Blog: http://nlphilia.net
Registered Linux User #450983 * Ubuntu Counter Project #10548
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: To convert American Express CSV to QIF

Wes704
In reply to this post by Michael DeBusk-2
Michael, I download AMEX transactions as MSEXCEL and use XL2QIF to convert
them for importing to GNUCash. There is also CALC2QIF.



On Mon, Oct 27, 2014 at 4:38 PM, Michael DeBusk <[hidden email]> wrote:

> Some time back, I found that I could no longer download QIF files from
> American Express. I could, however, get a CSV. The following is my
> first shot at a one-liner to convert that particular CSV to QIF.
>
> cat ofx.csv | sed -e 's/\"//g;s/Reference\:\ //g;s/ \{1,\}/ /g'
> ofx.csv | awk -F, '{ print "D" $1; print "N" $2; print "T" $3; print
> "P" $4; print "M" $5; print "LExpenses:&&"; print "^"; }' > amex.qif
>
> It's not perfect, but since I can barely scratch the surface of awk
> and sed, it'll do. I haven't found that it ruins anything. If anyone
> can improve upon it, I'd be grateful.
>
> (The last awk instruction to print "LExpenses:&&" creates an account
> placeholder for me in each transaction. I go in and edit the QIF
> afterward, replacing "&&" with the appropriate account name.)
>
> --
> ()  ascii ribbon campaign     * against html e-mail
> /\  www.asciiribbon.org       * against proprietary attachments
> Home: http://nlphilia.com     * Blog: http://nlphilia.net
> Registered Linux User #450983 * Ubuntu Counter Project #10548
> _______________________________________________
> gnucash-user mailing list
> [hidden email]
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
>
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: To convert American Express CSV to QIF

Michael DeBusk-2
I tried to get that working and couldn't make it go.

On Wed, Jan 21, 2015 at 12:08 PM, Wes Metz <[hidden email]> wrote:

> Michael, I download AMEX transactions as MSEXCEL and use XL2QIF to convert
> them for importing to GNUCash. There is also CALC2QIF.
>
>
>
> On Mon, Oct 27, 2014 at 4:38 PM, Michael DeBusk <[hidden email]> wrote:
>>
>> Some time back, I found that I could no longer download QIF files from
>> American Express. I could, however, get a CSV. The following is my
>> first shot at a one-liner to convert that particular CSV to QIF.
>>
>> cat ofx.csv | sed -e 's/\"//g;s/Reference\:\ //g;s/ \{1,\}/ /g'
>> ofx.csv | awk -F, '{ print "D" $1; print "N" $2; print "T" $3; print
>> "P" $4; print "M" $5; print "LExpenses:&&"; print "^"; }' > amex.qif
>>
>> It's not perfect, but since I can barely scratch the surface of awk
>> and sed, it'll do. I haven't found that it ruins anything. If anyone
>> can improve upon it, I'd be grateful.
>>
>> (The last awk instruction to print "LExpenses:&&" creates an account
>> placeholder for me in each transaction. I go in and edit the QIF
>> afterward, replacing "&&" with the appropriate account name.)
>>
>> --
>> ()  ascii ribbon campaign     * against html e-mail
>> /\  www.asciiribbon.org       * against proprietary attachments
>> Home: http://nlphilia.com     * Blog: http://nlphilia.net
>> Registered Linux User #450983 * Ubuntu Counter Project #10548
>> _______________________________________________
>> gnucash-user mailing list
>> [hidden email]
>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
>> -----
>> Please remember to CC this list on all your replies.
>> You can do this by using Reply-To-List or Reply-All.
>
>



--
()  ascii ribbon campaign     * against html e-mail
/\  www.asciiribbon.org       * against proprietary attachments
Home: http://nlphilia.com     * Blog: http://nlphilia.net
Registered Linux User #450983 * Ubuntu Counter Project #10548
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: To convert American Express CSV to QIF

Wes704
Michael, I never used xl2qif for more than a few transactions and had to
correct entries after the import. After your experience, I went at it again
and was able to make it work correctly. The documentation is not very good
so I will describe how I use it.

I get the transactions I want to import into a three column, excel sheet
containing the date, vendor and amount. (Credit Card charges must be a
negative number; credits and payments positive.) I select only the cells
that will be imported (no column headers or row labels).

Then I start xl2qif. Set the *QIF Output File *to something that makes
sense to you. Make sure the *Input Data *is what you selected. *Clear
*the *Fields
present in Input Data. *Select the following fields in this order: *Date,
Payee, Amount. *Verify the *Account Type *and *Date Format*. *Convert *and
import the file. It should work.

On Wed, Jan 21, 2015 at 6:33 PM, Michael DeBusk <[hidden email]> wrote:

> I tried to get that working and couldn't make it go.
>
> On Wed, Jan 21, 2015 at 12:08 PM, Wes Metz <[hidden email]> wrote:
> > Michael, I download AMEX transactions as MSEXCEL and use XL2QIF to
> convert
> > them for importing to GNUCash. There is also CALC2QIF.
> >
> >
> >
> > On Mon, Oct 27, 2014 at 4:38 PM, Michael DeBusk <[hidden email]>
> wrote:
> >>
> >> Some time back, I found that I could no longer download QIF files from
> >> American Express. I could, however, get a CSV. The following is my
> >> first shot at a one-liner to convert that particular CSV to QIF.
> >>
> >> cat ofx.csv | sed -e 's/\"//g;s/Reference\:\ //g;s/ \{1,\}/ /g'
> >> ofx.csv | awk -F, '{ print "D" $1; print "N" $2; print "T" $3; print
> >> "P" $4; print "M" $5; print "LExpenses:&&"; print "^"; }' > amex.qif
> >>
> >> It's not perfect, but since I can barely scratch the surface of awk
> >> and sed, it'll do. I haven't found that it ruins anything. If anyone
> >> can improve upon it, I'd be grateful.
> >>
> >> (The last awk instruction to print "LExpenses:&&" creates an account
> >> placeholder for me in each transaction. I go in and edit the QIF
> >> afterward, replacing "&&" with the appropriate account name.)
> >>
> >> --
> >> ()  ascii ribbon campaign     * against html e-mail
> >> /\  www.asciiribbon.org       * against proprietary attachments
> >> Home: http://nlphilia.com     * Blog: http://nlphilia.net
> >> Registered Linux User #450983 * Ubuntu Counter Project #10548
> >> _______________________________________________
> >> gnucash-user mailing list
> >> [hidden email]
> >> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> >> -----
> >> Please remember to CC this list on all your replies.
> >> You can do this by using Reply-To-List or Reply-All.
> >
> >
>
>
>
> --
> ()  ascii ribbon campaign     * against html e-mail
> /\  www.asciiribbon.org       * against proprietary attachments
> Home: http://nlphilia.com     * Blog: http://nlphilia.net
> Registered Linux User #450983 * Ubuntu Counter Project #10548
> _______________________________________________
> gnucash-user mailing list
> [hidden email]
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
>
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
Reply | Threaded
Open this post in threaded view
|

Re: To convert American Express CSV to QIF

Michael DeBusk-2
On Mon, Feb 23, 2015 at 4:28 PM, Wes Metz <[hidden email]> wrote:

> Michael, I never used xl2qif for more than a few transactions and had to
> correct entries after the import. After your experience, I went at it again
> and was able to make it work correctly. The documentation is not very good
> so I will describe how I use it.

Thanks for all the trouble. It appears that the OpenOffice version
does not work with current versions of Libreoffice. :(

--
()  ascii ribbon campaign     * against html e-mail
/\  www.asciiribbon.org       * against proprietary attachments
Home: http://nlphilia.com     * Blog: http://nlphilia.net
Registered Linux User #450983 * Ubuntu Counter Project #10548
_______________________________________________
gnucash-user mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.