Using Excel for mass import of data into db.xml

Hello Folks,
I'm not certain if anyone would find this interesting or not, but it worked nicely for me when I had to fill in data for my GURPS TRAVELLER campaign. It would also work nicely enough for use with your needs to import mass quantities of equipment data etc.

What you need to make this work is some Excel savvy, an add-in for Excel in the form of the XML Add-in for 2003 (it works with my version of 2007 by the way), some patience as you discover how to work with the xml Add in, and a decent text editor. That's it. That's all you will need.

First, you need to know what xml structures are required for the "thing" you want to do for your massive data import. For example, lets say you have 42 firearms you wish to add to your campaign, and dread having to enter them one at a time...

Open a new spreadsheet in Exel (note: If you do not have the xml add in, this whole process is moot, you absolutely need the xml add in for it to work)
Reserve row 1 for the "element names". This is what the xml add-in will use to create for your tags. For instance, if you had a tag name of <Range> </Range> you will want to enter into the cell for line one of that column "Range".

In cell A2, enter in the value of 1.
After you hit enter, the focus in Excel drops to Cell A3 or something like that. Click again on cell A2.
Look for the Autosum symbol in the top menu area (Looks like an odd looking E). Directly under that is the word Fill in tiny letters on an icon. Click Fill.
A drop down menu appears, select "Series"
A menu box shows up with varying items. Select "Columns" for type, and enter in a stop value of 42 - then ok.
What should happen next is that it auto-numbers all cells from A2 to A43 with the value of 1+ the previous cell above it.
In Cell B2, enter the following formula:
="id-" & TEXT(A2,"00000")
Note that this will make it appear as id-00001. Coincidentally, this is the value that db.xml uses to label each "thing" in your database for that section.

Now, this is what the "template" looks like for ranged weapons:

<id-00001>
<public />
<acc type="string">4</acc>
<bulk type="string">-4</bulk>
<cost type="string">400</cost>
<damage type="string">4d+1 pi</damage>
<isidentified type="number">1</isidentified>
<lc type="string">3</lc>
<locked type="number">0</locked>
<name type="string">Carbine, 7.62 mm</name>
<nonid_name type="string">Carbine, 7.62 mm</nonid_name>
<nonid_notes type="string">Winchester M1</nonid_notes>
<notes type="formattedtext">
<p></p>
</notes>
<range type="string">330/2,100</range>
<rcl type="string">2</rcl>
<rof type="string">3</rof>
<shots type="string">15+1(3)</shots>
<st type="string">8†</st>
<subtype type="string">Semi-automatic Carbine</subtype>
<tl type="string">6</tl>
<type type="string">Ranged Weapon</type>
<weight type="number">5.8</weight>
</id-00001>

Everything that has a <> and </> tag will need to have a column name in row 1 of that column, that matches precisely the name inside above. What the Excel can't do is give a data types (those "type="String" or "Type=formatted text" things). Those you will have to fix manually with a text editor.

Point is - you can enter into your excel spreadsheet, all of the data listed above - such that each row is one gun.

Now comes the fun part. You've spent some time entering in the data, you've created your structure in excel, and you're ready to export the data in the spread sheet out to an xml formatted file.

VERY Important. Save that excel spreadsheet as an xls file (ie standard excel workbook file). The reason you do that is because once you use the xml add in feature, it turns those things you want to save in xml formatting - as a table, that the xml add-in function will utilize. Seems that you can only do this once per sheet for some odd reason.

Ok, so now you want to export what you've labored upon. Go to the add in area from your spreadsheet menu - select xml tools
a drop down menu will appear. Select "Convert a range" to an XML List. This is the process by which the entire range of cells to be turned into XML formatting comes into play (and gets named and all that fun stuff). It will change colors, look like a pivot table arrangement, and will also have alternating colors for the rows. Don't panic. This is good.

Now , the next step requires that you have turned on your "Developer tab". If you don't know how to do this (and many won't), don't panic, Google is your friend and the process is outlined to where someone like myself can follow (and did). If I can do it, trust me, you can too.

There, in the Developer section, you will now see an XML section. In there, is an export function. Click on that, and it will export your range of cells into its very own XML file. Here is a sample of what my Spinward Marches Data looks like courtesy of the Excel spreadsheet method that I used...

<?xml version="1.0" encoding="UTF-8" standalone="true"?>

-<Root xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance">


-<Row>

<HexId>0101</HexId>

<World_Name>Zeycude</World_Name>

<UWP>C430698-9</UWP>

<Trade_Notes>De Na Ni Po</Trade_Notes>

<Ix>-1</Ix>

<Ex>C53-1</Ex>

<Cx>6559</Cx>

<PBG>613</PBG>

<Worlds>8</Worlds>

<Alleg>ZhIN</Alleg>

<Primary_Star>K9 V</Primary_Star>

<Secondary_Star> </Secondary_Star>

<Tertiary_Star> </Tertiary_Star>

<WTN>4</WTN>

</Row>

Now for the final touches if you will. Remember when I mentioned that the Excel method won't add in the finishing touches such as "Type" etc? That you will have to furnish on your own via the search/replace function with your text editor.

