Advice for importing purchase information

[storgendibal]

I have around 6000 CDs and 60000 songs iTunes and in the past have used CATraxx, a PC database, until I became a MacUser.

I bought CDpedia about a year ago but didn't immediately take to it as I was still trialling what would be best to replace CATraxx. I am now in a situation where I have an old database that is not up to date (CATraxx) and iTunes that is up to date but does not include purchase price, purchase date etc and does not quite list all of my CDs as I have some duplicates.

I have managed to import my CATraxx list of CDs into CDPedia (though the purchase date didn't import correctly due to UK/US date formats and I am missing track info but this could be resolved with little but some effort).

I have also imported my iTunes playlist which was a dream but obviously does not include the purchase information or UPCs.

I have a bluetooth barcode reader on order, for new purchases and in case I need to add any items in bulk (i might purchase DVDpedia in due course, and I have no database for my DVDs so that would be starting from scratch).

I wonder what your recommended course of action is to get a database with all my CDs and purchase info in place. I am almost resigned to going through the imported iTunes collection and adding manually my purchase date/purchase price/barcode information but this will take ages for 6000 CDs. I would then also need to compare the collection with the physical shelves to check for duplicates etc. I don't want to start from scratch with the scanner as I think there would be too many rare CDs in my collection (e.g. CD singles) that do not feature in amazon/freedb).

Is there any shortcut given that I have the purchase information in excel now and could bulk enter the UPCs with the scanner? I would be happy to have an export of the CDPedia information to excel and then re-import once I have added the purchase information in excel, overwriting that information in CDPedia, as it is easier to bulk edit using excel.

And once the UPCs are entered for the collection, it would be useful to be able to turn on an audit function whereby I scan every CD on my shelf and a report is then generated showing which CDs are not in CDPedia and which are in CDpedia but not on shelf. That would be SO useful.

I have played around with the program for new entries and it looks sweet, but want to resolve this first else I'll end up with 3 databases!

Thanks


Andrew

P.S. At the moment it has a collection of 5000+ CDs from iTunes and 5000+ CDs from excel import, and the program is running fine.

[storgendibal]

Sorry, thought this was posting to the PediaTalk forum.

Andrew

[storgendibal]

No comments as yet but I am nearing a solution.

I have managed to import iTunes information to CDPedia, and then export that information to Excel, other than track info and cover images (see later). In Excel, I can manually add the Unique ID for each album that corresponds to the UID that my old windows database used, and then use that reference to look up the 10+ items of personal information from my Excel import from the old database. I have then managed to import this consolidated Excel list back into CDPedia. This is mostly what I am after. Unresolved issues are now:

Link to iTunes: I don't need to worry about the iTunes links as I have found out from the forum and by trail that these links are not encoded in the data anyway but looked up using Song Title and Artist (and hopefully Album as they don't necessarily match up).

Track information: I don't know how to export the Track information from CDPedia so that I can view it in Excel. I know how to import it (using semi-colons to split tracks out) but I can't get the track information (name, artist, duration) from the iTunes import sitting in CDpedia into Excel. Help needed achieving this via CDpedia or iTunes directly.

Covers: I don't know how to export the Image URLs when exporting from CDPedia, but I know how to import it back in. The URL points to the Covers folder and then a .jpg with a numbered filename, presumably CDPedia's UID for the album, so if I had this number for each album I could recreate the URL easily enough, but I don;t know how to export this UID either. My only thought on this is to set the Collection ID as incremental, delete the database, and import the iTunes albums again and hope that CDPedia assigns the same UID as the Collection ID.

ASINs/UPCs: These will have to be done manually, but no escaping this really.

I'm getting there! The tracks and covers are just icing on the cake. The ASINs and UPCs will be done later and would be the cherries on the icing on the cake!

Andrew

[Conor]

Hi Andrew,

This is a tough one as there are so many formats involved. Sorry about the delay in replying but I lost my laptop's hard drive (no big deal with TimeMachine) and I needed to get a new drive installed and reinstall the entire OS X. Your original idea of exporting from CDpedia to a CSV and then doing a re-import is a good choice. You would need to have some field in common so that you could sort by that attribute to match entries in excel when adding the purchase date and price. The problem with CSV export/import is that it's not going to export the covers as you found out. The CSV export is a bit more limited than the import. The reason for this is to keep the CSV export simple to use and the text template export is so powerful that it allows those users looking for extra flexibility to export those values. The file saved as a myexport.txt in ~/Library/Application Support/CDpedia/Templates with the following text will then appear as an option under text export:

