creating QIF file under Dos using AWK

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

creating QIF file under Dos using AWK

piggyb
Hello I am trying to created a QIF file from a CSV using AWK under Dos
I have found a example script and edit it to work under Dos
But I can't get the script to print out the ^ circumflex accent
I have tried to escape characters using ^^

My Current script looks like this
gawk < statement.csv -F, "{ print \"D\" $1; print \"P\" $2; print \"T\" $3; print \"^\"; }" > statement.qif

Can someone also tell me how I can skip the first two rows, that would be a lot of help to me
Reply | Threaded
Open this post in threaded view
|

Re: creating QIF file under Dos using AWK

Wrolf
Try awk < statement.csv -F, 'NR > 2 { print "D" $1; print "P" $2; print "T"
$3;
print "^"; }' > statement.qif

Wrolf
[hidden email]

On Fri, Mar 6, 2015 at 10:33 AM, piggyb <[hidden email]> wrote:

> Hello I am trying to created a QIF file from a CSV using AWK under Dos
> I have found a  example script
> <
> http://wiki.gnucash.org/wiki/FAQ#Q:_How_do_I_convert_from_CSV.2C_TSV.2C_XLS_.28Excel.29.2C_or_SXC_.28OpenOffice.org_Calc.29_to_a_QIF.3F
> >
> and edit it to work under Dos
> But I can't get the script to print out the ^ circumflex accent
> I have tried to  escape <http://www.robvanderwoude.com/escapechars.php>
> characters using ^^
>
> My Current script looks like this
> /gawk < statement.csv -F, "{ print \"D\" $1; print \"P\" $2; print \"T\"
> $3;
> print \"^\"; }" > statement.qif/
>
> Can someone also tell me how I can skip the first two rows, that would be a
> lot of help to me
>
>
>
>
> --
> View this message in context:
> http://gnucash.1415818.n4.nabble.com/creating-QIF-file-under-Dos-using-AWK-tp4676673.html
> Sent from the GnuCash - User mailing list archive at Nabble.com.
> _______________________________________________
> 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: creating QIF file under Dos using AWK

Roger Miskowicz
In reply to this post by piggyb
I might use:

cat file | sed 1,2d

because I know a little sed,

On Fri, Mar 6, 2015 at 10:33 AM, piggyb <[hidden email]> wrote:

> Hello I am trying to created a QIF file from a CSV using AWK under Dos
> I have found a  example script
> <
> http://wiki.gnucash.org/wiki/FAQ#Q:_How_do_I_convert_from_CSV.2C_TSV.2C_XLS_.28Excel.29.2C_or_SXC_.28OpenOffice.org_Calc.29_to_a_QIF.3F
> >
> and edit it to work under Dos
> But I can't get the script to print out the ^ circumflex accent
> I have tried to  escape <http://www.robvanderwoude.com/escapechars.php>
> characters using ^^
>
> My Current script looks like this
> /gawk < statement.csv -F, "{ print \"D\" $1; print \"P\" $2; print \"T\"
> $3;
> print \"^\"; }" > statement.qif/
>
> Can someone also tell me how I can skip the first two rows, that would be a
> lot of help to me
>
>
>
>
> --
> View this message in context:
> http://gnucash.1415818.n4.nabble.com/creating-QIF-file-under-Dos-using-AWK-tp4676673.html
> Sent from the GnuCash - User mailing list archive at Nabble.com.
> _______________________________________________
> 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: creating QIF file under Dos using AWK

piggyb
In reply to this post by Wrolf
Unfortunately I am still getting a error about the ^ circumflex accent
I have read to escape characters under dos I can use a external script awk -f script.awk
awk < statement.csv -F, script.awk > statement.qif

But no clear idea how to implement it
prl
Reply | Threaded
Open this post in threaded view
|

Re: creating QIF file under Dos using AWK

prl
The command as you've typed it will try to execute the string
"script.awk" as an awk program and fail.

You need something more like:
     awk -F, -f script.awk statement.csv > statement.qif
or, if you really want to use input redirection
     awk -F, -f script.awk < statement.csv > statement.qif

I've always found character quoting in the Windows cmd shell one of the
Dark Arts, so using a script file is probably going to work a lot better
for you.

