csv export has too many commas to delimit

[JonnyZephyr]

Hola amigos, I've been trying various MP3/media catalog software to assist me in hopefully "fixing",updating,and improving access to a LARGE media database (audio and video collection). The software has to be FREE and FAST, to assist me in my tag&rename quest, and should help me update an excel"esque" database/system with (hopefully!!!!) some various useful macro functionality (including SelectMedia and CopySelectedMedia, for sharing gigs memorabilia with my fellow collaborators,musicians,artists and
stakeholders...)

REGARDING THE SOFTWARE I'VE TESTED SO FAR (advanced catalog pro, advanced MP3 Catalog, JBMusic, File Catlog, File Manager,

Catalog Max, Advanced Cataloguer and samba Professional) I've been disappointed with the CRUCIAL "EXPORT REPORT"
functionality, because I want to be able to

identify folders
(those without subfolders SHOULD be albums when the file structure
is fixed as drive\media\audio\artist\album, for example)

Be able to run a macro to fix the dataset for analysis (identify missing metadata/duplicate content etc)



I've just tried Catalog Max and, although it's not as fast and fit for purpose as aCatpro, it's FREE! HOWEVER... The reports are exported as csv using a COMMA as the delimiter. If the row has other commas in the file/folder path. data can span a different number of columns, like

Q:\MUSIC SERVER 1 [A-D]\Deftones\Deftones - [2006] Saturday Night Wrist\06 - Deftones - U, U, D, D, L, R, L, R, A, B, Selct, Start.mp3


Q:\MUSIC SERVER 1 [A-D]\Deftones\Deftones - [2006] Saturday Night Wrist\06 - Deftones - U, U, D, D, L, R, L, R, A, B, Selct,
Start.mp3,4045512,08/12/2007 08:51 AM,A

The end of each data row is, however, consistent. Any ideas how to, for example, recognize the final TWO delimiters in each row, so this row would have the following data

column A

Q:\MUSIC SERVER 1 [A-D]\Deftones\Deftones - [2006] Saturday Night Wrist\06 - Deftones - U, U, D, D, L, R, L, R, A, B, Selct, Start.mp3

column B

4045512

column C

08/12/2007 08:51 AM

Column D

A

Columns C and D will be skipped anyway (unless they're useful in identifying )

Anyone any ideas of IF (and HOW!) this could be achieved?

THANKS!!! Jonny

 

[JD_GFI]

Hi there,

If you use a helper column you could do the following (if date is a standard format also).

Cell A1 Input: String to be split

Column A Result: Q:\MUSIC SERVER 1 [A-D]\Deftones\Deftones - [2006] Saturday Night Wrist\06 - Deftones - U, U, D, D, L, R, L, R, A, B, Selct, Start.mp3,4045512,08/12/2007 08:51 AM,A

Cell B1 Input: =(LEFT(A1,FIND("/",A1)-4))

Cell B Result: Q:\MUSIC SERVER 1 [A-D]\Deftones\Deftones - [2006] Saturday Night Wrist\06 - Deftones - U, U, D, D, L, R, L, R, A, B, Selct, Start.mp3,4045512

Cell C1 Input: =(LEFT(B1,FIND(".mp3",B1)-1))&".mp3"

Cell C1 Result: Q:\MUSIC SERVER 1 [A-D]\Deftones\Deftones - [2006] Saturday Night Wrist\06 - Deftones - U, U, D, D, L, R, L, R, A, B, Selct, Start.mp3

Cell D1 Input: =RIGHT(B1,LEN(B1)-FIND("mp3,",B1)-3)

Cell D1 Result: 4045512

Let me know if this works ok or not, seemed to work fine in Excel 2010.

Cheers,

JD