Code: Select all

<!--BeginRepeat-->[key:title],[key:artist][key:alltheotherfieldsyouwouldbeinterestedin],file://Users/myusername/Library/Application Support/Covers/[key:uid].jpg,[tracksBegin][track:name],[track:artist],[track:duration],[track:composer][tracksEnd]
<!--EndRepeat-->

CDpedia also has two native formats where it has to do no massaging of the data that would be more accurate. One is XML, this is the format that is created when you do a ".cdpedia" export. the export is a package (that can be opened with control-click) that includes an XML file with all the data and a folder with all the covers. Modifying XML to inject it with the purchase data could be done using XLST. I myself have never used a XSLT and this would be the complicated option. The second option is CDpedia's database which is in SQL and can be read directly by SQLite. This is the most promising option as the SQL commands are not as daunting as they look and the database can be edited in place without requiring an import after changing the data. You could export the current information from Excel and change it with a text editor to insert the SQL command so that this export:

Code: Select all

Price		UID
'$ 14.20'	'MyID1'

Would turn into the following command:

Code: Select all

update zEntry set zPrice = '$ 14.20' where zCollectionID = 'MyID1'; 

Collection ID would be what you are using to match entries and would need to be exactly the same, you could use any other unique field if you don;t have such a field such as title. If you take this route all the keywords are what you would expect with a 'z' prefixed. The really tricky one would be the purchase date as for sorting and speed reasons it's stored as the number of seconds since January 1st, 2001. SQLite might have a DATE function that does the translation, if you need more help let me know and I can research it, otherwise Excel might be able to do the conversion.

The following command opens the file:

Code: Select all

sqlite3 ~/Library/Application\ Support/CDpedia/Database.pediadata

This one closes it:

Code: Select all

.exit

So in all you would want to end up with a text file that looks like the following that can be copy pasted into the programed called Terminal to modify the current CDpedia database. Before experimenting with changing the file, be sure to quit CDpedia and make a backup copy of your data folder inside your home folder at ~/Library/Application Support/CDpedia incase the modifications don't go as expected. (They would be irreversible and you would have to replace the Database.pediadata file with the backup copy to revert.)

Code: Select all

sqlite3 ~/Library/Application\ Support/CDpedia/Database.pediadata
update zEntry set zPrice = '$ 14.20' where zCollectionID = 'MyID1';
update zEntry set zPrice = '$ 11.50' where zCollectionID = 'MyID2';
....
update zEntry set zPrice = 'Online' where zCollectionID = 'MyID1';
update zEntry set zPurchasedAt = 'A Store' where zCollectionID = MyID2';
...
.exit

The internal UID is incremental starting from 1. So two clean databases imported in the same order would end up with the same internal UID and the covers would match. I give you all this information as you clearly have thought about the problem and wanted you to be well armed, but I would recommend the SQL or text template export that includes the tracks and cover URL.

You could enter the UPC directly with the scanner by using the edit window in CDpedia. CDpedia stays in the same field while editing and selects any text in the field when changing entries in order to make it convenient for a user to edit the same field in multiple records. The command for going to the next record is "command-n". Most scanners can be configured to send "Command-N" after the scanned number instead of the regular "enter", this would free you from having to use the keyboard or mouse.

Thank you for the idea on a specific audit option as that sounds very useful. In the meantime if you set the focus to the internal search filter field on the top right of the toolbar and then scan UPC codes and only one result (or two) would show up if you have the CD and none when absent. I have tested this with the Bluetooth scanner but should work with tethered scanners as well. Once again this also depends on how the scanner is configured.

[storgendibal]

Thank you Conor that is most helpful. I have found out that hard-drives crash as well!

I have had a go and here's where I am upto.

MyExport file - I have got this working as I would like, thank you, with the UIDs and cover locations.

XML - I didn't attempt this as although I can just about edit XML I am not confident in manipulating it.

Excel - I am well versed in Excel and tried this. The only issue I have at the moment is that CDs with lots of tracks do not export the track information to Excel in full as I presume there is a character limit in each Excel cell.

