Converting specific TAG File Format (Sage Accounting) to CSV or XML

Deploying to Windows? Utilizing VB Script execution? This is the place to ask Windows-specific questions.

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller, robinmiller

Post Reply
Tate83
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 30
Joined: Fri Mar 01, 2013 1:11 am

Converting specific TAG File Format (Sage Accounting) to CSV or XML

Post by Tate83 » Thu Feb 27, 2020 4:12 pm

Hi everyone,

been a while.. After a few years I have another little project that I would like to handle in Livecode. Before I code away I wanted to see if anyone has done that before or maybe has some good advice.

What I need to do:
My client gets regular accounting files from a system in Sage 50 (Switzerland) format. The call it TAF or BeamNT and it's quite ugly.
To import it to my accounting system I need either a proper CSV file or an XML.

Example

Code: Select all

{Blg Date=14.06.2019 Orig=0 MType=1
 {Bk AccId=6940 Type=0 CAcc=1021 ValNt=22.61 ValFW=22.54 Text="Vergütung: I_06.06.2019" }
 {Bk AccId=1021 Type=1 CAcc=6940 ValNt=22.61 ValFW=22.54 Text="Vergütung: I_06.06.2019" }
}
{Blg Date=21.06.2019 Orig=0 MType=2
 {Bk AccId=2001 Type=0 CAcc=Div ValNt=80858.15 ValFW=81369.10 Text="OP-Ausgleich Ultimate " Text2=" B.V.B.A." }
 {Bk AccId=2001 Type=0 CAcc=2001 ValNt=19479.20 ValFW=19552.25 Text="OP-Ausgleich Ultimate " Text2=" B.V.B.A." }
 {Bk AccId=2001 Type=0 CAcc=2001 ValNt=60188.22 ValFW=60446.40 Text="OP-Ausgleich Ultimate " Text2=" B.V.B.A." }
 {Bk AccId=2001 Type=0 CAcc=2001 ValNt=47663.42 ValFW=47881.05 Text="OP-Ausgleich Ultimate " Text2=" B.V.B.A." }
 {Bk AccId=2001 Type=0 CAcc=2001 ValNt=54286.33 ValFW=54588.85 Text="OP-Ausgleich Ultimate " Text2=" B.V.B.A." }
 {Bk AccId=2001 Type=0 CAcc=2001 ValNt=42969.75 ValFW=43395.20 Text="OP-Ausgleich Ultimate " Text2=" B.V.B.A." }
 {Bk AccId=1021 Type=1 CAcc=2001 ValNt=304409.38 ValFW=307232.85 Text="Bank ZKB USD (aus) Di" Text2="amonds B.V.B.A., Antwe" }
 {Bk AccId=4296 Type=1 CAcc=2001 ValNt=1035.69 Text="Kursgewinn Ultimate B" Text2=".V.B.A." }
}
{Blg Date=26.06.2019 Orig=0 MType=2
 {Bk AccId=1100 Type=0 CAcc=Div ValNt=119.55 Text="Rg Torres, Würenlos:  / I/Best" Text2="ellung per Telefon" }
 {Bk AccId=3200 Type=1 CAcc=1100 TaxId=USt77 ValBt=118.47 MkTxB=1 Text="Rg Torres, Würenlos:  / I/Best" Text2="ellung per Telefon" }
 {Bk AccId=3292 Type=1 CAcc=1100 TaxId=USt77 ValBt=1.08 MkTxB=1 Text="Rg Torres, Würenlos:  / I/Best" Text2="ellung per Telefon" }
}
Looking at this, I can see quite some issues
- "Spaces" used to separate information, but also within the text where spaces are a nice thing
- order of different data is different, e.g. TaxID in last sample doesn't show up in the others
- don't know yet, if all "Type" are necessary, to me it looks like lines with Type=0 are probably not need for simple bookings
- no information on what currency those ValFW (foreign currency) bookings actually are, must be deducted from the accounts currency (2001) in the software
- other stuff..

What would help me greatly is a result like this as a CSV, I can then use this csv with an existing converter to map accounts and taxCodes to my System

