Is there a utility to export esp info to csv?

[ipmlj]

I've been looking around for a utility that can export esm/esp information to a csv file.

 

The only partial solution I can find is Wrye Bash. However, the information it can export is limited and there aren't any options that I can find to change the data that is exported.

 

Purpose: I'm compiling excel worksheets that contain data on ammunition, armor, weapons, recipes, etc.. to find inconsistencies and duplicates so I can patch them.

 

For Example: ARMO data - EDIDs, MODIDs, NAMEs, Weight, Value, Armor Rating, Keywords, Flags (i.e. not playable). I can get some of these through the Wrye Bash export function but have to combine the data from the different files but am still missing data such as keywords and flags.

 

Edit: The only other extremely tedious solution is using a text capture program to copy paste info from the CK. It works but takes forever when dealing with over 12,000 items.

Edited May 28, 2014 by ipmlj

[fireundubh]

If you're using Excel, I can also separate keywords by newlines in a cell. Let me know.

 

Example:

="ArmorClothing"&CHAR(10)&"ClothingHands"&CHAR(10)&"MagicDisallowEnchanting"&CHAR(10)&"VendorItemClothing"

edit: I did one better. In the keywords cell, I've placed a link that you can click to take you to the right row in Sheet2, which would contain the keywords for each Form ID. Edited May 30, 2014 by fireundubh

[ipmlj]

Back from some great baseball games tonight! LL is so much fun to watch.

 

Getting back on track..

 

The main thing is to have all the information on the same sheet which can be easily turned into a table for filtering. (I use the columns as various categories.) When I was manually making a similar sheet (lol..took me three weeks to input data for about 1500 items) I realized having the information on a single row makes it much easier to compare the data after filtering. Data extending far off the sheet is not as much a problem in MS excel. Not sure about Open excel as I haven't used it. Columns can be hidden and whatnot so the other data can be viewed as needed. Honestly, I'm not even sure I could filter both horizontally and vertically simultaneously. (other than just hiding rows that I don't need to see)

 

For example, if there are 20 items with the word "Falmer" in them (name or edid)..from several different mods, I want to be able to quickly compare the keywords to see if (for example) "WAF_Factionlearning_KRY" or whatever is present or missing. The vertical space is more vital than the horizontal spacing if there are alot of items post filtering if that makes any sense.

 

There are probably better ways to do it. I know you can link data and formulas from one sheet to the other, I'm just not as experienced with the more advanced capabilities in excel.

Edited May 30, 2014 by ipmlj

[fireundubh]

This is what my script will output now:

 

 

----------------------------------- SHEET1 ------------------------------------FormID	EDID	FULL	Rating	Value	Weight	Object Effect	Keywords	Flags0x00012E4B	ArmorIronBoots	Iron Boots	10.000000	25	6.000000		=HYPERLINK("#'Sheet2'!"&ADDRESS(MATCH(INDIRECT(ADDRESS(ROW(), COLUMN()-7,4)), Sheet2!$A:$A, 0), 1), "Link")0x00012E4D	ArmorIronHelmet	Iron Helmet	15.000000	60	5.000000		=HYPERLINK("#'Sheet2'!"&ADDRESS(MATCH(INDIRECT(ADDRESS(ROW(), COLUMN()-7,4)), Sheet2!$A:$A, 0), 1), "Link")0x00012EB6	ArmorIronShield	Iron Shield	20.000000	60	12.000000		=HYPERLINK("#'Sheet2'!"&ADDRESS(MATCH(INDIRECT(ADDRESS(ROW(), COLUMN()-7,4)), Sheet2!$A:$A, 0), 1), "Link")	=HYPERLINK("#'Sheet3'!"&ADDRESS(MATCH(INDIRECT(ADDRESS(ROW(), COLUMN()-8,4)), Sheet3!$A:$A, 0), 1), "Link")0x00013104	ClothesBeggarHat	Ragged Cap	0.000000	1	0.500000		=HYPERLINK("#'Sheet2'!"&ADDRESS(MATCH(INDIRECT(ADDRESS(ROW(), COLUMN()-7,4)), Sheet2!$A:$A, 0), 1), "Link")0x00013105	ClothesBeggarRobes	Ragged Robes	1.000000	1	1.000000		=HYPERLINK("#'Sheet2'!"&ADDRESS(MATCH(INDIRECT(ADDRESS(ROW(), COLUMN()-7,4)), Sheet2!$A:$A, 0), 1), "Link")0x00013106	ClothesBeggarBoots	Ragged Boots	0.000000	1	1.000000		=HYPERLINK("#'Sheet2'!"&ADDRESS(MATCH(INDIRECT(ADDRESS(ROW(), COLUMN()-7,4)), Sheet2!$A:$A, 0), 1), "Link") ----------------------------------- SHEET2 ------------------------------------FormID	Keyword	Keyword	Keyword	Keyword	0x00012E4B	ArmorBoots	ArmorHeavy	ArmorMaterialIron	VendorItemArmor0x00012E4D	ArmorHeavy	ArmorHelmet	ArmorMaterialIron	VendorItemArmor0x00012EB6	ArmorMaterialIron	ArmorShield	VendorItemArmor0x00013104	ArmorClothing	ClothingHead	VendorItemClothing0x00013105	ArmorClothing	ClothingBody	ClothingPoor	VendorItemClothing0x00013106	ArmorClothing	ClothingFeet	VendorItemClothing ----------------------------------- SHEET3 ------------------------------------FormID	Flag	0x00012EB6	Constant HiddenFromLocalMap BorderRegion HasTreeLOD

In Excel, you'd copy Sheet3 first, then Sheet2, and then Sheet1, so that the hyperlinks in Sheet1 resolve without manual intervention.

 

I have all record types done except COBJ. edit: Done.

 

I'm going to implement a way to add Keyword/Flag column headings dynamically, so that if a record has 10 keywords and there are 10 keywords at most in the selected records, then there'd be 10 Keyword column headings in Sheet2. edit: Done.

 

edit: I also made the reference to the FormID cell relative, so that it's no longer hardcoded (i.e., you don't have to copy the header anymore.)