Peter

On 7/03/2015 22:05, piggyb wrote:

> Unfortunately I am still getting a error about the ^ circumflex accent
> I have read to escape characters under dos I can use a external script awk
> -f script.awk
> /awk < statement.csv -F, script.awk > statement.qif/
>
> But no clear idea how to implement it
>
>
>
>
> --
> View this message in context: http://gnucash.1415818.n4.nabble.com/creating-QIF-file-under-Dos-using-AWK-tp4676673p4676689.html
> Sent from the GnuCash - User mailing list archive at Nabble.com.
> _______________________________________________
> 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: creating QIF file under Dos using AWK

piggyb
This post was updated on .
Thank for letting me know how to use a external script
Because I am using a comma separated file ""; I tried these scripts

NR <2 {FS =";"}{ print "D" $1; print "P" $2; print "T" $3; print "^"; }
NR <2 {FS ="\"*;\"*"}{ print "D" $1; print "P" $2; print "T" $3; print "^"; }


But now the script reads form the first line and displays a comma in front of the bank-number

BANK EXPORT
BankAcountTransactiondateValutacodeCreditDebetAmountBanknrbankaccountdate Method DiscriptionTypeMachtigingsnummerauthorization Number Adres
12345631-12-2014EURC50987569gnucash01-01-2015iDEALContract 154543
12345731-12-2014EURD60987570gnucash31-12-2014iDEALContract 154544HKU0000324354537934KSADASD35342434235Hippo Land
12345831-12-2014EURD70987571gnucash31-12-2014iDEALContract 154545HKU0000000000000001KSADASD1Hippo Land
12345931-12-2014EURD71987572gnucash31-12-2014iDEALContract 154546HKU0000000000000002KSADASD2Hippo Land
12346030-12-2014EURC72987573gnucash30-12-2014iDEALContract 154547HKU0000000000000003KSADASD3Hippo Land
12346130-12-2014EURC73987574gnucash30-12-2014iDEALContract 154548HKU0000000000000004KSADASD4Hippo Land
12346230-12-2014EURC74987575gnucash30-12-2014iDEALContract 154549HKU0000000000000005KSADASD5Hippo Land
12346330-12-2014EURD75987576gnucash30-12-2014iDEALContract 154550HKU0000000000000006KSADASD6Hippo Land
12346429-12-2014EURD76987577gnucash29-12-2014iDEALContract 154551HKU0000000000000007KSADASD7Hippo Land
AC
Reply | Threaded
Open this post in threaded view
|

Re: creating QIF file under Dos using AWK

AC
FS needs to be used in a BEGIN block or from the command line.  Also, to
skip the first two lines don't use NR, instead make your own counter,
count off the first two lines and toss them away then begin parsing the
data.

(Note the following should be in its own file and then executed using
the -f option of awk,  you are also missing the type field, and the
order of the fields should be Type, then D, then T, then P and then the
caret).

If you write your script into a script file that is read into awk using
the -f command, you do not need to escape characters except for control
characters (newline, carriage return, etc.)  So the caret/circumflex
would be a simple string as below

BEGIN {FS=","; line=0}
#you said your entries are comma delimited so you should be using
#a comma not a semicolon as the field separator
{
        line++
        if ( line < 3 ) { next }
        else
        {
                print "!Type:Bank"
                print "D" $1
                print "T" $3
                print "P" $2
                print "^"
        }
}

For the following five lines in a sample import file called test.qif
(note that the description is prefixed A, B, C, D and E to distinguish
the lines):

$ cat test.qif
2006-09-27,AONLINE BILL PAYMENT: Bank of Example 123938568,250.00
2006-09-27,BONLINE BILL PAYMENT: Bank of Example 123938568,250.00
2006-09-27,CONLINE BILL PAYMENT: Bank of Example 123938568,250.00
2006-09-27,DONLINE BILL PAYMENT: Bank of Example 123938568,250.00
2006-09-27,EONLINE BILL PAYMENT: Bank of Example 123938568,250.00

I wrote the above script into a file called qifconvert.awk and ran the
script.  The result is below (note I'm using a script file to avoid
having to write shell escape sequences, do this instead of trying to
write your script on the command line):

