CSV Import. Separation Character 'list'

Copper Contributor

In the cvs import method you may specify what character to use as a separator.

It is very useful for importing HL7 messages into Excel as CSV files. 

However, Excel unlike 123 (a now long gone spreadsheet) had 'multiple' separator characters.
For example your file looked like:


First^Last|PatientID|AccessionNumber

Then the primary separator here is the | and the secondary separator character is the ^
In HL7 There are three depths of separators... |^~


So it would be useful in Excel if you could specify a 'list' of separator characters rather than just a single separator character.

Ideally First and Last would be in column 1, Patient ID in column 2 and Accession in column 3.
I'm not sure if column 1 can be two columns that 'span' 1 ... in Excel.



2 Replies

@Bobby1105 

You could import using | as separator, then replace ^ in column A with a space.

@Bobby1105 

I'm not familiar with HL7. However, as a comment. Perhaps if you use it on regular basis better to use some third-party tools like HL7 Soup - HL7 Interface Engine, Editor and Analysis Software , maybe open source exists. 

Power BI Desktop has FHIR server connector Power Query connector for FHIR | Microsoft Docs

Above is first in mind, I believe other options exist. IMHO, convert HL7 to CSV with manual transformations is not very productive, the same as to do that for XML.