We help IT Professionals succeed at work.

How to clean up the converted Json string from non Json ansi characters in Ms Access VBA

Medium Priority
Last Modified: 2020-03-26
Is it possible to Trim the first 4 characters & the second last from the Json string below, the idea here is to remain with pure json, so that if I fail to iterate it , then I can have it printed and posted to the table manually.(Characters not required are  ]    j  and 4c)
Below is the Ms Access VBA code that takes it to text file , but not fully cleaned up.

Text file Export VBA

n = FreeFile()
    Open "C:\Users\chris.hankwembo\Desktop\Leader\test.txt" For Output As #n
    Print #n, strData
    Close #n

Open in new window

Json String

 ]    j{
   "PosVendor": "Nector Prime Accounting Solutions",
   "PosSoftwareVersion": "",
   "PosModel": "Cap-2017",
   "PosSerialNumber": "100100001829",
   "IssueTime": "20200326115321",
   "TransactionType": 0,
   "PaymentMode": 0,
   "SaleType": 0,
   "LocalPurchaseOrder": "",
   "Cashier": "Admin Manager",
   "BuyerTPIN": "",
   "BuyerName": "",
   "BuyerTaxAccountName": "",
   "BuyerAddress": "",
   "BuyerTel": "",
   "OriginalInvoiceCode": "",
   "OriginalInvoiceNumber": "",
   "Memo": "",
   "Items": [
         "ItemId": 1,
         "Description": "Cleaning Materials",
         "BarCode": 19,
         "Quantity": 1,
         "UnitPrice": 56,
         "Discount": 0,
         "TaxLabels": [
         "TotalAmount": 64.96,
         "IsTaxInclusive": true,
         "RRP": 0

Open in new window

Watch Question

John TsioumprisSoftware & Systems Engineer
Distinguished Expert 2019
You can use the Mid Function
FirstTrimmed=Mid (JsonString,5)
This will trim the first 4 characters.
the opposite would be
Left(FirstTrimmed,len(FirstTrimmed )-4) ' I am not sure how many characters you want to trim in the End
Software Tead Lead / Business Analyst / System Analyst / Data Engineer
or in one liner:

json = Mid(json, 5, Len(json) - 8)

Open in new window

and you would need some error handling in case your json string's length is less than 8 chars