malformed CSV in reports when " exist in a field [#6457]

Post a reply

Smilies
:D :) :( :o :-? 8) :lol: :x :P :oops: :cry: :evil: :roll: :wink:

BBCode is ON
[img] is ON
[url] is ON
Smilies are ON

Topic review
   

Expand view Topic review: malformed CSV in reports when " exist in a field [#6457]

Re: malformed CSV in reports when quotes exist in a field

by Lowlander » Thu Sep 02, 2010 11:51 am

Re: malformed CSV in reports when quotes exist in a field

by Lowlander » Mon Aug 30, 2010 9:59 am

Try the following then for the same code:
QAStr = chr(34) & SDB.toASCII(Replace(astr,"""","""""")) & chr(34)

Again I haven't tested it.

Re: malformed CSV in reports when quotes exist in a field

by andyroo » Mon Aug 30, 2010 2:06 am

Hi Lowlander, and thank you,

But while that would work for my example above, it won't work for all cases - for example, this title:

Cantate "Cessate, Omai Cessate", RV. 684. II Larghetto e Andante molto. Ah ch'i

is includedin the csv report as follows:

"Antonio Vivaldi","Cantate "Cessate, Omai Cessate", RV. 684. II Larghetto e Andante molto. Ah ch'i","Stabat Mater","5:16",...

which creates problems because it re-parses to:

Antonio Vivaldi | Cantate "Cessate | Omai Cessate", RV. 684. II Larghetto e Andante molto. Ah ch'i | Stabat Mater | 5:16

not to mention that it breaks the csv import to google fusion tables (just uploaded my whole db there) since it's malformed.

But I see the solution, it looks like the export.vbs needs to be expanded to look for embedded quotes, and then needs to properly handle them as per the csv "standards" I referenced above. Too bad my visual basic is sooo rusty... But since the commas are already in quoted fields, it's just the case for double-qotes.

so this line:

"Antonio Vivaldi","Cantate "Cessate, Omai Cessate", RV. 684. II Larghetto e Andante molto. Ah ch'i","Stabat Mater","5:16",...

would become:

"Antonio Vivaldi","Cantate ""Cessate, Omai Cessate"", RV. 684. II Larghetto e Andante molto. Ah ch'i"","Stabat Mater","5:16",...

Re: malformed CSV in reports when quotes exist in a field

by Lowlander » Sat Aug 28, 2010 9:40 pm

Try changing QAStr = chr(34) & SDB.toASCII(astr) & chr(34) on line 19 of C:\Program Files\MediaMonkey\Scripts\Export.vbs to QAStr = chr(34) & chr(34) & SDB.toASCII(astr) & chr(34) & chr(34), if I'm not mistaken looking at it quickly it would do the trick.

malformed CSV in reports when " exist in a field [#6457]

by andyroo » Sat Aug 28, 2010 3:12 pm

MediaMonkey does not follow the defacto .csv standard for embedded quotes (see http://creativyst.com/Doc/Articles/CSV/CSV01.htm).

This malformed csv creates formatting issues when importing to other applications.

For example, in the following export line, Five by February is in my database as "Five by February", and is exported in the .csv report as ""Five by February", but should be exported as """Five by February"""

export line from .csv:

"","Let's Get it Started",""Five by February"","3:41",0,"",-1,128,"G:\music\_Five by February_\01 Let's Get it Started.m4a","[re]drive"

Top