Edited May 30, 2014 by fireundubh

[fireundubh]

Purpose: Export AMMO, ARMO, COBJ, MISC, and WEAP RecordsGame: The Elder Scrolls V: SkyrimAuthor: fireundubhVersion: 0.1HOW TO USE:1. In Excel, create four worksheets named: Main, Keywords, Flags, and Components.2. Select any number of records of A SINGLE TYPE (e.g., AMMO, ARMO, COBJ) and apply the script.3. Import each CSV in reverse order into the respective Excel worksheets.   NOTE: Reverse order is important because the Main worksheet will contain hyperlinks to the other worksheets and Excel is stupid.4. The Keywords, Flags, and Components links may not be formatted correctly but they will still work. To update the formatting:	4a. Use the arrow keys to highlight the topmost unformatted links cell.	4b. Press F2.	4c. Press Enter.	4d. Repeat until you're finished.5. Tell fireundubh how much you love and adore him for making you this script. Kneeling is optional but encouraged.

You can download the script here:

https://pastebin.com/raw.php?i=i8LtJ5PU

 

And here's a blank XLSX file with four pre-named worksheets:

https://copy.com/oHe3ZnCP4Pb4

 

edit: I rewrote that script to export .csv files instead of outputting messages.

Edited May 30, 2014 by fireundubh

[ipmlj]

@Fireundubh

 

Can't wait to put it to use. I'd give you my firstborn but I already promised that to Sheshon.

[EssArrBee]

@Fireundubh

 

Can't wait to put it to use. I'd give you my firstborn but I already promised that to Sheshon.

I think about half of us have done the same.

 

@fireundubh, you should maybe tell the UPP guys about this script. I bet they'd find quite a bit of use out it or at least curse themselves for having already gone through all the records manually.

[ipmlj]

Its absolutely beautiful, works like a charm!

 

2763 records exported in 49 seconds. Took me a few minutes to figure out how to turn on the excel Power pivot add-in. Time saved = priceless.

 

Gosh man, thank you a million times over. Would have taken me a month or longer to do it by hand! Now, I just need to export about 41 more data sets..hehe

Edited May 30, 2014 by ipmlj

[fireundubh]

No problem.

 

By the way, I could modify the script so that the various FormIDs are maintained in the exported data.

 

I opted to export only EDIDs because I figured you'd find those more useful for manual comparison.

 

LMK.

[EssArrBee]

EDIDs or FormIDs can both be searched in the CK and TES5Edit so it really doesn't matter.

 

Would there be an Libre office version of that excel power pivot add-on? 

[ipmlj]

Not sure EssArrBee. I was surprised I had to turn it on. I don't remember having to activate it in (2007 version). I'm sure others will want to know if you do find it.

 

I'll share the stuff I put together in my github repository for whomever wants to use them as well.

 

 

Edit: it took me a bit longer to get the first one created..other stuff kept delaying it...

 

Here is the repository where I am storing worksheets for SRLE. The ARMO sheet has combined data from 40 mods. The format is slightly different than the raw output but fireundubh's script made it possible to compile all these records. There are 9,429 ARMO records in the SRLE build.

Edited June 3, 2014 by ipmlj

[fireundubh]

EDIDs or FormIDs can both be searched in the CK and TES5Edit so it really doesn't matter.

In xEdit, EDID queries are super slow relative to FormIDs. Edited May 31, 2014 by fireundubh

[sm0kem]

Hey guys,

 

Sorry to reanimate an old thread, but i wanted to see if i can get my hands on this script, if you still have it.

 

Thanks

[danielleonyett]

Hey guys,

 

Sorry to reanimate an old thread, but i wanted to see if i can get my hands on this script, if you still have it.

 

Thanks

I'll pm you