SQL - I have done a small test on basic information and got this to work. My concern is that I'll have about 20 data items for 5,000 CDs so that would be a large SQL file - 100,000 lines, and I also have to conquer the date issue. On this latter point, the format seems rare being from the year 2001 and not 1901, is it just something you chose or something common to SQL or a typo? As there are 60x60x24 seconds in a day I assume that if I wanted noon on 2 Jan 2001 I would enter 129,600, and that if I wanted dates prior to 1 Jan 2001 I would enter a negative number. I also assume that leap days are taken into account.

I have just about finished matching the CDPedia UIDs to my CATraxx UIDs so am about ready to go (no mean task, its taken me about 18 hours). My thoughts for easiest way forward, if this is possible, is to export except track info using MyExport, match my data in excel, and reimport creating duplicates in CDPedia, one set with track info and one set with my purchase info etc. I would then like to use SQL to transfer track information from one record to another (after another bit of exporting and importing to link one CDPedia UID to the duplicate record with the track information). This would then mean that I am only running 5000 x 4 (for track title, artist, duration, composer) lines of SQL code. But I am not sure if that is possible in SQL and what the command would be.

Failing that, I would only import the track names from MyExport where they have not been truncated, and then do the CDs with lots of tracks manually (though I think there are about 1000 of them!)

Anyway, thanks for your thorough answer, it immediately solved a big chunk of the problem and I could live without the track names if need be as I use the database mainly as a record of purchases, and iTunes for track information.

Andrew

P.S. If there are any other readers, there are a few typos in the code:

sqlite3 ~/Library/Application\ Support/CDpedia/Database.pediadata should be
sqlite3 ~/Library/Application Support/CDpedia/Database.pediadata and probably needs to be in quotes due to the space in Application Support

and

<!--BeginRepeat-->[key:title],[key:artist][key:alltheotherfieldsyouwouldbeinterestedin],file://Users/myusername/Library/Application Support/Covers/[key:uid].jpg,[tracksBegin][track:name],[track:artist],[track:duration],[track:composer][tracksEnd]
<!--EndRepeat-->

should be

<!--BeginRepeat-->[key:title],[key:artist][key:alltheotherfieldsyouwouldbeinterestedin],file://Users/myusername/Library/Application Support/CDPedia/Covers/[key:uid].jpg,[tracksBegin][track:name],[track:artist],[track:duration],[track:composer][tracksEnd]
<!--EndRepeat-->

and you obviously have to replace myusername with your user name!

[Conor]

100,000 lines is a lot of changes but SQL is extremely fast and will be able to do it in seconds. The harder part is automating the creation of the SQL commands from the Excel file. I would also do them one field at a time as it would be easier to manipulate the export and to verifying that all went well.

The counting of second is tricky, specifically due to the fact that leap years also count. Not to mention you have to shift your calculation to GMT time zone. Although any calculations would be only hours off depending on what country you live and this is probably not a concern as you just want the correct day and not hour.

The date January 1st. 2001 GMT, is an Apple thing. They previous reference date was 1970 but it was updated with Tiger to the beginning of the new millennium.

SQL doesn't store the dates in a number of seconds since a reference date, this is an Apple thing and why it's more complicated to modify a date directly in the database. SQL does have a function that will take a date and return the number of seconds since 1970: "strftime('%s', '2001-01-01');". This same function can be modified to shift the date to return the number of seconds since 2001 by subtracting the difference of seconds between the two reference dates: "strftime('%s', '2001-01-01', '-978307200 seconds');". So to summarize you can build the SQL commands in the following format:

Code: Select all

update zEntry set zPurchasedOn = strftime('%s', '2001-01-01', '-978307200 seconds') where zCollectionID = 'MyID1';
update zEntry set zPurchasedOn = strftime('%s', '2006-05-24', '-978307200 seconds') where zCollectionID = 'MyID2';

For testing purposes in SQL you need to prefix "SELECT" infront of any statement to see the results. E.i. in Ternminal you could do the following (the last one should be 0 seconds):

Code: Select all

sqlite3
SELECT strftime('%s', '2001-01-01');
SELECT date('now');
SELECT strftime('%s', '2001-01-01', '-978307200 seconds');

