Can I populate an array from data in this text file, using a WIndows batch file?

Question

A client I work with changed EHRs, and the new EHR changes the format of the text which needs to pe imported into their billing system.

Below is the new file format:

"Patient Class","Hosp Acct","MRN","SSN","Patient Name","Pt Suffix","DOB","Sex","Marital Status","Street Address","City","State","ZIP Code","Phone","Pt. E-mail Address","Patient Employer","Employer Address","Emp City","Emp State","Emp ZIP Code","Emp Phone","Guarantor First","Guarantor Last","Guarantor Middle","Guarantor Relation","Guarantor Address","Guar City","Guar State","Guar ZIP","Primary Plan","Primary Subsc ID","Primary CVG Sub Name","Primary Cvg Sub Sex","Primary Cvg Sub DOB","Primary Cvg Mem Rel to Sub","Primary Cvg Sub Address","Primary Cvg Sub City","Primary Cvg Sub State","Primary CVG Sub ZIP","Primary CVG Group Num","Cvg 1 Auth Num","Primary CVG Address 1","Primary CVG City","Primary CVG State","Primary CVG ZIP","Primary Cvg Sub Phone","Secondary Plan","Secondary Subsc ID","Secondary CVG Sub Name","Secondary Cvg Sub Sex","Secondary Cvg Sub DOB","Secondary Cvg Mem Rel to Sub","Secondary Cvg Sub Address","Secondary Cvg Sub City","Secondary Cvg Sub State","Secondary CVG Sub ZIP","Secondary CVG Group Num","Cvg 2 Auth Num","Secondary CVG Address 1","Secondary CVG City","Secondary CVG State","Secondary CVG ZIP","Secondary Cvg Sub Phone","Tertiary Plan","Tertiary Subsc ID","Tertiary CVG Sub Name","Tertiary Cvg Sub Sex","Tertiary Cvg Sub DOB","Tertiary Cvg Mem Rel to Sub","Tertiary Cvg Sub Address","Tertiary Cvg Sub City","Tertiary Cvg Sub State","Tertiary CVG Group Num","Cvg 3 Auth Num","Tertiary CVG Address 1","Tertiary CVG City","Tertiary CVG State","Tertiary CVG ZIP","Tertiary Cvg Sub Phone","Quaternary Payor","Quaternary Subsc ID","Quat Cvg Subscr Nm","Quat Cvg Subscr Sex","Quat Cvg Subscr DOB","Quat Cvg Subscr Rel to Pt","Quat Cvg Subscr Addr","Quat Cvg Subscr City","Quat Cvg Subscr State","Quat Cvg Subscr ZIP Code","Quat Cvg Subscr Phone Num","Quat Cvg Grp Num","Cvg 4 Auth Num","Quat Cvg Addr Ln 1","Quat Cvg City","Quat Cvg State","Quat Cvg ZIP Code","Quat Cvg Phone Num"

The client's Billing Software is set up to read a .txt file that is pipe delimited, for example:

