Convert a text file into a JSON file with keys and values

Andy5 · May 12, 2025, 12:11pm

Hello ,
I want to fetch each piece of data from the text file below and create a JSON file, using keys with values. How can I do this? I have attached a small sample file.
Thanks in advance.

Sample file :
Tuesday, April 01, 2025

Patient First Name andy
Patient Middle Initial abc

Patient Last Name defcd
Date of Birth Friday, January 1, 1902

Sex Male

Phone Number (123) 456-7891

1Phone Type Work

Mailing Address
Preferred Language
1011 Ghijk ave
lamnop, qr, 98201
english

Race RTUV

Email ams@abc.org
Do you have a Guarantor? No
Emergency Contact Information
Emergency Contact test test
Emergency Contact Phone Number (000) 000-0000
Emergency Contact Phone Type Other

Emergency Contact’s Relationship to Patient
parent
Insurance Information
PRIMARY INSURANCE INFORMATION
Primary Insurance
test insurance
Primary Subscriber Name andy
Primary Subscriber’s Relationship to self
Patient (If you are the primary
subscriber, please write “self.”)
Primary Subscriber’s Date of Birth Friday, January 1, 1902

Primary Insurance ID # (include alpha asdasda321a
prefix, if applicable)
Primary Group # 123456

Do you have secondary insurance? No

SECONDARY INSURANCE INFORMATION
Patient Employer Information
Employer Name XYZABC

2Employer Phone Number (123) 456-7891

Employer City lamnop
Employer State qr
Reason for Visit

Please choose your marital status: Single
Do you currently or have you ever Yes
used tobacco products?
Please choose your tobacco smoking Former smoker
status:

Type of recreational drugs used: Other

If other, please list: test drug usage

Pharmacy Preference
What is your pharmacy of choice? Dummy
Location (City, State) lamnop, qr

Thnaks
Andy

V_Roboto_V · May 12, 2025, 12:48pm

Hi @Andy5

The previous solution that i provided was not efficient.
Here is my new approach:

Workflow:

Explanation:

Read Text File.
Create a Dictionary variable of type Dictionary(Of String, String), And assign the values using Regex:

dict = New Dictionary(Of String, String) From {
  {"Patient First Name", System.Text.RegularExpressions.Regex.Match(rawText, "(?<=Patient First Name\s).*").Value.Trim},
  {"Patient Middle Initial", System.Text.RegularExpressions.Regex.Match(rawText, "(?<=Patient Middle Initial).*").Value.Trim},
  {"Patient Last Name", System.Text.RegularExpressions.Regex.Match(rawText, "(?<=Patient Last Name).*").Value.Trim},
  {"Mailing Address", System.Text.RegularExpressions.Regex.Match(rawText, "(?<=Mailing Address\s*\n)(?<line1>.*)\n(?<line2>.*)\n(?<language>.*)").Value.Trim}
 }

Note: I have done only 4 Pairs, But I hope you get an idea of what needs to be done?.

Assign the Dictionary to a String in the Json format:

jsonOutput = Newtonsoft.Json.JsonConvert.SerializeObject(dict, Newtonsoft.Json.Formatting.Indented)

Write Text file to .json file

The Output:

NOTE: If you need help/get stuck in Regex of any of these key-values, Do let me know.

If this solves your query, Do mark it as a solution.
Happy Automation

Andy5 · May 12, 2025, 12:57pm

Thans you for response
How will get the Regex for this :

Do you have a Guarantor? No
Emergency Contact Information
Emergency Contact test test
Emergency Contact Phone Number (000) 000-0000
Emergency Contact Phone Type Other

sudster · May 12, 2025, 12:57pm

Hi @Andy5 ,
Can you upload a sanitised sample text file preserving all formatting? Please keep whitespace, tab, new line characters intact.

Andy5 · May 12, 2025, 12:59pm

hi @sudster , its here

Andy5:

Sample file :
Tuesday, April 01, 2025

Patient First Name andy
Patient Middle Initial abc

Patient Last Name defcd
Date of Birth Friday, January 1, 1902

Sex Male

Phone Number (123) 456-7891

1Phone Type Work

Mailing Address
Preferred Language
1011 Ghijk ave
lamnop, qr, 98201
english

Race RTUV

Email ams@abc.org
Do you have a Guarantor? No
Emergency Contact Information
Emergency Contact test test
Emergency Contact Phone Number (000) 000-0000
Emergency Contact Phone Type Other

