Skip to Main Content
  • Questions
  • Parsing out a field in MS Access that is CSV (JSON) data

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sherry.

Asked: April 03, 2020 - 8:11 pm UTC

Answered by: Connor McDonald - Last updated: April 06, 2020 - 1:08 am UTC

Category: SQL - Version: 1.0

Viewed 100+ times

You Asked

I have a CSV data file that I load into MS Access. No problem doing that. I’ve got it all loaded.

My issue is that one of the records in the file contains a field that contains CSV data (JSON data) which is line item invoice data (ProductData) and it may contain up to 25 line items.

What I’m trying to do is parse out that field into multiple records in a different table all related by the number after “500” which is the transaction number.

Does anyone have any ideas on how to do this in Microsoft Access please?

Sample data:

500,3,30600052763,30128903358,"{""type"": ""reportingData"", ""merStoreId"": ""8135001"", ""productData"": [{""productCode"": ""001"", ""totalAmount"": ""20.00"", ""quantity"": ""16.000"", ""unitPrice"": ""1.25"", ""tax1Amount"": ""6.22""}, {""productCode"": ""102"", ""totalAmount"": ""14.00"", ""quantity"": ""1.000"", ""unitPrice"": ""14.00""}, {""productCode"": ""963"", ""totalAmount"": ""1.00"", ""quantity"": ""1.000"", ""unitPrice"": ""1.00"", ""tax1Amount"": ""0.00""}]}"

I’ve attached a sample of how I would like the table to look based on transaction number 3 above:

Tbl500
Transaction Number Second Field Third Field Type MerStoreID ProductCode TotalAmount Quantity UnitPrice Tax1Amount Tax2Amount
3 30600052763 0128903358 reportingData 8135001 001 20.00 16.000 1.25 6.22
3 30600052763 30128903358 reportingData 8135001 102 14.00 1.000 14.00
3 30600052763 30128903358 reportingData 8135001 963 1.00 1.000 1.00 0.00


and we said...

Sorry, we're not Access experts here. Perhaps try the following

http://www.christiaanheidema.nl/?JSON_functions

(We can't comment on the validity or functionality of any external website)

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.