How to create a hentai catalog!

How to create a hentai catalog!

I will create a create a text file, then make a csv file from it and import it into Excel. Finally I'll write a couple of useful excel functions that'll help me find duplicate entries in my catalog.

UPDATE! I've found a better way to make csv files, the guide has been updated with the new method that uses Windows own scripting instead and doesn't need Word.

Setting Up

Name your folders/files (if you archive them) according to some rigid scheme. This time we will use mine. My Windows is Hungarian, but you should find everything familiar and I'll always try to name what I do in English:




a) Hentai Doujinshi

Franchise (Author) - Title [Translator]

b) Hentai Manga & Original Doujinshi

Author - Title [Translator]

The crossed-out part was for the old csv guide.
Nowadays I use this naming scheme:



Franchise {Author} - Title [Translator]

I do this, because this way I can keep all entries (both doujinshi and original work) in a single database with common formating. For eromanga or doujinshi with original setting I just write "Original" into the Franchise field.

New CSV Guide

[size=14]1. A VB Script to switch out characters in a text file.[/h]

First, we make a vb script file that we'll use to make our csv files:


Put this into note-pad as save as "replace.vbs".

This script looks for a specific character in a text file and replaces it with another. We'll use it, to replace the characters you normally use to denote specific parts of a file name (ie. artist is always between {}) with semi-colons (;).

[size=14]2. Next we write a batch file, to process our list files.[/h]



Put this into note-pad and store it as "csv.bat". I keep this in a separate batch file, so whenever I need to make a list I can just call this with the right parameters.

All it does is call the vb script we wrote numeral times and replace varios characters. See that "%1" there? That means this batch file will need a parameter when calling it.

A csv file is a list of records that Excel (or other database/spreadsheet) software can access. Each record ends with an EOF character, and the fields are divided by semi-colons.

Since a listing by windows already separates items with EOF if we can change the characters that denote the different parts of the file/directory name we can get a useable csv file.

This only works with my own naming scheme!

Franchise {Artist} - Title [Translator]

If you use a different naming scheme, you'll have to modify the script!

Ie. If you used Artist (Franchise) - Title and kept your stuff in .rar files, you'd have to replace " (" (yes, you need to get rid of the space too!) with ";" then ") -" with ";" finally ".rar" with "" (empty as we don't need that end of the file).

[size=14] 3. Finally we make the list of directory where our hentai is, then process it with our other batch file.[/h]

You'll have to replace PATH with where you keep your stuff! (ie. C:\My Documents and Setting\User\Documents\Hentai. The batch file will create the new csv file where you run it.



Put this into note-pad and save it as "make-list.cmd".
When you want to create your csv (and text listing file), just run this file.

Old guide on making the CSV file



Mucking around in Excel

If I did everything right, then I should be able to open up this file in excel and a table will greet me:



After correcting some minor errors, (which are easy to find. Just go along the last column and see if there are any extra or missing entries. These are likely due parsing errors you (or in this case me) made during the conversion above or as even more likely the original file or folder didn't follow your naming scheme), it's time to set things up. I create the field headers:



Notice that I created 3 empty fields. I will use these to detect whether there is a duplicate entry in the database.



Before I move on, I'll have to create some self-expanding dynamic named ranges. This will ensure that I can just expand my database without having to rewrite and reapply my parameters each time I add to it.

It's easier than it sounds. This is how:



Go into Add-->Name-->Define.



Write a name, for me it'll be "Artist".
Then write this into the Refers to Box:
"=OFFSET($B$1,0,0,COUNTA($B:$B),1)"

In the above line, B refers to the B column where I have the "Artists" listed. If you have it in a different column, you'll have to write its letter there.
If your spreadsheet has has more than one worksheet , you have to define the range for each sheet and you'll have to name it '< Sheet >'!< Range > and write Refers to as:
"=OFFSET('< Sheet >'!$B$1,0,0,COUNTA('< Sheet >'!$B:$B),1)"

Likewise I'll add another range for the Full name.

Write a name, for me it'll be "FName".
Then write this into the Refers to Box:
"=OFFSET($G$1,0,0,COUNTA($G:$G),1)"

You can read more about Dynamic Ranges here:
http://www.ozgrid.com/Excel/DynamicRanges.htm

Time to write the functions. First I'll assemble the full name to search for duplicate entries. I write this into G2 (the first data row in the Full Name field):



=A2 & " (" & B2 & ") - " & C2 & "[" & D2 & "]"

...and I drag fill the rest of the rows. (Just double-click where I indicated).

Next, Duplicates. I write this into E2 (the first data row in the Duplicate field)::

"=IF(COUNTIF(FName,$G2)>1,"TRUE","FALSE")"

...and I drag fill the rest of the rows.

If there's a duplicate entry somewhere in the database than this field will be "TRUE".

Next, Duplicate Under a Different Name. What does this do? One thing I frequently encountered is that there is no set way of writing a Japanese manga-ka's name in English, so frequently his name will be written as < Firstname > < Sirname> or vice versa < Sirname > < Firstname >. I wanted to find if I had an entry where the same manga-ka was already used but with a different order.

Writing this function was a bitch, as I had to evaluate several things. First I had to detect whether there was more than one word in the field. If not, a duplicate entry can't be found under a different name. Then I had to evaluate whether the manga-ka first and last name were the same (due some inane pseudo name like "Muchi Muchi" or something).

So here's the function. I write this into D2 (the first data row in the Duplicate field):

"=IF(ISERR(SEARCH(" ",$B2,1)),"FALSE",IF(OR(COUNTIF(Artist,CONCATENATE(MIDDLE($B2,SEARCH(" ",$B2,1)+1,LEN($B2))," ",LEFT($B2,SEARCH(" ",$B2,1)-1)))<1,MIDDLE($B2,SEARCH(" ",$B2,1)+1,LEN($B2))=LEFT($B2,SEARCH(" ",$B2,1)-1)),"FALSE","TRUE"))"

...and I drag fill the rest of the rows.

Finally I want Excel to automatically highlight errors. I select all the data. (I go to A2, then press Ctrl-right,then Ctrl-down), then I select Format-->Conditional Formating.



I write the following entries



"Formula is" --> =$E2="TRUE" --> Set up formatting for duplicate entries
"Formula is" --> =$F2="TRUE" --> Set up formatting for duplicate under different name.

You can read more about conditional formatting here:
http://www.contextures.com/xlCondFormat02.html

That's a lot of info, whoa my brain has to catch up. Besides that nice method. Love the step by step tutorial you give out.

~my head hurts,anyway thanks,new info for me.....~

tee hee, OFSZET. tee hee.
also. that guide, is awesome.
too bad I don't collect hentai, but I advise anyone who does to try this out.

Really cool guide. I would need ages to format my collection for this to work, though... ;_;

Haha, what's whit that windows ? Are you hungarian too ?

You can use this with other formatting too, you just need to modify the "find & replace" parameters. If you post the format you use I can help you.

Whoa dude...that took a lot of work to write up. If you want to do more with your end product, make a table out of it, you can sort it anyway you want on the fly.

That's the problem... my stuff is all over the place in terms of formatting. The only consistent ones are maybe the comiket doujins, the seem to follow a guideline...

But thanks for the offer!

To the asshole who -reps me for bumping a guide that took quite a while to write and may be useful: thank you. You prove that the human race isn't worth the air it consumes each year. Thank you, you're affirming my misanthropy.

To the one who +rep-ed me: Thank you, but you don't really have to fight these guys. Rep is just another e-penis, nothing of real importance. I hope the guide gave you some useful information.

nice guild il check it out when i got time :)

Thanks man, now i can catalog all my collection of H.... Hahahaha

NO SHIT!

Ki gondolta volna?

I feel if any collection has to come to this, it miiiiiight just be too big.

Plus, storage space online is free and easy, and Fakku's got all my faves. I stopped saving to my HD ages ago.

Freaking awesome man!!!

Wow, you put a lot of thought into this. Nice work. Though I have all my stuff organized quite well anyway.

Hello everyone!

Since I wrote this guide, I've made a better method to create a catalog. This one doesn't involve the use of Word, instead we'll use Windows' own built in scripting system.

1. A VB Script to switch out characters in a text file.

First, we make a vb script file that we'll use to make our csv files:


Put this into note-pad as save as replace.vbs.

This script looks for a specific character in a text file and replaces it with another. We'll use it, to replace the characters you normally use to denote specific parts of a file name (ie. artist is always between {}) with semi-colons (;).

2. Next we write a batch file, to process our list files.



Put this into note-pad and store it as csv.bat. I keep this in a separate batch file, so whenever I need to make a list I can just call this with the right parameters.

All it does is call the vb script we wrote numeral times and replace varios characters. See that "%1" there? That means this batch file will need a parameter when calling it.

A csv file is a list of records that Excel (or other database/spreadsheet) software can access. Each record ends with an EOF character, and the fields are divided by semi-colons.

Since a listing by windows already separates items with EOF if we can change the characters that denote the different parts of the file/directory name we can get a useable csv file.

This only works with my own naming scheme:
Franchise {Artist} - Title [Translator].

If you use a different naming scheme, you'll have to modify what the separator character is. Ie. If you used Artist (Franchise) - Title and kept your stuff in .rar files, you'd have to replace " (" (yes, you need to get rid of the space too!) with ";" then ") -" with ";" finally ".rar" with "" (empty as we don't need that end of the file).

Finally we make the list of directory where our hentai is, then process it with our other batch file.

You'll have to replace < PATH > with where you keep your stuff (ie. C:\My Documents and Setting\User\Documents\Hentai. The batch file will create the new csv file where you run it.



Put this into note-pad and save it as make-list.cmd.
When you want to create your csv (and text listing file), just run this file.

I keep my stuff on an external, should that matter for this set up? I'm not terribly familiar with this sort of thing.

Edit: My external drive is not always connected.