Emergency Contact’s Relationship to Patient
parent
Insurance Information
PRIMARY INSURANCE INFORMATION
Primary Insurance
test insurance
Primary Subscriber Name andy
Primary Subscriber’s Relationship to self
Patient (If you are the primary
subscriber, please write “self.”)
Primary Subscriber’s Date of Birth Friday, January 1, 1902

Primary Insurance ID # (include alpha asdasda321a
prefix, if applicable)
Primary Group # 123456

Do you have secondary insurance? No

SECONDARY INSURANCE INFORMATION
Patient Employer Information
Employer Name XYZABC

2Employer Phone Number (123) 456-7891

Employer City lamnop
Employer State qr
Reason for Visit

Please choose your marital status: Single
Do you currently or have you ever Yes
used tobacco products?
Please choose your tobacco smoking Former smoker
status:

Type of recreational drugs used: Other

If other, please list: test drug usage

Pharmacy Preference
What is your pharmacy of choice? Dummy
Location (City, State) lamnop, qr

sudster · May 12, 2025, 1:02pm

Sorry @Andy5 , please upload (attach) as a text file, don’t paste into an html page. Please remember to preserve original formatting.

Andy5 · May 12, 2025, 1:07pm

hi @sudster ,please see attched file
New Text Document 1.txt (1.5 KB)
attched file

sudster · May 12, 2025, 1:11pm

@Andy5 , Looking at the file content, it doesn’t look like the original file with formatting intact. Did someone edit this? or copy pasted from html into the text file?

What I’m trying to get at is, sometimes there’s a very easy way to split keys and values if the original formatting was designed properly. I’m guessing you’ll be doing this the hard way

Andy5 · May 12, 2025, 1:14pm

Thank you for your time but i have only this.

sudster · May 12, 2025, 1:47pm

Here’s my solution. This is very similar to the previous solution, but a bit easier.

V_Roboto_V · May 12, 2025, 2:38pm

Hi @Andy5

The regex will be:

(?<=Do you have a Guarantor\?).*

(?<=Emergency Contact Phone Number).*

(?<=Emergency Contact Phone Type).*

Andy5 · May 12, 2025, 3:47pm

Can you please send me Xaml ?

Andy5 · May 12, 2025, 3:48pm

Thansk you will let you know if i face any challnges

sudster · May 13, 2025, 3:23am

Hi @Andy5 , could you please build it yourself? If you cannot, then I can give you the xaml file. I’ve made some modifications to make it easier - somewhat separating logic from data; this way you can just edit the data file rather than changing the process.

Key	Regex	Modifiers
Patient First Name	^Patient\sFirst\sName\s(?<val>.+)$	i,m
Patient Middle Initial	^Patient\sMiddle\sInitial\s(?<val>.+)$	i,m
Patient Last Name	^Patient\sLast\sName\s(?<val>.+)$	i,m
Date of Birth	^Date\sof\sBirth\s(?<val>.+day,\s.+\s\d{1,2},\s\d{4})\s$	i,m
Sex	^Sex\s(?<val>.+)\s$	i,m
Phone Number	^Phone\sNumber\s(?<val>.+)$	i,m
1Phone Type	^1Phone\sType\s(?<val>.+)$	i,m
Mailing Address	Mailing Address\r\nPreferred Language\r\n(?<val>.+)\r\n\w+[\r\n]+Race	i,s,m
Preferred Language	Preferred Language\r\n.+\r\n(?<val>\w+)[\r\n]+Race	i,s,m
Race	^Race\s(?<val>.+)$	i,m
Email	^Email\s(?<val>.+@.+.\w{2,})\s$	i,m

Save this table as Regex_Data.xlsx

Then follow this sequence:

Result:

system · May 16, 2025, 3:24am

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Convert mail text into Json format Studio studio , regex , question , variables_management , linq , linq-query , reqex , regular-expression	7	159	June 26, 2024
Regex to extract data from string and make it json format Studio studio , regex , question , variables_management , regex-extractor	8	328	June 25, 2024
Extract the information from the Json file // Want to extract the value of DOB Studio studio , question , command_palette	9	350	July 26, 2023
Mail json to JSON Studio studio , question , activities_panel	1	34	November 3, 2024
How to convert text file to json file Activities uiautomation , studio	10	5054	May 4, 2022

Convert a text file into a JSON file with keys and values

Related topics