I would not recommend trying to do the tracks via the SQL. Do those via the import and then the attribute changes to the regular fields via the SQL. The tracks are actually a separate table in the SQL database for speed, so working with them directly from outside CDpedia is extremely complicated.

Hope all this information helps, and thank you for the corrections on the text export file and the terminal command. You are correct that I should use:

Code: Select all

sqlite3 "~/Library/Application Support/CDpedia/Database.pediadata"

The quoted version is easier to read than the backslash in the previous version, the backslash tell the computer that the following space character should be read as part of the path and not as a separation to start another input.

[storgendibal]

Thanks Conor, looks like I'll get this sorted. I'll be fine manipulating Excel to produce the commands. I won't actually be able to give it a try for another week or so I reckon so I'll let you know how I get on later. I am happy if you want any feedback or testing on large sized databases in the future if you are interested. Someone has already commented on PocketPedia syncing but I know this is an apple backup iPhone issue.

Andrew

P.S. I didn't know that forward slash did that, very useful.

[storgendibal]

Conor

Sorry to bug you. I have had a try with this and am up against a small barrier. I have asked around but no one has managed to solve this as yet. When pasting 5,000 lines from the clipboard into Terminal/SQLite, the keyboard buffer overflows so that not all of the commands are actioned (it processes about 30 lines of code, then the copied text starts getting a bit "corrupted" as the buffer overflows then catches up etc. I have tried to see if I can import a .txt file into the Terminal window so that it processes the commands this way, or to put a pause in between lines of codes, or to find an applescript to automate it, or to increase size of buffer but all to no avail.

Any thoughts?

Thanks

Andrew

[Alex]

Ok, this is a little trick that works in regular mysql but I'm not sure about sqLite and I do apologize for not having the time to test it right now, but you can give it a whirl with a test database. Don't try it on your regular database please.

If your text file contains sql commands you can feed the to a database or table using the redirect caret:

# mysql -u your_mysql_username -p your_database < database_backup.sql

The following link (http://www.sqlite.org/sqlite.html) has this to say about importing from a text file (scroll a bit from the beginning)

.import FILE TABLE Import data from FILE into TABLE

which sounds like what you're after.

Sorry, I'm not as handy with sqLite, just mysql

PS
Oh yeah, forgot to mention that if you're doing a lot of tinkering with sqLite, you might want to try some of the tools out there. There is a browser called SQLite Database Browser 1.3.app, free and seems to work quite well.

Cheers

[storgendibal]

Conor / Alex

Success! I did a search for some SQLite tools as suggested by Alex and actually used MesaSQLite. It enabled me to import datadase.pediadata and then Export the whole of ZENTRY table to excel and then paste in the information that I wanted (having already matched CDPedia's UID's with my other database's IDs). I then deleted all information in ZENTRY table and reimported my amended excel table. Opening CDPedia presented me with the original information plus my new information. Excellent! I didn't need to use the Terminal commands or import the commands from a txt file in the end. It was all quite simple.

(Obviously I had a back up whilst this was going on).

Took me about 2 hours - excluding the hours of work in matching the two IDs essentially manually. Would be easy if I didn't have so many similar sounding CDs.

Thanks for all of your help and ideas. Not particularly recommended for casual users, but I am ok working with relational databases.

Andrew

[Conor]

That is great news. This is why we always use the open source standards for our data as we want users to have flexibility, even if it means getting dirty with the Terminal. Our previous XML database was easier to understand and modify but not as powerful when it comes to making complex changes and even more important for the growing Pedias the SQL is much faster.

[gg66]

I've also switched from CATraxx on Windows to CDpedia on Mac. I succeeded to import automatically almost all data from Catraxx including CDcover images. Here is the how-to-do:

Step1: extraction Data
On the Windows PC, inside CATraxx select File > Export > Text File
In General tab choose Field separator TAB, Text delimiter=none, Separator artists= & (blank + & + blank), Separator persons= & , Separator studio & , Separator awards= & , check sort order by AlbumID
In Albums tab, type the File name for the file to create, select all fields you want to export, including Internal > Album ID. Track names can be exported, but not Track length (in fact Tracks length can be exported from the Tracks tab but the file output format is not very easy to use with CDpedia)
It's not necessary to choose any fields in the Tracks tab
Press Start and all data will be exported in the specified file, here for the example ExportFile.csv
No CDcover image at this time though (see step 5 below)

Step 2: conversion UTF-8
If there is non US character in the data, for example éàä…, a conversion of the ExportFile from ISO-8859-1 to UTF-8 will perhaps be needed.
I tried Iconv on the Mac (command line utility), but although the command worked, the characters still appears not correctly in CDpedia. So I used a freeware on the Windows PC: Character Set Converter (fromhttp://download.cnet.com/Character-Set- ... 42412.html). This freeware recognized automatically the input file as ISO-8859-1, choose UTF-8 as output and a new file with a .2. added in the filename is created: ExportFile.2.csv
Transfer this file on the Mac

Step 3: Transformation of Date format and replacement of character " (double quote) if present
On the Mac now.
If there is any Date in the ExportFile.2.csv file a conversion is needed. CATraxx export Date in the format yyyymmdd but CDpedia understand Date in another format mm.dd.yy
I opened ExportFile.2.csv with Excel. I added a new column right after those which contains Date and use Excel string function to extract part of the Date and write these parts in another order:
=STXT(H2;5;2)&"."&STXT(H2;7;2)&"."&STXT(H2;3;2)
In this example, the CATraxx date is in the cell H2 and I copy this formula for all subsequent cells (drag the little square in the bottom right of the cell to copy the formula to all cells below).
If any column contains a character " (double quote), replace it with a character not present in this file, for example | If you don't do that Excel will add " around the fields which contain " when you save the file.
If you have exported Tracks, they are all separated with the string: blank+ / +blank. Check if this string is not present in another field. If present replace this string by another not used in the file, for example \.
Save the file from Excel as ExportFile.2b.csv
Open ExportFile.2b.csv with TextEdit and replace the previously used character | with " Replace also all track delimiter (blank+ / +blank ) with the character ; (no blank around). Then replace \ with blank / blank

Step 4: Import the data in CDpedia (finally!)
Choose File > Import List and use the file previously exported from CATraxx and converted. Make the match between CATraxx and CDpedia fields. If you want to import CDcover, import the CATraxx AlbumID as Custom10 for example.
If all is ok you will have your collection imported with all fields.

Step 5: CDcover images
If you want to export your CDcover images from CATraxx and import them into CDpedia, do first an Export from CDpedia(!): File > Export List, check Text template and press the button Edit. Give the following text:
Export <!--BeginRepeat-->ren Album_[key:custom10].jpg [key:uid].jpg
<!--EndRepeat-->
save it and then press the Export button. This will create a Windows command file, export-uid.txt for example. Open this file with TextEdit and convert it as text only format (not RTF): Format > Convert in Text format, save it. Close CDpedia and transfer the command file to the Windows PC.
Go back to CATraxx on the Windows PC and make a new export in XML format this time: File > Export > XML, type a XML filename, check at least Cover > Cover 1 and press the button Export. A XML file will be created (we will not use this one!) and also all CDcover images will be exported as Album_xxx.jpg files where xxx is the CATraxx AlbumID. Put the command file export-uid.txt in the same folder as the Album_xxx.jpg files and rename this command file to export-uid.bat then double-click on this command file and all your images will be renamed to the CDpedia compatible names. Transfer all the images on the Mac in the CDpedia folder (Library\Application Support\CDpedia\Covers).
Open CDpedia and all the CDcovers should be here!
You can finally select all CDs from CDpedia, press the Edit button and clear the field Custom10 which is no more useful.

Claude

[mikedt]

Excellent instructions Claude. One thing, when I went to do the export from Catraxx, album ID wasn't showing. Turns out I had to make sure the Use property of the Album ID field was checked in the 'Database Setup' window.

[snomis]

Thank you so much for the instructions! I'm having two issues with importing my catraxx database into CDpedia.

1. I have 558 Albums in my Catraxx database. Only 344 make it into CDpedia. Catraxx confirms that it exports all of them.
2. When I look at the album info, all tracks are merged into 1. i.e. each album only has one track with an extremely long title (all the tracks' titles on one line)

I spent a long time entering all the info the way I wanted it into Catraxx over many years and I'm a recent convert to Mac. My goal is to also rip all the CDs into lossless (flac probably) as well as lower quality mp3 to stream online from my NAS when I'm in the car or away from home.

thanks!

[Nora]

Can you send us the file you're importing please so we can take a look at that here? You'll find our email on the support page.