EncProv|Hospital_Patient_Account_Number|Medical_Record_Number|Surgery_Date|Patient_Last_Name|Patient_First_Name|Patient_Middle_Initial|Patient_Social_Security_Number|Patient_Date_Of_Birth|Patient_Address|Patient_Address_Line_2|Patient_City|Patient_State|Patient_Zip_Code|Patient_Country|Patient_Phone_Number|Patient_Work_Phone_Number|Patient_Work_Phone_Extension|Patient_Gender|Patient_Marital_Status_Code|Patient_Type_Code|Patient_Type_Description|Patient_Admission_Date|Patient_Discharge_Date|Patient_Employer_Name|Patient_Empoyer_Address|Patient_Employer_City|Patient_Employer_State|Patient_Employer_Zip|Patient_Employer_Country|Guarantor_Patient_Relationship_To_Gurantor_Code|Guarantor_Patient_Relationship_To_Guarantor_Description|Guarantor_Last_Name|Guarantor_First_Name|Guarantor_Middle_Intitial|Guarantor_Address|Guarantor_Address_Line_2|Guarantor_City|Guarantor_State|Guarantor_Zip_Code|Guarantor_Country|Guarantor_Social_Security_Number|Guarantor_Phone_Number|Guarantor_Work_Phone_Number|Guarantor_Work_Phone_Extension|Guarantor_Gender|Guarantor_Date_Of_Birth|Guarantor_Employer_Name|Guarantor_Empoyer_Address|Guarantor_Employer_City|Guarantor_Employer_State|Guarantor_Employer_Zip|Guarantor_Employer_Country|Primary_Hospital_Carrier_Code|Primary_Carrier_Name|Primary_Carrier_Mnemonic|Primary_Carrier_Address|Primary_Carrier_Address_Line_2|Primary_Carrier_City|Primary_Carrier_State|Primary_Carrier_Zip_Code|Primary_Carrier_Policy_Number|Primary_Carrier_Group_Number|Primary_Carrier_Patient_Relationship_To_Subscriber_Code|Primary_Carrier_Patient_Relationship_To_Subscriber_Desc|Primary_Carrier_Policy_Holder_Last_Name|Primary_Carrier_Policy_Holder_First_Name|Primary_Carrier_Policy_Holder_Middle_Initial|Primary_Carrier_Policy_Holder_Address|Primary_Carrier_Policy_Holder_Address_Line_2|Primary_Carrier_Policy_Holder_City|Primary_Carrier_Policy_Holder_State|Primary_Carrier_Policy_Holder_Zip_Code|Primary_Carrier_Policy_Holder_Country|Primary_Carrier_Policy_Holder_Social_Security_Number|Primary_Carrier_Policy_Holder_Phone_Number|Primary_Carrier_Policy_Holder_Work_Phone_Number|Primary_Carrier_Policy_Holder_Gender|Primary_Carrier_Policy_Holder_Date_Of_Birth|Primary_Carrier_Policy_Holder_Employer_Name|Primary_Carrier_Policy_Holder_Empoyer_Address|Primary_Carrier_Policy_Holder_Employer_City|Primary_Carrier_Policy_Holder_Employer_State|Primary_Carrier_Policy_Holder_Employer_Zip|Primary_Carrier_Policy_Holder_Employer_Country|Secondary_Hospital_Carrier_Code|Secondary_Carrier_Name|Secondary_Carrier_Mnemonic|Secondary_Carrier_Address|Secondary_Carrier_Address_Line_2|Secondary_Carrier_City|Secondary_Carrier_State|Secondary_Carrier_Zip_Code|Secondary_Carrier_Policy_Number|Secondary_Carrier_Group_Number|Secondary_Carrier_Patient_Relationship_To_Subscriber_Code|Secondary_Carrier_Patient_Relationship_To_Subscriber_Desc|Secondary_Carrier_Policy_Holder_Last_Name|Secondary_Carrier_Policy_Holder_First_Name|Secondary_Carrier_Policy_Holder_Middle_Initial|Secondary_Carrier_Policy_Holder_Address|Secondary_Carrier_Policy_Holder_Address_Line_2|Secondary_Carrier_Policy_Holder_City|Secondary_Carrier_Policy_Holder_State|Secondary_Carrier_Policy_Holder_Zip_Code|Secondary_Carrier_Policy_Holder_Country|Secondary_Carrier_Policy_Holder_Social_Security_Number|Secondary_Carrier_Policy_Holder_Phone_Number|Secondary_Carrier_Policy_Holder_Work_Phone_Number|Secondary_Carrier_Policy_Holder_Gender|Secondary_Carrier_Policy_Holder_Date_Of_Birth|Secondary_Carrier_Policy_Holder_Employer_Name|Secondary_Carrier_Policy_Holder_Empoyer_Address|Secondary_Carrier_Policy_Holder_Employer_City|Sedondary_Carrier_Policy_Holder_Employer_State|Secondary_Carrier_Policy_Holder_Employer_Zip|Secondary_Carrier_Policy_Holder_Employer_Country|Tertiary_Hospital_Carrier_Code|Tertiary_Carrier_Name|Tertiary_Carrier_Mnemonic|Tertiary_Carrier_Address|Tertiary_Carrier_Address_Line_2|Tertiary_Carrier_City|Tertiary_Carrier_State|Tertiary_Carrier_Zip_Code|Tertiary_Carrier_Policy_Number|Tertiary_Carrier_Group_Number|Tertiary_Carrier_Patient_Relationship_To_Subscriber_Code|Tertiary_Carrier_Patient_Relationship_To_Subscriber_Desc|Tertiary_Carrier_Policy_Holder_Last_Name|Tertiary_Carrier_Policy_Holder_First_Name|Tertiary_Carrier_Policy_Holder_Middle_Initial|Tertiary_Carrier_Policy_Holder_Address|Tertiary_Carrier_Policy_Holder_Address_Line_2|Tertiary_Carrier_Policy_Holder_City|Tertiary_Carrier_Policy_Holder_State|Tertiary_Carrier_Policy_Holder_Zip_Code|Tertiary_Carrier_Policy_Holder_Country|Tertiary_Carrier_Policy_Holder_Social_Security_Number|Tertiary_Carrier_Policy_Holder_Phone_Number|Tertiary_Carrier_Policy_Holder_Work_Phone_Number|Tertiary_Carrier_Policy_Holder_Gender|Tertiary_Carrier_Policy_Holder_Date_Of_Birth|Tertiary_Carrier_Policy_Holder_Employer_Name|Tertiary_Carrier_Policy_Holder_Empoyer_Address|Tertiary_Carrier_Policy_Holder_Employer_City|Tertiary_Carrier_Policy_Holder_Employer_State|Tertiary_Carrier_Policy_Holder_Employer_Zip|Tertiary_Carrier_Policy_Holder_Employer_Country|Rendering_Performing_Doctor_Last_Name|Rendering_Performing_Doctor_First_Name|Rendering_Performing_Doctor_Middle_Initial|Rendering_Performing_Doctor_NPI_Number

I want to write a batch file that converts the comma and quote separated values to the pipe delimited values.

If I use an array, I can easily re-order the data as needed to match the original file.

It seems like I should be able to write a batch file that can take the data that is between the the quotation marks in the first text file, and populate an array with it.

Then all I would have to do is to output the data in the order that I need by outputting the data from the array like so:

echo off

call :sub >output.txt
:sub

set a[0]=1 
set a[1]=2 
set a[2]=3 
echo ^| %a[0]% ^| %a[1]% ^| %a[2]%

exit /b

But, how would I populate the array using values separated by the quotation marks in the first text file above?

I've tried to find examples of importing lines of a text file into a batch file, but I've found nothing that only takes the variable from in between quotation marks like the file I've been given.

Answer 1

@ECHO OFF
SETLOCAL ENABLEDELAYEDEXPANSION 

SET "newformat="Patient Class","Hosp Acct","MRN","SSN","Patient Name","Pt Suffix","DOB","Sex","Marital Status","Street Address","City","State","ZIP Code","Phone","Pt. E-mail Address","Patient Employer","Employer Address","Emp City","Emp State","Emp ZIP Code","Emp Phone","Guarantor First","Guarantor Last","Guarantor Middle","Guarantor Relation","Guarantor Address","Guar City","Guar State","Guar ZIP","Primary Plan","Primary Subsc ID","Primary CVG Sub Name","Primary Cvg Sub Sex","Primary Cvg Sub DOB","Primary Cvg Mem Rel to Sub","Primary Cvg Sub Address","Primary Cvg Sub City","Primary Cvg Sub State","Primary CVG Sub ZIP","Primary CVG Group Num","Cvg 1 Auth Num","Primary CVG Address 1","Primary CVG City","Primary CVG State","Primary CVG ZIP","Primary Cvg Sub Phone","Secondary Plan","Secondary Subsc ID","Secondary CVG Sub Name","Secondary Cvg Sub Sex","Secondary Cvg Sub DOB","Secondary Cvg Mem Rel to Sub","Secondary Cvg Sub Address","Secondary Cvg Sub City","Secondary Cvg Sub State","Secondary CVG Sub ZIP","Secondary CVG Group Num","Cvg 2 Auth Num","Secondary CVG Address 1","Secondary CVG City","Secondary CVG State","Secondary CVG ZIP","Secondary Cvg Sub Phone","Tertiary Plan","Tertiary Subsc ID","Tertiary CVG Sub Name","Tertiary Cvg Sub Sex","Tertiary Cvg Sub DOB","Tertiary Cvg Mem Rel to Sub","Tertiary Cvg Sub Address","Tertiary Cvg Sub City","Tertiary Cvg Sub State","Tertiary CVG Group Num","Cvg 3 Auth Num","Tertiary CVG Address 1","Tertiary CVG City","Tertiary CVG State","Tertiary CVG ZIP","Tertiary Cvg Sub Phone","Quaternary Payor","Quaternary Subsc ID","Quat Cvg Subscr Nm","Quat Cvg Subscr Sex","Quat Cvg Subscr DOB","Quat Cvg Subscr Rel to Pt","Quat Cvg Subscr Addr","Quat Cvg Subscr City","Quat Cvg Subscr State","Quat Cvg Subscr ZIP Code","Quat Cvg Subscr Phone Num","Quat Cvg Grp Num","Cvg 4 Auth Num","Quat Cvg Addr Ln 1","Quat Cvg City","Quat Cvg State","Quat Cvg ZIP Code","Quat Cvg Phone Num""
SET "oldformat=EncProv|Hospital_Patient_Account_Number|Medical_Record_Number|Surgery_Date|Patient_Last_Name|Patient_First_Name|Patient_Middle_Initial|Patient_Social_Security_Number|Patient_Date_Of_Birth|Patient_Address|Patient_Address_Line_2|Patient_City|Patient_State|Patient_Zip_Code|Patient_Country|Patient_Phone_Number|Patient_Work_Phone_Number|Patient_Work_Phone_Extension|Patient_Gender|Patient_Marital_Status_Code|Patient_Type_Code|Patient_Type_Description|Patient_Admission_Date|Patient_Discharge_Date|Patient_Employer_Name|Patient_Empoyer_Address|Patient_Employer_City|Patient_Employer_State|Patient_Employer_Zip|Patient_Employer_Country|Guarantor_Patient_Relationship_To_Gurantor_Code|Guarantor_Patient_Relationship_To_Guarantor_Description|Guarantor_Last_Name|Guarantor_First_Name|Guarantor_Middle_Intitial|Guarantor_Address|Guarantor_Address_Line_2|Guarantor_City|Guarantor_State|Guarantor_Zip_Code|Guarantor_Country|Guarantor_Social_Security_Number|Guarantor_Phone_Number|Guarantor_Work_Phone_Number|Guarantor_Work_Phone_Extension|Guarantor_Gender|Guarantor_Date_Of_Birth|Guarantor_Employer_Name|Guarantor_Empoyer_Address|Guarantor_Employer_City|Guarantor_Employer_State|Guarantor_Employer_Zip|Guarantor_Employer_Country|Primary_Hospital_Carrier_Code|Primary_Carrier_Name|Primary_Carrier_Mnemonic|Primary_Carrier_Address|Primary_Carrier_Address_Line_2|Primary_Carrier_City|Primary_Carrier_State|Primary_Carrier_Zip_Code|Primary_Carrier_Policy_Number|Primary_Carrier_Group_Number|Primary_Carrier_Patient_Relationship_To_Subscriber_Code|Primary_Carrier_Patient_Relationship_To_Subscriber_Desc|Primary_Carrier_Policy_Holder_Last_Name|Primary_Carrier_Policy_Holder_First_Name|Primary_Carrier_Policy_Holder_Middle_Initial|Primary_Carrier_Policy_Holder_Address|Primary_Carrier_Policy_Holder_Address_Line_2|Primary_Carrier_Policy_Holder_City|Primary_Carrier_Policy_Holder_State|Primary_Carrier_Policy_Holder_Zip_Code|Primary_Carrier_Policy_Holder_Country|Primary_Carrier_Policy_Holder_Social_Security_Number|Primary_Carrier_Policy_Holder_Phone_Number|Primary_Carrier_Policy_Holder_Work_Phone_Number|Primary_Carrier_Policy_Holder_Gender|Primary_Carrier_Policy_Holder_Date_Of_Birth|Primary_Carrier_Policy_Holder_Employer_Name|Primary_Carrier_Policy_Holder_Empoyer_Address|Primary_Carrier_Policy_Holder_Employer_City|Primary_Carrier_Policy_Holder_Employer_State|Primary_Carrier_Policy_Holder_Employer_Zip|Primary_Carrier_Policy_Holder_Employer_Country|Secondary_Hospital_Carrier_Code|Secondary_Carrier_Name|Secondary_Carrier_Mnemonic|Secondary_Carrier_Address|Secondary_Carrier_Address_Line_2|Secondary_Carrier_City|Secondary_Carrier_State|Secondary_Carrier_Zip_Code|Secondary_Carrier_Policy_Number|Secondary_Carrier_Group_Number|Secondary_Carrier_Patient_Relationship_To_Subscriber_Code|Secondary_Carrier_Patient_Relationship_To_Subscriber_Desc|Secondary_Carrier_Policy_Holder_Last_Name|Secondary_Carrier_Policy_Holder_First_Name|Secondary_Carrier_Policy_Holder_Middle_Initial|Secondary_Carrier_Policy_Holder_Address|Secondary_Carrier_Policy_Holder_Address_Line_2|Secondary_Carrier_Policy_Holder_City|Secondary_Carrier_Policy_Holder_State|Secondary_Carrier_Policy_Holder_Zip_Code|Secondary_Carrier_Policy_Holder_Country|Secondary_Carrier_Policy_Holder_Social_Security_Number|Secondary_Carrier_Policy_Holder_Phone_Number|Secondary_Carrier_Policy_Holder_Work_Phone_Number|Secondary_Carrier_Policy_Holder_Gender|Secondary_Carrier_Policy_Holder_Date_Of_Birth|Secondary_Carrier_Policy_Holder_Employer_Name|Secondary_Carrier_Policy_Holder_Empoyer_Address|Secondary_Carrier_Policy_Holder_Employer_City|Sedondary_Carrier_Policy_Holder_Employer_State|Secondary_Carrier_Policy_Holder_Employer_Zip|Secondary_Carrier_Policy_Holder_Employer_Country|Tertiary_Hospital_Carrier_Code|Tertiary_Carrier_Name|Tertiary_Carrier_Mnemonic|Tertiary_Carrier_Address|Tertiary_Carrier_Address_Line_2|Tertiary_Carrier_City|Tertiary_Carrier_State|Tertiary_Carrier_Zip_Code|Tertiary_Carrier_Policy_Number|Tertiary_Carrier_Group_Number|Tertiary_Carrier_Patient_Relationship_To_Subscriber_Code|Tertiary_Carrier_Patient_Relationship_To_Subscriber_Desc|Tertiary_Carrier_Policy_Holder_Last_Name|Tertiary_Carrier_Policy_Holder_First_Name|Tertiary_Carrier_Policy_Holder_Middle_Initial|Tertiary_Carrier_Policy_Holder_Address|Tertiary_Carrier_Policy_Holder_Address_Line_2|Tertiary_Carrier_Policy_Holder_City|Tertiary_Carrier_Policy_Holder_State|Tertiary_Carrier_Policy_Holder_Zip_Code|Tertiary_Carrier_Policy_Holder_Country|Tertiary_Carrier_Policy_Holder_Social_Security_Number|Tertiary_Carrier_Policy_Holder_Phone_Number|Tertiary_Carrier_Policy_Holder_Work_Phone_Number|Tertiary_Carrier_Policy_Holder_Gender|Tertiary_Carrier_Policy_Holder_Date_Of_Birth|Tertiary_Carrier_Policy_Holder_Employer_Name|Tertiary_Carrier_Policy_Holder_Empoyer_Address|Tertiary_Carrier_Policy_Holder_Employer_City|Tertiary_Carrier_Policy_Holder_Employer_State|Tertiary_Carrier_Policy_Holder_Employer_Zip|Tertiary_Carrier_Policy_Holder_Employer_Country|Rendering_Performing_Doctor_Last_Name|Rendering_Performing_Doctor_First_Name|Rendering_Performing_Doctor_Middle_Initial|Rendering_Performing_Doctor_NPI_Number"

