Extracting strings from a field

[ldaxh33]

Hi,

I need to extract fields from this string (and 100's others like it)

{"currency":"NZD","delivery_confirmation":"SIGNATURE","duty_payment_account":"164497240","freight_charge":0.0,"incoterm":"DDP","label_date":"2017-04-18T12:42:07+00:00","label_format":"ZPL","label_size":"4X7","print_custom_1":"ECOM 66651 / 6900091985","commercial_invoice_signature":"IMAGE_3","parties_to_transaction_are_related":true,"import_federal_tax_id":"40422445L","customs_include_shipping":true,"delivered_duty_paid":true,"print_custom":[{"name":null,"value":"ECOM 66651 / 6900091985","barcode":null}]}

I am looking to get the label_date (just the date, not the T onward in that string) and print_custom_1 fields out but I can't work out how, I have not used access in over 10 years so I am really rusty.

Any help?

Cheers

 

[bob fitz]

You could use an expression like:

=Mid([txt],InStr(1,[txt],"label_date")+13,10)

to return the required date.

Example shown in attached db

 

[plog]

That smells like JSON to me (https://www.w3schools.com/js/js_json_intro.asp). It's a standard data encoding method. So standard, I bet you can find someone who has written a module to handle files like that in Access.

Search this forum for JSON and if that doesn't work, go to google with it.

 

[jdraw]

I agree with plog... has a JSON syntax.
What is the source of the data from which you must extract strings?
I note that this is your first post, so my advice is:
Describe to readers in plain English what you are trying to accomplish. Once readers understand what, they may offer options for HOW to do it in Access.

Good luck.

 

[sxschech]

After a long search, trying out parsers and viewing code examples beyond my understanding, our IT director found a program that I've been using to convert json to csv. They have a free version, which you copy and paste the json string and it converts to a downloadable excel file. I have used it for the last 6 months.
https://json-csv.com/

Recently, I switched to the desktop version, which is a paid version, but now I can automate the process with vba, no more copy and pasting, instead, I click the button and it does all the conversion automatically allowing it to flow into the table without further user intervention. https://json-csv.com/download
Been using that version for about a month now.