Perl csv2qif for CSV exported by GnuCash to import back into GnuCash

[Lewis Balentine]

If you have gotten to importing transaction then you are much further 
along than I am ... I am still stuck on customer records. I exported the 
data from Quickbooks to LibreOffice ODS Spreadsheets (/because much 
massaging of the data is required to go from QB's gazillion fields to 
GNUCash's more modest structure/). I had to stick the 'active/inactive' 
flag in the last line of the address. I exported the data to a CSV file 
(comma delimited, quoted text). When I tried to import it I got nothing. 
The only thing I have found that attempts to work is semicolon 
delimited. Once I got it to import I discovered that it totally ignores 
the 'notes' existence field even though it shown in the import screen. 
That field is missing from the 'reg expression'

^(?<id>[^;]+);(?<company>[^;]*);(?<name>[^;]+);(?<addr1>[^;]+);?(?<addr2>[^;]*);?(?<addr3>[^;]*);?(?<addr4>[^;]*);?(?<phone>[^;]*);?(?<fax>[^;]*);?(?<email>[^;]*);?(?<shipname>[^;]*);?(?<shipaddr1>[^;]*);?(?<shipaddr2>[^;]*);?(?<shipaddr3>[^;]*);?(?<shipaddr4>[^;]*);?(?<shipphone>[^;]*);?(?<shipfax>[^;]*);?(?<shipemail>[^;]*)

should be (/best I can tell/):

^(?<id>[^;]+);(?<company>[^;]*);(?<name>[^;]+);(?<addr1>[^;]+);?(?<addr2>[^;]*);?(?<addr3>[^;]*);?(?<addr4>[^;]*);?(?<phone>[^;]*);?(?<fax>[^;]*);?(?<email>[^;]*);_?(?<notes>[^;]*);_?(?<shipname>[^;]*);?(?<shipaddr1>[^;]*);?(?<shipaddr2>[^;]*);?(?<shipaddr3>[^;]*);?(?<shipaddr4>[^;]*);?(?<shipphone>[^;]*);?(?<shipfax>[^;]*);?(?<shipemail>[^;]*)

With that patched it shows on the import screen but never shows up in 
the data. My guess is that there is another piece missing ..... :>(

I hate to imagine what importing tractions is going to be like. From 
what I read imports is a relatively new feature ... perhaps still 
needing a bit of polish.

Best of luck,

Lewis
Houston, Texas


On 04/30/2016 07:37 PM, Art wrote:
> I am writing a Perl script to do this because the CSV Import from GnuCash v2.6.12 doesn't seem to like the format of its own export. I set the date format to what the CSV has, as well as the locale for the 1,234.56 format vs. comma, but it still doesn't like it. The GnuCash QIF and OFX import functions have always worked well for me, but GnuCash only seems to export transactions to CSV format.
>
> I saw the Dutch banks csv2qif Perl script and a Python one from the GnuCash Wiki, but they don't do this.I only have 105 transactions would take far less time to just reenter that data then to transfer it from the old database I accidentally stuck them in, however, because I'm getting older, I have a feeling I'm going to do this again in future at an accelerated rate, so I think this script will pay off in the long run.
> I am just hoping someone has the script written already or can provide a recipe for more efficiently handling this. Both databases use MySQL for the backend. I would prefer to do it in SQL script, but am not confident data integrity would be preserved as I understand the entity relationships are implicit in GnuCash and not in the MySQL database.
> While writing this, it occurred to me that the backup transaction journals are left behind which can be executed to catch-up databases in case the GnuCash database buys the farm, e.g., gets corrupted but the journal files are available. If that's true, I can just copy the journal files from my old database and execute them against the new database. I think I also recall that the database is serialized which may mean that this may not work because the new database is in a different machine and presumably was created with a different serial number.
> If anyone has any suggestions of what the best course might be for this recovery operation is, I'd like to hear.
> BTW, I tweaked the Excel script version of cvs2qif (which I think had a Libre Office version) to suit my needs about 5-10 years ago, but I'd rather do it in Perl or C++, since I'm mostly Ubuntu Linux based with access to Windows 10 on another server.
> - Art
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at gnucash.org
> 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.