Code: Select all

id,date,acc,cacc,valFW,ValNt,TaxID,text1,text2,currency
1,14.6.2019,6940,1020,22.61,22.54,"Vergütung: I_06.06.2019",,"USD"
2...
"id" would be a manual id 1 to n to mark the bookings so I can later have to collective bookings together. my example would have 3 ids for those three bookings but with the second ID used on quite a few lines.

Your input is very much appreciated!
Thanks Pascal

FourthWorld
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 7424
Joined: Sat Apr 08, 2006 7:05 am
Location: Los Angeles
Contact:

Re: Converting specific TAG File Format (Sage Accounting) to CSV or XML

Post by FourthWorld » Thu Feb 27, 2020 6:03 pm

Curious about TAF, I did a quick search and found this:
https://onlinehelp.sageschweiz.ch/Porta ... at_neu.pdf
...which includes this gem:
Why another format? The CSV (comma separated) format used by the accounting System of Sage 50 and many third party vendors up to this point has some serious limitations and is not easily extendable. This import format should make it possible to extend the database of the accounting system, adding newfields, yet still being able to import the older import files. Since there’s no suitable ASCII format for databases we have designed our own proprierty format.
When a vendor tells me I can trust them to handle all my mission-critical data, yet are somehow unfamiliar with XML which has been around for more than 20 years, I get concerned. ;)

You're right. The format is insane. And by succumbing to the temptation to add yet-another-format to a world with too many already, they've made the one choice that guarantees any integration efforts will be more expensive than needed.

