Avatar of Mel Brooks
Mel Brooks

asked on 

Identify file type and extraction method

I've been provided a text file that represents data being exported from a pharmacy machine that automatically dispenses medications to a patient. Can anyone tell me if they can identify the named schema for this file.  It's similar to XML, but obviously does not use the same tags.
If this is a standardized schema, are there any SQL server tools already available to parse out the elements for storing in MS SQL data tables.billable-data-example.txt
Microsoft SQL Server

Avatar of undefined
Last Comment
Gustav Brock
Avatar of David Johnson, CD
David Johnson, CD
Flag of Canada image

A Sample data file would be appreciated.
Avatar of lcohan
Flag of Canada image

Can anyone tell me if they can identify the named schema for this file. 
Is it possible to attach a sample file after stripping out/replacing any confidential info in it? 

Avatar of Mel Brooks
Mel Brooks


Sorry about that.  I can't believe I didn't attach that file.  I have edited the question and included the file
it is json

get-content -Path "H:\downloads\billable-data-example.txt" | ConvertFrom-Json

patientId       : 77491e92-05fd-45eb-8375-b39f54f4xxxx
firstName       : John
lastName1       : Doe
dob             : 1958/06/19
rangeStartDate  : 2021/01/01 00:00:00
rangeEndDate    : 2021/02/01 00:00:00
administrations : {@{adminTime=2021/01/01 00:08:00; adminMethod=spencer; doses=System.Object[]}}
prescriptions   : {@{prescriptionId=c166258c-a827-47f0-b51d-1104513a3131; 
                  drugId=445cfab2-a83d-40c0-ac3d-a4c1443eddc6; prescriptionNumber=1995H5Y; 
                  prescriberName=Stanley Tatum}, 
                  drugId=b1d6c8b7-bcb3-4c6b-8800-89fece915536; prescriptionNumber=2010BB0; 
                  prescriberName=Stanley Tatum}, 
                  drugId=61996550-57de-4575-bd43-87f70d48db79; prescriptionNumber=654BH4; 
                  prescriberName=Stanley Tatum}, 
                  drugId=1935bd2c-692e-4594-8418-5907b0d90f36; prescriptionNumber=688JH7; 
                  prescriberName=Susan Strawberry}}
drugs           : {@{drugId=445cfab2-a83d-40c0-ac3d-a4c1443eddc6; din_ndc=02380838; 
                  commercialName=TEVA-LOSARTAN; genericName=Losartan Potassium Tab 25 MG; 
                  strength=25 mg}, @{drugId=b1d6c8b7-bcb3-4c6b-8800-89fece915536; din_ndc=02380684; 
                  commercialName=TARO-CANDESARTAN; genericName=Candesartan Cilexetil Tab 4 MG; 
                  strength=4 mg}, @{drugId=61996550-57de-4575-bd43-87f70d48db79; din_ndc=02381907; 
                  commercialName=AURO-CIPROFLOXACIN; genericName=Ciprofloxacin HCl Tab 250 MG (Base 
                  Equiv); strength=250 mg}, @{drugId=1935bd2c-692e-4594-8418-5907b0d90f36; 
                  din_ndc=02385961; commercialName=RANITIDINE; genericName=Ranitidine HCl Tab 300 
                  MG; strength=300 mg}}

Open in new window

Avatar of Mel Brooks
Mel Brooks


Well I've never worked with Json and so that's why I didn't recognize it.
I don't think the Powershell script is going to do what I want though.  I'm wanting to get the data into a datasheet format.
Either by by repeating the patient demographics and , administrations, and medication properties so that everything falls into a single table or by parsing out the data so that the Patient elements are in a table, administrations are in a table, prescriptions are in a table and drugs are in a table.
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Mel Brooks
Mel Brooks


Wow Gustav.  Looks like you put a lot of effort into this.  If I was only dealing with Microsoft Access, then I would just modify your code and make it work in my application, but I'll be working primarily with MS SQL server.  So before I use what you have, I want to see if there is something simpler with Powershell as David initially suggested.  Here is a powershell line I used that works great to pull the root dynamics into a properly formatted csv file.  Is there anyway to pull the nested data into a csv file as well?  (for simplicity, I renamed my text file)

Get-Content C:\Temp\billable.txt | ConvertFrom-Json | Export-Csv C:\Temp\billable.csv -NoTypeInformation
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

CSV files are basically flat files, that's one of the reasons for having the Json format.

I haven't used Powershell for data handling, but it can do a lot, I'm sure, I just don't know how and if it will be enough for your case - your current structure is not the simplest.
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo