I am new to the forum but in need of some help. I have a script that is supposed to look at xml files in a folder and then copy them to a new folder based on reading the patient name, dob, person number and a timestamp. However, I’m having issues with the timestamp format but beyond that just having an output csv file with the info would be great.
What I get with the script below is the filename and the filedate but none of the data that should be getting pulled from inside the file.
However, after running the script it does actually print to the screen all the info needed.
Example of what prints:
Filename : \Test\EMR\CCD\0000001.xml
FirstName : ZZtest
LastName : Adele
PersonNbr : 12345
BirthDate : 9/13/1984 12:00:00 AM
Script used:
$SourceFolder = "\\ngimage\Nextgenroot\Test\EMR\CCD"
$ReportLocation = "C:\TestXMLOutput\$FileCompatibleDateStr`_FileReport.csv"
## Parse all XML files in SourceFolder
$Patients = Get-ChildItem -File -Path $SourceFolder -Filter *.xml | ForEach-Object {
$XML = [Xml](Get-Content -Path $_.FullName)
[PSCustomObject]@{
Filename = $_.FullName
FirstName = $XML.ClinicalDocument.RecordTarget.PatientRole.Patient.Name.Family
LastName = $XML.ClinicalDocument.RecordTarget.PatientRole.Patient.Name.Given.InnerText
PersonNbr = $XML.ClinicalDocument.RecordTarget.PatientRole.Id.Extension
BirthDate = [DateTime]::ParseExact($XML.ClinicalDocument.RecordTarget.PatientRole.Patient.BirthTime.Value, 'yyyyMMdd', $null)
##EffectiveDate = [DateTime]::ParseExact($XML.ClinicalDocument.ComponentOf.EncompassingEncounter.EffectiveTime.Value,'yyyyMMddHmm', $null)
}
}
## Outputs all unique patients with their most recent visit date. I also added a copy item example if you need to copy somewhere else. Update the path and remove the -WhatIf to actually copy files.
$Patients | Sort-Object EffectiveDate -Descending | Group-Object BirthDate, LastName, FirstName | ForEach-Object {
$_.Group[0]
Copy-Item -Path "$($_.Group[0].Filename)" -Destination "C:\TestXMLOutput"
}
#Exports the array created earlier into a CSV file, sorting the entries first by last name, then first name, then DOB, and then by file date
$FileArray |Sort-Object LastName,FirstName,PersonNbr,Birthdate,Filename |Export-Csv -Path $ReportLocation
Technically you’re actually not asking any question.
It would have been nice to have some (sanitized) sample data to play with.
No. The code you posted copies actually only the first file of each group of your grouped data. But all of them to the same destination folder "C:\TestXMLOutput".
What issue do you have with it?
I’m unsure if I understand. The output you shared is exactly the output I’d expect when I look at your code snippet.
BTW: You should format console output as code as well. The same counts for error messages or sample data when you post something like this.
So you may output the content of the variable $Patients instead of the variable $FileArray at the end of your code. Where do actually define these variable?
Thank you and I apologize for the newbie mistakes.
I should not have even posted the first part. Right now, I’m only trying to get the output that prints to the screen to export to a csv file. It does output a file but does not contain any of the most important data from the file.
Example of what does output to a file below, which is missing name, dob and person_nbr:
Filename FirstName LastName DOB PersonNbr FileDate
\\Test\EMR\CCD\0000001.xml 5/17/2022 14:33
Please carefully re-read the last paragraph of my first reply again! If I got you right you’re using the wrong variable.
It’s nearly impossible for us to figure out what’s wrong without actually having your data. If the output of other files is as expected I’d say the issue is with the particular files you miss the expected data. Did you check if the files actually contain the data you’re trying to extract?
You were so correct. Thank you very much for catching that. The second part of this needed to actually get the script to only copy the most recent file based on a date is being stopped because of some type of date formatting issue.
Sample file is below. Sample error from trying to get the EffectiveDate to be included (currently commented out in the original code I included) is below that.
Exception calling "ParseExact" with "3" argument(s): "String was not recognized as a valid DateTime."
At line:6 char:5
+ [PSCustomObject]@{
+ ~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : FormatExceptiontype or paste code here
Although I’m far from being an XML expert, I can see that the snippet you posted is incomplete/inconsistent. So I had to strip it down a little to be able to play with it.
Thanks so much. You were right. The script worked on most of the files but threw errors on many of them. After a lot of sifting through the xml files I finally found that not all files had the date field expected. This is contrary to what the vendor alluded to as the date to use so I’ve reached out to them. Again thank you very much for your help.
If the output of other files is as expected I’d say the issue is with the particular files you miss the expected data. Did you check if the files actually contain the data you’re trying to extract?