SET newcount=0
FOR %%e IN (%newformat%) DO SET /a newcount+=1&SET "fieldn[!newcount!]=%%~e"
SET oldcount=0
FOR %%e IN ("%oldformat:|=","%") DO SET /a oldcount+=1&SET "fieldo[!oldcount!]=%%~e"

:: Create mapping array; map[oldfieldnumber]=newfieldnumber
FOR /l %%e IN (1,1,%oldcount%) DO SET /a map[%%e]=0

SET "newfield="
FOR %%m IN (
 "DOB","Patient_Date_Of_Birth"
 "Hosp Acct","Hospital_Patient_Account_Number"
) DO (
 IF DEFINED newfield (
  FOR /L %%o IN (1,1,%oldcount%) DO IF "!fieldo[%%o]!"==%%m (
   FOR /l %%y IN (1,1,%newcount%) DO IF "!fieldn[%%y]!"=="!newfield!" SET /a map[%%o]=%%y
   SET "newfield="
  )
 ) ELSE SET "newfield=%%~m"
)

FOR /L %%e IN (1,1,%oldcount%) DO IF !map[%%e]!==0 ECHO !fieldo[%%e]! no source field

SET "sourcedir=."
SET "filename1=%sourcedir%\q77448153.txt"
SET "field[0]=?"
FOR /f "usebackqdelims=" %%y IN ("%filename1%") DO (
 SET /a index=0
 FOR %%e IN (%%y) DO SET /a index+=1&SET "field[!index!]=%%~e"
 SET "outline="
 FOR /L %%e IN (1,1,%oldcount%) DO FOR %%o IN (!map[%%e]!) do SET "outline=!outline!|!field[%%o]!"
 ECHO !outline:~1!
)

GOTO :EOF

Without some sample data (obfuscated as necessary), it's difficult to test.

I used a file named q77448153.txt containing some dummy data for my testing.

First step is to establish arrays of old fieldname fieldo and new fieldname fieldn

Then process the mapping - this is in OP's court - I just chose two reasonably obvious fields. Just a matter of following the bouncing ball for the remaining fields. map[oldfieldnumber] contains the new fieldnumber or zero for none defined.

Report any undefined mapping

Then read the data file, fill up field[newfieldnumber] for each data field and construct the output line.