Export multiline fields into csv or xls

Hi all,

I spent a big time but couldn't find the answer.

I want to export some multiline field (for example comment, unsuncedlyrics) into a spreadsheet (csv or xls). Of course the whole content of 1 field must be in 1 cell of the table. Both csv and xls formats are able to store line breaks in one cell - but I don't know, what character does it. I tried to replace CRLF sequencences (\r\n) to $char(10), $char(13), $char(11) (=vertical tab - it seems that Excel uses), but in all cases breaks will not in one cell - instead they make a new cell in the second line every time.

Is there a solution? Thank you in advance.

Try using quotation marks around the field:

:w00t: :smiley:
Many thanks, dano!

Using Excel I can do a line break resp. a line feed in a cell of a worksheet manually by pressing [Alt]+[Enter], doing so I filled a cell with three lines.
After exporting this worksheet into a CSV file, I could see, using a hex editor, that the keyboard sequence [Alt]+[Enter] has been translated to "0A", which is the standard ASCII Linefeed character, and the line was closed by a CarriageReturn-Linefeed "0D0A" sequence.
The Linefeed character can be created by Mp3tag using the function $char(10).
The CarriageReturn character can be created by Mp3tag using the function $char(13).

After reloading the same unchanged CSV file into Excel, the formerly exported Linefeeds has not been respected by Excel on import and the cell contains only the first line of the initial input.

I assume that Excel is not ready to import "multiline" CSV fields, but do not know this for sure.
According to CSV convention, multiline csv fields are allowed, but are never actually used, assumable because of the problems which they can cause in a standard text editor.

Back to your problem how to export multiline tag fields from Mp3tag via standard CSV text file and import into Excel.
It looks like that you have to replace all LF resp. CRLF sequences at first with a "transportable" surrogate character, e. g. the vertical bar "|" $char(124) or a tab character $char(9), or some other character you like and which is unique to the entire output file and do not break the CSV line structure.

After importing this CSV text file into Excel, you should use a VBA macro (Excel should provide a macro recorder), which converts the surrogate character into an Excel Linefeed character.


While I did my investigations and was writing the previous statement, Dano has offered a simple solution: Enclose the UNSYNCEDLYRICS tag field content with double apostrophes.

This works fluently using a standard Semicolon Separated File together with my Excel application too.
After looking into my olde CSV export script, I saw that I have used the double apostrophes since long time ago for text fields, but never tried to export multiline tag fields this way.
Well, there is still the caveat to eliminate the CR character from the UNSYNCEDLYRICS content before exporting, because Excel displays a question mark symbol instead.


DetlevD, thank you too for quick and detailed help.

I decided to use use this variant, with replacing characters, because of
1. remaining double quotes in cell,
2. not soluted, too, tabs in field,

and because it meets booth my needs,
3. backup and
4. quick search in spreadsheet, without loading all files into Mp3tag.

Excel 2003 spreadsheet cell able to store 32,760 (I think, 2-digits - because I use unicode) characters. And as I found on the net, txt-format (what I need for restoring - Excel>txt>Mp3tag) has no line lenght limit.

Have a nice week end!