MobileSheets Forums

Full Version: '|' as separator for value lists
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
I work a lot with CSV import. Sometimes it makes sense to import a PDF via CSV import, edit and refine field values in MSP and export as CSV file again.
I described a workaround to use Generate Song List for CSV export long ago:
https://zubersoft.com/mobilesheets/forum...-5213.html

When doing that, one important feature is missing: when a field contains multiple values, which is often the case for composers or source types, a value list is exported where multiple values always are separated by a comma followed by a blank. For CSV import values in a value list have to be separated by |.
Search and replace in the exported file needs a lot of attention as e.g. for composers comma is frequently used as part of a single composer entry.

It would be great if I could choose between ', ' and '|' as value list separator when configuring a song list format.

Or, even better, if there existed a specific Song List Format for CSV export. This should use
| as separator for fields with multiple values, allow choosing tab or semicolon as field separator and include the column headers in the file.
I'll add that to the list. As far as tab versus semicolon for separating fields - that should be something you can already control yourself with the generate song list format I believe. Are you saying you'd want an option to override the current logic and forcefully use a different separator than what is specified in the format string? 

Thanks,
Mike
No, I cannot control that.
The song title format allows specifying the separator between fields, but not the separator between list entries.

e.g. a song with several composers:
The CSV file
Title;AlternativTitel;PDFPage;Pages;SourceTypes;Keys;Years;Composers;Tempos;Signatures;Albums
Achin' Hearted Blues;;331;331-332;LS|2 S.|Tx;Eb;;Clarence Williams|Clarence Johnson|Spencer Williams;;4/4;CJB Fakebook 2023
also works with tabs between the fields and creates three composer entries in the MobileSheets database as expected.

Exporting with this setlist format

%TITLE%;%CUSTOM_GROUPS%;%KEYS%;%PAGE_ORDER%;%FILE_NAMES%;%SOURCE_TYPES%;%FILE_PATHS%;%COMPOSERS%;%GENRES%;%YEARS%;%SIGNATURES%;%TEMPOS%

creates this

CJB Fakebook 2023 Sample

Achin' Hearted Blues;;Eb;331-332;_CJB_Fakebook_2023_C.pdf;2 S., LS, Tx;D:\HerbertsGoogleDrive\_MobileSheets\Fkbk0\CJB_Fakebook_2023\_CJB_Fakebook_2023_C.pdf;Clarence Johnson, Clarence Williams, Spencer Williams;;;4/4;

Simply copying the setlist format into the file should create a valid CSV file for import. But re-importing that creates one composer entry, not three.
The commas between the composers for the export as well as the | for CSV import seem to be hardcoded. Using search&replace or a script to replace comma with | is not possible because composers often are specified as
Williams, Clarence|Johnson, Clarence|Williams, Spencer;
to get them sorted by lastname.