Jump to content


Photo

Is there a utility to export esp info to csv?


  • Please log in to reply
28 replies to this topic

#16 ipmlj

ipmlj

    Guard

  • Members
  • PipPip
  • 153 posts

Posted 29 May 2014 - 06:31 PM

Are you sure you don't want keywords on a separate row? You can then copy the keyword rows to a different sheet and then link them. As you can see from the screenshot, there can be a lot of keywords and you'll need a really tiny font to see the whole sheet at 1920x1080.

Now that you mention it, it may be faster to put the keywords on seperate rows if they occupy one cell per keyword. The combined list as shown in your example is how I'll end up placing them. The only difference will be splitting them up into seperate columns.

 

Also, I completely forgot Weapons (WEAP records):

Record flags (if any), FormID, EDID, Full-name, kyewords, datavalue, dataweight, datadamage, DNAM - speed, DNAM - reach, EITM - Object Effect


Edited by ipmlj, 29 May 2014 - 06:32 PM.

  • 0

#17 fireundubh

fireundubh

    Thane

  • Mod Authors
  • PipPipPipPipPipPip
  • 350 posts

Posted 29 May 2014 - 07:08 PM

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 by fireundubh, 29 May 2014 - 09:05 PM.

  • 0

#18 ipmlj

ipmlj

    Guard

  • Members
  • PipPip
  • 153 posts

Posted 29 May 2014 - 09:57 PM

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 by ipmlj, 29 May 2014 - 10:14 PM.

  • 0

#19 fireundubh

fireundubh

    Thane

  • Mod Authors
  • PipPipPipPipPipPip
  • 350 posts

Posted 29 May 2014 - 11:29 PM

This is what my script will output now:

Posted Image
 
----------------------------------- 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 by fireundubh, 30 May 2014 - 02:56 AM.

  • 0

#20 fireundubh

fireundubh

    Thane

  • Mod Authors
  • PipPipPipPipPipPip
  • 350 posts

Posted 30 May 2014 - 03:30 AM

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: http://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 by fireundubh, 30 May 2014 - 07:22 AM.

  • 1

#21 ipmlj

ipmlj

    Guard

  • Members
  • PipPip
  • 153 posts

Posted 30 May 2014 - 05:29 PM

@Fireundubh

 

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


  • 0

#22 EssArrBee

EssArrBee

    Incompatibilism Manager

  • STEP Staff
  • PipPipPipPipPipPipPipPipPipPipPipPipPipPipPipPipPipPipPipPipPipPipPipPipPipPipPipPip
  • 7,721 posts

Posted 30 May 2014 - 05:40 PM

@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.



#23 ipmlj

ipmlj

    Guard

  • Members
  • PipPip
  • 153 posts

Posted 30 May 2014 - 06:42 PM

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 by ipmlj, 30 May 2014 - 06:43 PM.

  • 0

#24 fireundubh

fireundubh

    Thane

  • Mod Authors
  • PipPipPipPipPipPip
  • 350 posts

Posted 30 May 2014 - 06:49 PM

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.
  • 0

#25 EssArrBee

EssArrBee

    Incompatibilism Manager

  • STEP Staff
  • PipPipPipPipPipPipPipPipPipPipPipPipPipPipPipPipPipPipPipPipPipPipPipPipPipPipPipPip
  • 7,721 posts

Posted 30 May 2014 - 07:01 PM

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? 



#26 ipmlj

ipmlj

    Guard

  • Members
  • PipPip
  • 153 posts

Posted 30 May 2014 - 07:06 PM

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 by ipmlj, 03 June 2014 - 02:49 PM.

  • 0

#27 fireundubh

fireundubh

    Thane

  • Mod Authors
  • PipPipPipPipPipPip
  • 350 posts

Posted 30 May 2014 - 11:20 PM

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 by fireundubh, 30 May 2014 - 11:20 PM.

  • 0

#28 sm0kem

sm0kem

    Thane

  • Members
  • PipPipPipPipPipPip
  • 412 posts

Posted 03 April 2018 - 04:32 PM

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


  • 0

#29 danielleonyett

danielleonyett

    Guard

  • Members
  • PipPip
  • 130 posts

Posted 24 May 2018 - 12:03 PM

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


  • 0


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users