Rather than reinforcing the insanity, would it be possible for your client to request those files in CSV or other common format? Sage 50 claims to offer CSV as an export option. (CSV is also a stupid format, but for better or for worse it's common enough that most devs have to deal with it sooner or later, and most languages have robust parsers for it - an LC parser is here.)

If you're stuck working with TAF we can help you write a handler for it, but you're right: it's a stupid format that will be cumbersome to write and slow to execute compared to common formats for which ample tooling already exists.
Richard Gaskin
Community volunteer LiveCode Community Liaison

LiveCode development, training, and consulting services: Fourth World Systems: http://FourthWorld.com
LiveCode User Group on Facebook : http://FaceBook.com/groups/LiveCodeUsers/

bn
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 3345
Joined: Sun Jan 07, 2007 9:12 pm
Location: Bochum, Germany

Re: Converting specific TAG File Format (Sage Accounting) to CSV or XML

Post by bn » Fri Feb 28, 2020 2:31 am

Hi Pascal,

try the attached stack. It should get you started. It should be easy to add other categories if needed. See the code and the comments.

I had to convert commas into colons because the text portions contain commas.


Kind regards
Bernd
Attachments
Sage TAF converter to CSV.livecode.zip
(2.92 KiB) Downloaded 20 times

Tate83
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 30
Joined: Fri Mar 01, 2013 1:11 am

Re: Converting specific TAG File Format (Sage Accounting) to CSV or XML

Post by Tate83 » Fri Feb 28, 2020 9:22 am

Oh wow, thanks guys for your quick answers! :D
I will have a look at the weekend

Tate83
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 30
Joined: Fri Mar 01, 2013 1:11 am

Re: Converting specific TAG File Format (Sage Accounting) to CSV or XML

Post by Tate83 » Fri Feb 28, 2020 8:25 pm

Holy moly, Bernd

Thanks a lot, that is a looong way towards what I need!
I will need to do some minor adjustments and get the ID to span over the whole booking set (so like only 3 id's in my example) but again, thanks a lot!

Also thank you Richard, I will remember that if we have another and larger project that I cannot do myself!

This will allow my client to still provide the sage-formatted file from his system (not Sage, another one where it is too complicated/expensive to change export formats to our Abacus-XML-Standard) and I can transform that to some beautiful XML - might even think about expanding Bernds stack to do everything in there instead of another converter afterwards.. Because CSV (and the more ugly stuff) needs to die ;-)

Have a great weekend!
Once again blown away by the LC community, seems it hasn't changed too much since 2015 or so

Pascal

richmond62
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 4140
Joined: Fri Feb 19, 2010 10:17 am
Location: Bulgaria

Re: Converting specific TAG File Format (Sage Accounting) to CSV or XML

Post by richmond62 » Fri Feb 28, 2020 9:10 pm

I see that you can download trial software here:

https://www.sage.com/en-gb/shop/freetrials.aspx

On this page it is made explicitly clear that Sage can export to both CSV and XML files:

https://my.sage.co.uk/public/help/askar ... leid=12591

Tate83
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 30
Joined: Fri Mar 01, 2013 1:11 am

Re: Converting specific TAG File Format (Sage Accounting) to CSV or XML

Post by Tate83 » Fri Feb 28, 2020 9:19 pm

Yes, but there's a ton of different Sage accounting software, in Switzerland we have at least 3 (all bought, so no common code base).
And my source is yet another one that previously was imported to Sage but now will be imported to another Abacus.. easier staying with Sage format and converting it than changing the export..

richmond62
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 4140
Joined: Fri Feb 19, 2010 10:17 am
Location: Bulgaria

Re: Converting specific TAG File Format (Sage Accounting) to CSV or XML

Post by richmond62 » Fri Feb 28, 2020 9:22 pm

You state:
To import it to my accounting system I need either a proper CSV file or an XML.
Can your client not send you CSV or XML exported directly from their Sage version?

Tate83
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 30
Joined: Fri Mar 01, 2013 1:11 am

Re: Converting specific TAG File Format (Sage Accounting) to CSV or XML

Post by Tate83 » Fri Feb 28, 2020 9:29 pm

unfortunately this is all I can get without them having to change quite a bit of their software (it's not something by sage, but it does export in sage format)

Thierry
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 697
Joined: Wed Nov 22, 2006 3:42 pm
Location: France
Contact:

Re: Converting specific TAG File Format (Sage Accounting) to CSV or XML

Post by Thierry » Mon Mar 02, 2020 10:23 am

Hi Tate,

Your problem is a good candidate for regular expressions,
and more precisely for sunnYrex: https://sunny-tdz.com/livecode/sunnyrex

Your datas are consistent and well-formed even if they appear not to be so at a first glance :wink:

As an exercise,
here is 30 lines of code I've done in one hour in a rainy sunday afternoon...

Code: Select all

constant rexBLG = "(?msi)(?|^{BLG (Date=[^\t ]+).*?\n|^\s*{BK\s+(.*?)}$)"
constant rexKV = "(?msi)\b([^=]+)=(?|([\x22][^\x22]+[\x22]) +|([^\s]+) *)(\n)?"
local labelCols = "id,date,accID,CAcc,valFW,ValNt,TaxID,text,text2"
local currentDate,aRow, idCounter

on convertBlgBk
   put 1 into idCounter
   get sunnYextract(fld "fSource",rexBLG,"\1\n",rText)
   get sunnYextract(rText,rexKV,"?{printKV \1 \2 \3}",rText)
   replace comma with tab in labelCols
   put labelCols &cr&cr& rText into fld "fTableTdz"
end convertBlgBk

on printKV K,V,sameRow
   local T
   set the itemdel to tab
   if "Date" is K then
      put V into currentDate
      return empty
   else if sameRow is not empty then
      put idCounter into item 1 of aRow
      add 1 to idCounter
      put V into item itemoffset(K, labelCols) of aRow
      put currentDate into item itemoffset("Date", labelCols) of aRow
      put aRow &CR into T
      put empty into aRow
      return T
   else
      put V into item itemoffset(K, labelCols) of aRow
      return empty
   end if
end printKV
and got:

sunnYscrrenshot 2020-03-02 à 10.07.06.png


and about speed,
the whole process is around 15 ms on a Mac mini (end 2012);
- 8 ms of pure computation and
- 7 ms for feeding the field object: put labelCols &cr&cr& rText into fld "fTableTdz"


Regards,

Thierry
Regex LiveCode sunnYrex
https://sunny-tdz.com

Post Reply

Return to “Windows”