CSV Import. Separation Character 'list'

%3CLINGO-SUB%20id%3D%22lingo-sub-2967138%22%20slang%3D%22en-US%22%3ECSV%20Import.%20Separation%20Character%20'list'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2967138%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20the%20cvs%20import%20method%20you%20may%20specify%20what%20character%20to%20use%20as%20a%20separator.%3C%2FP%3E%3CP%3EIt%20is%20very%20useful%20for%20importing%20HL7%20messages%20into%20Excel%20as%20CSV%20files.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EHowever%2C%20Excel%20unlike%20123%20(a%20now%20long%20gone%20spreadsheet)%20had%20'multiple'%20separator%20characters.%3CBR%20%2F%3EFor%20example%20your%20file%20looked%20like%3A%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EFirst%5ELast%7CPatientID%7CAccessionNumber%3CBR%20%2F%3E%3CBR%20%2F%3EThen%20the%20primary%20separator%20here%20is%20the%20%7C%20and%20the%20secondary%20separator%20character%20is%20the%20%5E%3CBR%20%2F%3EIn%20HL7%20There%20are%20three%20depths%20of%20separators...%20%7C%5E~%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ESo%20it%20would%20be%20useful%20in%20Excel%20if%20you%20could%20specify%20a%20'list'%20of%20separator%20characters%20rather%20than%20just%20a%20single%20separator%20character.%3CBR%20%2F%3E%3CBR%20%2F%3EIdeally%20First%20and%20Last%20would%20be%20in%20column%201%2C%20Patient%20ID%20in%20column%202%20and%20Accession%20in%20column%203.%3CBR%20%2F%3EI'm%20not%20sure%20if%20column%201%20can%20be%20two%20columns%20that%20'span'%201%20...%20in%20Excel.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2967138%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2967158%22%20slang%3D%22en-US%22%3ERe%3A%20CSV%20Import.%20Separation%20Character%20'list'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2967158%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F952215%22%20target%3D%22_blank%22%3E%40Bobby1105%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20could%20import%20using%20%7C%20as%20separator%2C%20then%20replace%20%5E%20in%20column%20A%20with%20a%20space.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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.