<id-00001>
<public />
<acc>4</acc>
<bulk>-4</bulk>
<cost>400</cost>
<damage>4d+1 pi</damage>
<isidentified>1</isidentified>
<lc>3</lc>
<locked>0</locked>
<name>Carbine, 7.62 mm</name>
<nonid>Carbine, 7.62 mm</nonid_name>
<nonid_notes>Winchester M1</nonid_notes>
<notes">
<p></p>
</notes>
<range>330/2,100</range>
<rcl>2</rcl>
<rof>3</rof>
<shots>15+1(3)</shots>
<st>8†</st>
<subtype>Semi-automatic Carbine</subtype>
<tl>6</tl>
<type>Ranged Weapon</type>
<weight>5.8</weight>
</id-00001>

What is above is what Excel will export. Clearly the "types" need to be added, and you will then have to use your search and replace to make them conform to what db.xml requires. Point is, you are just about where you want to be. Simply copy and paste the entire elements you have in your XML file starting with the <id-00001> and paste it where it belongs.

What is nice about the excel method is that - if you have already entered elements/things into your database, if your last "thing" ends at say, id-000231, on your spreadsheet, you can enter into cell A2, the value 232, use the fill method to include the next bunch of things you want to add, go through the process, and then copy and paste the result AFTER your last entry of id-000231.

Be aware that you NEVER do this while you're running the FG software. What happens is that FG does not ever read the db.xml file while running. It may accept changes you enter in via FG, and every increment of time (10 minutes?) save what it has to the file, but it does not ever read it while running. You run the risk of it overwriting your work.

Ultimately, this process is intended to save you the drudgery of having to open a new item, fill in the fields, then close it and open a new one, enter the data, etc. This way permits you to do it all at the same time on your spreadsheet, export it, tweak it, then enter it in its final form.

Take for instance, the <notes> example above. Excel will NOT put it in that same structure. It will be <Notes> </Notes> on the same line. You will have to do that manually to get it on its own line. Notepad++ has this wonderful functionality that lets you add in a line feed via its search and replace function, so you could search on <notes> </notes> and replace it with
<notes> \r\n \t\t<P></P>\r\n </notes>

The \r\n is a new line feed. the \t is an embedded tab. That replace feature will replace the original item that was on one line, and create three lines in its stead. You need to select extended mode for the search type for that to work however...

In any event - give this a try if you have a lot of data to enter all at the same time. I suspect too that you may find yourself running into issues where something is not working right. By all means, either contact me via private message, or tell me in a thread or two (assuming I see it!) "Hey Hal, can we connect via Fantasy Grounds and discuss this real time? I need help."

If Ronnke can do the hard work he's done on this (along with a lot of others!!!), then I can do the same in my own small way.

If it ever comes to pass that I get my other project off the ground, then someday, I may feel confident enough to write a VB.NET app that will work directly with the db.xml file so that it permits people to simply work with excel spreadsheets, and permit them to work with it directly. Either that, or if I learn how to make a Datagridview container (think of it as an excel spreadsheet grid arrangement) such that one can use it directly, enter data in a row by row structure - and then export the entire beastie as an db.xml file that works - well, one can dream.

I still want to figure out a way to use those GURPS Data Files used with GURPS CHARACTER ASSISTANT, parse the files until it reaches a line saying "Equipment", read every single line to harvest equipment data (weight, cost, LC, Category, range, mode, damage, etc) and post that data in a visible screen area. The user could then select lines to delete that he doesn't want, or select cells to fix the data manually due to programmer ineptness (that's ME!) and then export the entire harvested data into a single xml file for use with db.xml.

In theory, I should be able to read an entire xml file into the vb.net app (Just found out how to do that last week in fact). But learning how to WORK with it is the rub.

How simple is the code? See below. The problem with reading the db.xml file is that it contains several "tables" instead of just one. I don't have the skill as yet to be able to use schemas or break down a single xml with multiple schemas embedded within, to code it. THAT is going to take a long time until I finally figure it out (self-taught and all). By the by? Remember that xml example I gave above with the Spinward Marches up stream? THAT is the schema that this code below reads.

Imports System.Xml
Imports System.Data.Sql
Imports System.Data


Public Class Form1




Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim xmlFile As XmlReader
Dim ds As New DataSet


xmlFile = XmlReader.Create("C:\Users\Hal\Documents\GDW\T4\Sp inwardMarchesEnhancedData.xml", New XmlReaderSettings())



ds.ReadXml(xmlFile)



ds.Tables(0).TableName = "StellarData"

'For Each tables In ds.Tables
'MsgBox(tables.TableName)
'Next

'this allows me to find specific information within a table. Format is tablename, row desired, then column name.
'MsgBox(ds.Tables("StellarData").Rows(4)("HexId"))


DataGridView1.DataSource = ds.Tables("StellarData")


'This section of code keeps any datagridview column from being sortable. The only reason to do this is to avoid
'changing the order within a bound control accessing a Dataset.

For i = 0 To DataGridView1.ColumnCount - 1
DataGridView1.Columns(i).SortMode = DataGridViewColumnSortMode.NotSortable


Next
DataGridView1.Columns(0).Width = 50
DataGridView1.Columns(4).Width = 25

ListBox1.DataSource = ds.Tables("StellarData")
ListBox1.DisplayMember = "HexId"

End Sub

Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged

TextBox1.Text = DataGridView1.CurrentRow.Cells("HexId").Value
TextBox2.Text = DataGridView1.CurrentRow.Cells("World_Name").Value

End Sub