$ awk -f qifconvert.awk < test.qif
!Type:Bank
D2006-09-27
T250.00
PCONLINE BILL PAYMENT: Bank of Example 123938568
^
!Type:Bank
D2006-09-27
T250.00
PDONLINE BILL PAYMENT: Bank of Example 123938568
^
!Type:Bank
D2006-09-27
T250.00
PEONLINE BILL PAYMENT: Bank of Example 123938568
^


On 2015-03-08 04:46, piggyb wrote:

> Thank for letting me know how to use a external script
> Because I am using a comma separated file, I tried these scripts
>
> /NR <2 {FS =";"}{ print "D" $1; print "P" $2; print "T" $3; print "^"; }
> NR <2 {FS ="\"*;\"*"}{ print "D" $1; print "P" $2; print "T" $3; print "^";
> }/
>
> But now the script reads form the first line and displays a comma in front
> of the bank-number
>
>
> KNAB EXPORT
> BankAcount Transactiondate Valutacode CreditDebet Amount Banknr bankaccount
> date Method Discription Type Machtigingsnummer authorization Number Adres
> 123456 31-12-2014 EUR C 50 987569 gnucash 01-01-2015 iDEAL Contract 154543
> 123457 31-12-2014 EUR D 60 987570 gnucash 31-12-2014 iDEAL Contract 154544
> HKU0000324354537934 KSADASD35342434235 Hippo Land
> 123458 31-12-2014 EUR D 70 987571 gnucash 31-12-2014 iDEAL Contract 154545
> HKU0000000000000001 KSADASD1 Hippo Land
> 123459 31-12-2014 EUR D 71 987572 gnucash 31-12-2014 iDEAL Contract 154546
> HKU0000000000000002 KSADASD2 Hippo Land
> 123460 30-12-2014 EUR C 72 987573 gnucash 30-12-2014 iDEAL Contract 154547
> HKU0000000000000003 KSADASD3 Hippo Land
> 123461 30-12-2014 EUR C 73 987574 gnucash 30-12-2014 iDEAL Contract 154548
> HKU0000000000000004 KSADASD4 Hippo Land
> 123462 30-12-2014 EUR C 74 987575 gnucash 30-12-2014 iDEAL Contract 154549
> HKU0000000000000005 KSADASD5 Hippo Land
> 123463 30-12-2014 EUR D 75 987576 gnucash 30-12-2014 iDEAL Contract 154550
> HKU0000000000000006 KSADASD6 Hippo Land
> 123464 29-12-2014 EUR D 76 987577 gnucash 29-12-2014 iDEAL Contract 154551
> HKU0000000000000007 KSADASD7 Hippo Land
>
>
>
>
>
>
> --
> View this message in context: http://gnucash.1415818.n4.nabble.com/creating-QIF-file-under-Dos-using-AWK-tp4676673p4676720.html
> Sent from the GnuCash - User mailing list archive at Nabble.com.
> _______________________________________________
> 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: creating QIF file under Dos using AWK

Michael DeBusk-2
On Sun, Mar 8, 2015 at 7:02 PM, AC <[hidden email]> wrote:

> BEGIN {FS=","; line=0}
> #you said your entries are comma delimited so you should be using
> #a comma not a semicolon as the field separator
> {
>         line++
>         if ( line < 3 ) { next }
>         else
>         {
>                 print "!Type:Bank"
>                 print "D" $1
>                 print "T" $3
>                 print "P" $2
>                 print "^"
>         }
> }

Is it desirable to have "!Type:Bank" in each entry? AFAIK, it belongs
only at the top of the file.

--
()  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.
AC
Reply | Threaded
Open this post in threaded view
|

Re: creating QIF file under Dos using AWK

AC
On 2015-03-08 16:19, Michael DeBusk wrote:

> On Sun, Mar 8, 2015 at 7:02 PM, AC <[hidden email]> wrote:
>
>> BEGIN {FS=","; line=0}
>> #you said your entries are comma delimited so you should be using
>> #a comma not a semicolon as the field separator
>> {
>>         line++
>>         if ( line < 3 ) { next }
>>         else
>>         {
>>                 print "!Type:Bank"
>>                 print "D" $1
>>                 print "T" $3
>>                 print "P" $2
>>                 print "^"
>>         }
>> }
>
> Is it desirable to have "!Type:Bank" in each entry? AFAIK, it belongs
> only at the top of the file.
>

