Import Excel List into CB

[Kurt D. Koester]

Hello. Mark J. Castaneda mentioned I could contact you Tech Support people directly, so I hope this is how he meant for me to do that.

I have my entire collection (around 7,000 pieces) in an Excel spreadsheet. I saved the contents in a tab delimited .txt format. I've attached a small sample of it as "Alpha Flight Test" in which you can see the only fields I have include: Title, Item #, Variation, Cover Date, Cover Price, Cost, Condition, and CGC Rate. How it was exported, there is not "how many copies", but instead if I have more than one copy the same line is presented as many times as many copies I have. (e.g. - #'s 1, 30, 31).

For my complete list I created a new database in C.B. and called it "My Collection" and used the "Import..." command, and in the "Import" box I selected the fields, "Title, Item #, Variation, Cover Date, Cover Price, Cost, Condition, and Add Blank Item (for the CGC Rate)". When I clicked on "Import" the one box below showed up after a few minutes of importing the data and I clicked "OK". What seemed to happen is that all of the "Titles" of the comics I have were imported, but there's absolutely no information shown below the various Titles that I can click through.

I admit I know absolutely nothing about databases, so I really appreciate your ability to "dumb-down" for me to understand. What I'm hoping I will be able to do is import the titles I have, which issues and how many issues, their condition, price paid, but also be able to keep all the FREAKING AWESOME information that you all have put into your "Master" database (e.g. - writer, artist, inker, etc.).

Is what I'm hoping to do possible, and if so, how? Thank you in advance for your time, patience with a newbie, and for this FREAKING AWESOME database you've put together!  🙂

    

Alpha Flight Test.txt

[Steven L. Dasinger]

I exported data from CB to see what it looks like and have some suggestions. You may not need all of these but this is what will match what CB is expecting and make it easier.

Change all your Title names to Match CB Title names.

example: Alpha Fight (1983) to Alpha Flight (1st Series)

Change Condition to NM.

example: Near Mint to NM

Add number of Issues (Qty In Stock).

example: add 1

Remove the Dollar Sign from Cover Price and Cost.

example: change $0.60 to 0.60

Remove the CGC Rate (or you can just ignore it as long as it is the last column).

Remove the Variation column (combine it with the Item #).

example: Item # 1 and Variation A to Item # 1/A
(if you can match what CB has, that would be the absolute best).

If you don't have the exact Title to match the one in CB, it will import it with Unknown Publisher and NOT have a single issue in any of those Titles (I hope this is a bug that can be fixed...).

I made these changes to your file and Imported it to a New Database without any problems.

If you have other Conditions here are the various abbreviations you can use:

MT
NM
VF-NM
VF
FN
VG
G
FA
P
CGC 10.0
CGC 9.9

 

[Mark J. Castaneda]

Hey Kurt,

Steven pretty much jumped in with all the tweaks you need to make to pull off the import. Hope it helps.

 

[Kurt D. Koester]

I'm home sick from work today and thought I'd spend a little time adjusting the Excel spreadsheet I have with your recommendations. I think I'm just going to eliminate the "Variation" column altogether. It's all that variant-cover crap that pissed me off to no end and when I stopped buying new comics.

I have most changes made that was recommended, but I'm a bit stuck on the "Title" changes to match what is in C.B. How does C.B. handle Annuals? For example: Avengers Annual, Captain America Annual, Conan Annual, Daredevil Annual, etc.

[Steven L. Dasinger]

For the most part, Annuals are Issues under the same Title as the Issues with an Item # of Anl 1, Anl 2, etc.

However, it is possible for Annuals to span more than one Title.

As a made up example, Spider-Man 1st Series may have Anl 1 - Anl 4, then a new Title Spider-Man 2nd Series may have Anl 5 - Anl 6.

 

 

 

[Kurt D. Koester]

Thank you for the info on annuals. Another question - even though I downloaded and installed the covers, why does it only show a cover image for the very first entry of a title and no others?

[Steven L. Dasinger]

Depends on the Edition you have.

The Professional Edition only has a limited number of covers (around 50,000) . Since there are about 50,300 Titles in CB, that works out to only 1 per Title (on average. Some may have more and some may have none).

The Archive Edition has around 625,000  cover.

 

 

[Kurt D. Koester]

Yes, I have the "Professional Edition".

Whew, this is going to take a while. I'm going through and trying to make sure my "Title" names match the names in C.B. and I only made it through the A's and B's. Time for a break. Thanks for all the help, and I'll let you know what happens when I finally finish tweaking my spreadsheet and try to import it.

[Kurt D. Koester]

Okay, so I finally finished modifying my spreadsheet to match the fields as recommended. I created a new DB and imported the spreadsheet that I converted as tab delimited text, and it appears it imported it all - mostly. I have some odd examples of what did and did not get imported. 2001, A Space Odyssey did not import, but 2010 did. None of my Avengers, The were imported, either. I tried a test with adjusting Avengers, The to The Avengers, but that didn't work either.

Is there a text list of "exactly" how the titles need to be for them to import? Any suggestions?

[Steven L. Dasinger]

The easiest way is to export the database to a file. This would give you the exact name (and Item #) that CB is using.

For example, the two you mentioned export (using Tab Delimited) as:

2001, A Space Odyssey

Avengers, The

Looks like it might have to do with punctuation and location of leading articles.

I think the Professional Edition can do exports.

 

 

[Steven L. Dasinger]

If you just want a list of Titles, instead of exporting (which will have a lot of duplicates), you can use Find like this:

Click on the Magnifying Glass icon next to the Find box at the top of the window.

In the Find window, click on the Find drop-down box and select Title Fields->Title.

In the condition drop-down box, select Is Not.

In the input box, type a Space (press the space bar).

When you click the Find button, you will get a list of all 58,000+ Titles (along with Publisher an Years Published).

 

At this point you can select (high-light) all the Titles then Copy (CTRL+C) and Paste (CTRL+V) the information into a spreadsheet or Notepad.

NOTE: You may need to use Menu item Setup->Preferences and set Rows to Display in Grid View to 100000 to get them all in one display.

If not, you may have to copy/paste one display, advance to the next display, then copy/paste them until you have gone through all the displays.

[Kurt D. Koester]

The odd thing I noticed when I exported from my spreadsheet to the text file is that some of the titles show up with " " marks around them, and those are the titles that don't import into CB. I did a test of removing the " " marks around "2001, A Space Odyssey" and also "Avengers, The" but that didn't seem to make a difference. They still did not import.

Why are the " " marks showing up for some titles, but not others, and why would they possibly be the reason the titles with the " " marks not import? Thanks as always for your hlep.

[Steven L. Dasinger]

I am not sure. For a Comma Separated data (instead of Tab Separated)  The " " are needed for any Title that contains a Comma (so the process knows to ignore it).

For example:

"Avengers, The",2,Marvel

The Import would only see the 2 comma's between Title, Item #, and Publisher.

However, when I did a Tab Separated export, the Title showed up as:

Avengers, The   2   Marvel

Without the " " around the Title.

 

It might be best to use Comma Separated Import if the data has " "

[Kurt D. Koester]

I'll again expose my ignorance by saying I don't know how to export out of Excel to a comma separated .txt file. The only option I saw would be saved as a .csv file, and CB only looks for .txt files. I tried exporting out of Excel as a Unicode Text .txt, and also the Text (MS-DOS) .txt, and neither of those two .txt files imported into CB correctly. With the Text (Tab delimited) .txt, at least "most" of my collection imported into CB properly. It was just anything with the , in the title that didn't work. I may just be completely dumb, but if there's a way to export out of Excel as a comma separation instead of tab, how would I do that/what type would I choose?

[Kurt D. Koester]

I just tried an experiment. I opened a copy of my exported .txt file and did a "find & replace" finding the "tab" and replacing it with a ",". I then imported that file into CB and made sure to select the "Separator" as "comma" instead of "tab" in the bottom right corner of the CB import window. I thought it worked, but all of the "Qty" and "Cost" fields are all 0 (zeroes). That doesn't seem to be working either. I'm stumped!

[Steven L. Dasinger]

First, you are correct. The CSV (Comma delimited) (*.csv) is the correct option to create a Comma Delimited file.  After that, just rename it from *.csv to *.txt so CB can find it.

If you include a few lines of you file, I can see if there is anything obviously wrong with it.

[Kurt D. Koester]

I've attached a sample of the export to a .csv and changed to a .txt file. Thank you SO much for your help with this! I sincerely appreciate it because otherwise, I'd be lost.

CSVWorking File 01-02-2020.txt

[Steven L. Dasinger]

The Good News is that all the ones that Imported successfully have the Qty set to 1 (along with the other values in the file). So it works for me.

One thing you might check is when you Import the Comma Delimited file, is that the Item Separator in the Bottom Right corner is set to COMMA and not TAB.

 

The file could use a little clean up.

Open in Notepad and Change ,,, (that is 3 commas) to nothing (leave the to value empty). This will remove the 3 commas at the end of each row that are not needed.

Go to the last line and remove it if it is all commas.

 

Of the Titles in your file, only 2 did not import successfully.

Amazing Spider-Man All Detergent Giveaway,1,FN-VF,1,0.40,USD,1/1/1979,3.51 is NOT a separate Title. It is an Issue in Amazing Spider-Man Giveaways Item # 4.  Change the row to:

Amazing Spider-Man Giveaways,4,FN-VF,1,0.40,USD,1/1/1979,3.51

And it should import correctly.

 

The other is  Avengers Chronicles,1,FN +,1,1.50,USD,6/1/1982,1.50. The problem with this is it is is NOT in the Comic Books View.  It is in Books.

Unfortunately, you have to Import Books, Comic Books and Magazines individually.

When doing this, make sure the drop-down box in the Top Left corner is set to the View you are importing to.

Since this Title needs to go into Books, the Item # needs to be changed. There are 3 possible Issues. HC/LE, TPB/A, or TPB/B. Pick the one that matches your book (I am using TPB/A as an example. Also the Title is Avenger and NOT Avengers (no S on the end) and it has The in it.  Since the new name is Avenger Chronicles, The (with a Comma in it) it needs to have " " around it.

For this Title, put it in a different file and change it to:

"Avenger Chronicles, The",TPB/A,FN +,1,1.50,USD,6/1/1982,1.50

PS: If you have any Magazines, they will also need to be in a separate file.

 

 

 

[Kurt D. Koester]

You are so AWESOME! Thank you for helping me all of this. I will work on the modifications you listed and let you know how it goes. Thank you again VERY much!  🙂

[Kurt D. Koester]

Ugh! I'm seeing the problems of when a title is classified as one thing and possibly another, but being the same title. The title I'm referring to is "Avengers Chronicles, The" (not "Avenger Chronicles, The"). The situation is that it is actually "Fantaco's Chronicles Series" in CB as issue #4. To make things even MORE confusing, "X-Men Chronicles, The" is listed as issue #1, but also as a totally separate title as "X-Men Chronicles (FantaCo) in CB. Both are the exact same book. "face-palm"

[Adam Sternberg]

15 hours ago, Kurt D. Koester said:

Ugh! I'm seeing the problems of when a title is classified as one thing and possibly another, but being the same title. The title I'm referring to is "Avengers Chronicles, The" (not "Avenger Chronicles, The"). The situation is that it is actually "Fantaco's Chronicles Series" in CB as issue #4. To make things even MORE confusing, "X-Men Chronicles, The" is listed as issue #1, but also as a totally separate title as "X-Men Chronicles (FantaCo) in CB. Both are the exact same book. "face-palm"

Kurt, there are a couple of things I do to make sure my database entries match what CB2020 is looking for; either or both may help:

1. I keep a blank database that I update weekly (along with my main database); that way, it's a reflection of the master database on Human Computing's servers
2. Look up titles on Atomic Avenue for those times that switching databases isn't convenient.

Hope this helps,

Adam

[Kurt D. Koester]

Thanks for the suggestions. I'll give them a try.