According to the QIF file format information link that is provided in
the Gnucash wiki (the link goes to Wikipedia:
http://en.wikipedia.org/wiki/QIF) it is required on each transaction.
If not then that isn't clear from the samples.
_______________________________________________
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: creating QIF file under Dos using AWK

Edward Doolittle
Perhaps it would be a good idea to transition from awk to Perl in the
documentation, because Perl is installed alongside GnuCash (for
Finance::Quote).

Here's a 30-second Perl hack to accomplish roughly the same thing as the
awk script:

#!/usr/bin/perl
# usage: csvtoqif.pl < infile.csv > outfile.qif
use strict;
use warnings;

# skip first two input lines
<>;
<>;

print "!Type Bank\n";
while (<>) {
  chomp;
  if (/([^,]*),([^,]*),([^,]*)/) {
    print "D", $1, "\n";
    print "T", $3, "\n";
    print "P", $2, "\n";
    print "^\n";
  }
}

Admittedly it looks like gibberish in parts, and there is room for
improvement (e.g., better CSV processing, correct newline character(s) for
various operating systems). But Perl also has the advantage of a huge
library available for robust CSV processing and correct newline characters
and on and on.


On 8 March 2015 at 17:23, AC <[hidden email]> wrote:

> On 2015-03-08 16:19, Michael DeBusk wrote:
> > On Sun, Mar 8, 2015 at 7:02 PM, AC <[hidden email]> wrote:
> >
> >> BEGIN {FS=","; line=0}
> >> #you said your entries are comma delimited so you should be using
> >> #a comma not a semicolon as the field separator
> >> {
> >>         line++
> >>         if ( line < 3 ) { next }
> >>         else
> >>         {
> >>                 print "!Type:Bank"
> >>                 print "D" $1
> >>                 print "T" $3
> >>                 print "P" $2
> >>                 print "^"
> >>         }
> >> }
> >
> > Is it desirable to have "!Type:Bank" in each entry? AFAIK, it belongs
> > only at the top of the file.
> >
>
> According to the QIF file format information link that is provided in
> the Gnucash wiki (the link goes to Wikipedia:
> http://en.wikipedia.org/wiki/QIF) it is required on each transaction.
> If not then that isn't clear from the samples.
> _______________________________________________
> 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.
>



--
Edward Doolittle
Associate Professor of Mathematics
First Nations University of Canada
1 First Nations Way, Regina SK S4S 7K2

« Toutes les fois que je donne une place vacante, je fais cent mécontents
et un ingrat. »
-- Louis XIV, dans Voltaire, Le Siècle de Louis XIV, Chap. XXVI
_______________________________________________
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: creating QIF file under Dos using AWK

Michael DeBusk-2
In reply to this post by AC
On Sun, Mar 8, 2015 at 7:23 PM, AC <[hidden email]> wrote:

>> Is it desirable to have "!Type:Bank" in each entry? AFAIK, it belongs
>> only at the top of the file.
>
> According to the QIF file format information link that is provided in
> the Gnucash wiki (the link goes to Wikipedia:
> http://en.wikipedia.org/wiki/QIF) it is required on each transaction.
> If not then that isn't clear from the samples.

Now I'm confused.

Here's a wayback machine link to the original QIF spec, no longer on
Intuit's Web site:
http://web.archive.org/web/20100222214101/http://web.intuit.com/support/quicken/docs/d_qif.html

Under the heading of "Required File Formatting," it says, "When
Quicken exports an account register or list, it adds a line to the top
of the file that identifies the type of account or list. Listed below
are the header lines Quicken adds to the exported files:" It goes from
there to give a table of header lines

I've never gotten an error message about this. I can assume you have
never gotten one, either, doing it as you do.

--
()  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: creating QIF file under Dos using AWK

Michael DeBusk-2
In reply to this post by Edward Doolittle
On Sun, Mar 8, 2015 at 11:28 PM, Edward Doolittle
<[hidden email]> wrote:

> Perhaps it would be a good idea to transition from awk to Perl in the
> documentation, because Perl is installed alongside GnuCash (for
> Finance::Quote).

Good idea. Not necessarily to transition, but to give another choice.

> Here's a 30-second Perl hack to accomplish roughly the same thing as the
> awk script:

Nice. Even I can read it. :)

--
()  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.
AC
Reply | Threaded
Open this post in threaded view
|

Re: creating QIF file under Dos using AWK

AC
In reply to this post by Michael DeBusk-2
On 2015-03-08 23:44, Michael DeBusk wrote:

> On Sun, Mar 8, 2015 at 7:23 PM, AC <[hidden email]> wrote:
>
>>> Is it desirable to have "!Type:Bank" in each entry? AFAIK, it belongs
>>> only at the top of the file.
>>
>> According to the QIF file format information link that is provided in
>> the Gnucash wiki (the link goes to Wikipedia:
>> http://en.wikipedia.org/wiki/QIF) it is required on each transaction.
>> If not then that isn't clear from the samples.
>
> Now I'm confused.
>
> Here's a wayback machine link to the original QIF spec, no longer on
> Intuit's Web site:
> http://web.archive.org/web/20100222214101/http://web.intuit.com/support/quicken/docs/d_qif.html
>
> Under the heading of "Required File Formatting," it says, "When
> Quicken exports an account register or list, it adds a line to the top
> of the file that identifies the type of account or list. Listed below
> are the header lines Quicken adds to the exported files:" It goes from
> there to give a table of header lines
>
> I've never gotten an error message about this. I can assume you have
> never gotten one, either, doing it as you do.
>

No idea, I don't use QIFs and I never import data.  I just happen to
write lots of awk scripts (for non-Gnucash purposes) on a regular basis
so I took what was requested (awk script to convert CSV to QIF) and then
followed the exact example listed in the Wikipedia page to generate the
output.  That's why I mentioned that if the type is supposed to be only
a single file header it is not clear from the Wikipedia page which
quotes the QIF spec but then turns around and posts an example
containing the type header per transaction.

If it really is supposed to be just a file header then it's just a
matter of moving the single print statement with the type identifier
from the main block up to the BEGIN block in the script.
_______________________________________________
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: creating QIF file under Dos using AWK

Michael DeBusk-2
On Mon, Mar 9, 2015 at 3:44 AM, AC <[hidden email]> wrote:

>> I've never gotten an error message about this. I can assume you have
>> never gotten one, either, doing it as you do.
>
> No idea, I don't use QIFs and I never import data.  I just happen to
> write lots of awk scripts (for non-Gnucash purposes) on a regular basis

Ah. I've been poking around in awk for some time, dabbling a bit here
and there, never getting serious. People who write awk, sed, perl,
shell, etc., scripts are fascinating to me in the way that magicians
are to little kids. How did he DO that?!

> If it really is supposed to be just a file header then it's just a
> matter of moving the single print statement with the type identifier
> from the main block up to the BEGIN block in the script.

Understood. (I think.) Thanks!

--
()  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: creating QIF file under Dos using AWK

piggyb
In reply to this post by Edward Doolittle
Thanks for the perl file
Can you tell me how I can use it on comma separated file?

"BANKnr12345";"31-12-2014";"EUR";"C";"25,50";"DEBAccount1234";"Bank";"01-01-2015";"Disciption";"Disciption";"";"";"";"";

I have changed the line in to if (/([^";"]*),([^";"]*),([^";"]*)/) { but now only 11 rows get imported
Reply | Threaded
Open this post in threaded view
|

Re: creating QIF file under Dos using AWK

Edward Doolittle
The fast hackish way to fix the regular expression would be to repeat the
block "([^"]*)"; for as many times as there are fields, so for example to
capture 10 active fields as you seem to have in your example the line would
be

if
(/"([^"]*)";"([^"]*)";"([^"]*)";"([^"]*)";"([^"]*)";"([^"]*)";"([^"]*)";"([^"]*)";"([^"]*)";"([^"]*)";/)
{

Then the contents of the first field would be represented by $1, the second
would be $2 ... hmm I don't know offhand what the 10th field would be. Let
me look that up and get back to you.

There are ways to make the regular expression more legible if you care
about maintainability.

HOWEVER, you should know that the regular expression fails if there is a "
inside one of the fields. There *probably* won't be, but you don't know for
certain. What you REALLY want is a Perl module called Text::CSV which is
far more complex and robust than anything I can hack up sitting in front of
my e-mail.

On 9 March 2015 at 04:38, piggyb <[hidden email]> wrote:

> Thanks for the perl file
> Can you tell me how I can use it on comma separated file?
>
>
> "BANKnr12345";"31-12-2014";"EUR";"C";"25,50";"DEBAccount1234";"Bank";"01-01-2015";"Disciption";"Disciption";"";"";"";"";
>
> I have changed the line in to /if (/([^";"]*),([^";"]*),([^";"]*)/) {/ but
> now only 11 rows get imported
>
>
>
> --
> View this message in context:
> http://gnucash.1415818.n4.nabble.com/creating-QIF-file-under-Dos-using-AWK-tp4676673p4676752.html
> Sent from the GnuCash - User mailing list archive at Nabble.com.
> _______________________________________________
> 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.
>



--
Edward Doolittle
Associate Professor of Mathematics
First Nations University of Canada
1 First Nations Way, Regina SK S4S 7K2

« Toutes les fois que je donne une place vacante, je fais cent mécontents
et un ingrat. »
-- Louis XIV, dans Voltaire, Le Siècle de Louis XIV, Chap. XXVI
_______________________________________________
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: creating QIF file under Dos using AWK

Edward Doolittle
The tenth field would be $10 .

On 9 March 2015 at 17:34, Edward Doolittle <[hidden email]>
wrote:

> The fast hackish way to fix the regular expression would be to repeat the
> block "([^"]*)"; for as many times as there are fields, so for example to
> capture 10 active fields as you seem to have in your example the line would
> be
>
> if
> (/"([^"]*)";"([^"]*)";"([^"]*)";"([^"]*)";"([^"]*)";"([^"]*)";"([^"]*)";"([^"]*)";"([^"]*)";"([^"]*)";/)
> {
>
> Then the contents of the first field would be represented by $1, the
> second would be $2 ... hmm I don't know offhand what the 10th field would
> be. Let me look that up and get back to you.
>
> There are ways to make the regular expression more legible if you care
> about maintainability.
>
> HOWEVER, you should know that the regular expression fails if there is a "
> inside one of the fields. There *probably* won't be, but you don't know for
> certain. What you REALLY want is a Perl module called Text::CSV which is
> far more complex and robust than anything I can hack up sitting in front of
> my e-mail.
>
> On 9 March 2015 at 04:38, piggyb <[hidden email]> wrote:
>
>> Thanks for the perl file
>> Can you tell me how I can use it on comma separated file?
>>
>>
>> "BANKnr12345";"31-12-2014";"EUR";"C";"25,50";"DEBAccount1234";"Bank";"01-01-2015";"Disciption";"Disciption";"";"";"";"";
>>
>> I have changed the line in to /if (/([^";"]*),([^";"]*),([^";"]*)/) {/ but
>> now only 11 rows get imported
>>
>>
>>
>> --
>> View this message in context:
>> http://gnucash.1415818.n4.nabble.com/creating-QIF-file-under-Dos-using-AWK-tp4676673p4676752.html
>> Sent from the GnuCash - User mailing list archive at Nabble.com.
>> _______________________________________________
>> 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.
>>
>
>
>
> --
> Edward Doolittle
> Associate Professor of Mathematics
> First Nations University of Canada
> 1 First Nations Way, Regina SK S4S 7K2
>
> « Toutes les fois que je donne une place vacante, je fais cent mécontents
> et un ingrat. »
> -- Louis XIV, dans Voltaire, Le Siècle de Louis XIV, Chap. XXVI
>



--
Edward Doolittle
Associate Professor of Mathematics
First Nations University of Canada
1 First Nations Way, Regina SK S4S 7K2

« Toutes les fois que je donne une place vacante, je fais cent mécontents
et un ingrat. »
-- Louis XIV, dans Voltaire, Le Siècle de Louis XIV, Chap. XXVI
_______________________________________________
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: creating QIF file under Dos using AWK

piggyb
This post was updated on .
In reply to this post by Edward Doolittle
With the help of Google I have created this Perl script

 #!/usr/bin/perl

  use strict;
  use warnings;
  use Text::CSV;

 my $file = "input.csv";
 
 open my $fh, "<", $file or die "$file: $!";

  my $csv = Text::CSV->new ({ binary => 1, auto_diag => 1,sep_char => ";" });
  $csv->column_names ($csv->getline ($fh)); # use header    
  print "!Type:Bank\n";
 
  while (my $row = $csv->getline_hr ($fh)) {
   
   
    my $CreditDebet = $row->{CreditDebet};
       

    print "D".$row->{TransactionDate}."\n";
     
       if ( $CreditDebet eq "C"){
              print "T".$row->{Amount}."\n";          
              }
              elsif ( $CreditDebet eq "D"){
              print "T-".$row->{Amount}."\n";          
              }
 
        print "M".$row->{Disciption}."\n"
    print "N".$row->{PayMethode}."\n";
    print "P".$row->{CounterNumber}."\n";
    print "A".$row->{Adress}."\n";
    print "^\n";
   
      }
  close $fh;

But how can I tell the script that the second row contains the headers?
The first row starts with BANK EXPORT;;;;;;;;;;;;;;

Reply | Threaded
Open this post in threaded view
|

Re: creating QIF file under Dos using AWK

Edward Doolittle
If you want to throw away the first row, the simplest way would be
$csv->getline($fh); just before $csv->column_names(...) . The first getline
would then get a line from the file and just throw it away with no other
effect than moving to the next record in the input file.

To make the script a little more robust (if the bank should ever stop
putting the BANK EXPORT;;; row in), you could try something like if
($csv->getline($fh)->[0] !~ m/BANK EXPORT/) die "Unexpected first row"; I
haven't tested it though.

On 18 March 2015 at 04:23, piggyb <[hidden email]> wrote:

> With the help of Google I have created this Perl script #!/usr/bin/perl
> use
> strict;  use warnings;  use Text::CSV; my $file = "input.csv";  open my
> $fh,
> "<", $file or die "$file: $!";  my $csv = Text::CSV->new ({ binary => 1,
> auto_diag => 1,sep_char => ";" });  $csv->column_names ($csv->getline
> ($fh)); # use header      print "!Type:Bank\n";    while (my $row =
> $csv->getline_hr ($fh)) {            my $CreditDebet = $row->{CreditDebet};
> print "D".$row->{TransactionDate}."\n";               if ( $CreditDebet eq
> "C"){                   print "T".$row->{Amount}."\n";
>             }
> elsif ( $CreditDebet eq "D"){                   print
> "T-".$row->{Amount}."\n";
> }               print "M".$row->{Disciption}."\n"       print
> "N".$row->{PayMethode}."\n";                                    print
> "P".$row->{CounterNumber}."\n";
> print "A".$row->{Adress}."\n";          print "^\n";          }  close
> $fh; But
> how can I tell the script that the second row contains the headers? The
> first row starts with BANK EXPORT;;;;;;;;;;;;;;
>
>
>
> --
> View this message in context:
> http://gnucash.1415818.n4.nabble.com/creating-QIF-file-under-Dos-using-AWK-tp4676673p4676996.html
> Sent from the GnuCash - User mailing list archive at Nabble.com.
> _______________________________________________
> 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.
>



--
Edward Doolittle
Associate Professor of Mathematics
First Nations University of Canada
1 First Nations Way, Regina SK S4S 7K2

« Toutes les fois que je donne une place vacante, je fais cent mécontents
et un ingrat. »
-- Louis XIV, dans Voltaire, Le Siècle de Louis XIV, Chap. XXVI
_______________________________________________
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: creating QIF file under Dos using AWK

piggyb
Good idea to check if the input file doesn't change

I have tried out the script below to check if the header is the same, and it works correctly
But when I change the variable $mystring to $csv->getline($fh)->[0] then it stops to work.

my $mystring;
$mystring = "Blank EXPORT";
if($mystring !~ m/BANK EXPORT/) { print "Unexpected first row"; }
12