Editing DBCs With SQL

There are many situations where creating SQL queries for DBCs is to die for, so I finally decided to make it happen. 
SQL queries for DBCs is very useful for mass operations or combining DBC data. It's also useful for version updates.

In this tutorial I will teach you how to convert your DBCs to SQL files, which can then be stored on MySQL servers.
I will also show you how to export your DBC tables back to the CSV format so it can then be converted back to DBC.

Things You Will Need:

Step 1: Converting DBC to CSV

First we must convert our desired DBC file to CSV, as there is no other way to reliably import it to MySQL.
Simply drag and drop your DBC file onto the DBC Util tool and it will automatically convert to CSV. 

Step 2: Setting Up Your DBC Table

Before we are able to import a CSV file into MySQL, we must first create a table with proper columns and datatypes.
There are two methods to choose from here. We can either enter everything manually, or create an SQL file instead.

:-:-:-:-: Method A - Manual Creation:

Using HeidiSQL, right click your server at the top left and select Create New -> Database. I named mine "dbc".
After that, right click your new database and select Create New -> Table. For example, I created "lightintband".

Select your new table and navigate to the "Table: lightintband" tab. This is where we will create our columns.
Depending on the DBC you're importing, you must make the correct amount of columns. This must be exactly the same.

Then you can start adding columns with the green button. Name them whatever you want, but make sure the datatype
is always set to VARCHAR due to the DBC files utilizing lots of hexadecimal values. Do this or it won't work.
Make sure that the settings Unsigned, Allow NULL, and Zerofill are all unchecked. Also keep the Defaults at 0.

:-:-:-:-: Method B - SQL File Creation:

Since above I mentioned our Database name is "dbc" and our example table is called "lightintband" we'll run this: 

CREATE DATABASE IF NOT EXISTS `dbc`
USE `dbc`;
CREATE TABLE IF NOT EXISTS `lightintband` (
  `Field01` varchar(50) NOT NULL DEFAULT '0',
  `Field02` varchar(50) NOT NULL DEFAULT '0',
  `Field03` varchar(50) NOT NULL DEFAULT '0',
  `Field04` varchar(50) NOT NULL DEFAULT '0',
  `Field05` varchar(50) NOT NULL DEFAULT '0',
  `Field06` varchar(50) NOT NULL DEFAULT '0',
  `Field07` varchar(50) NOT NULL DEFAULT '0',
  `Field08` varchar(50) NOT NULL DEFAULT '0',
  `Field09` varchar(50) NOT NULL DEFAULT '0',
  `Field10` varchar(50) NOT NULL DEFAULT '0',
  `Field11` varchar(50) NOT NULL DEFAULT '0',
  `Field12` varchar(50) NOT NULL DEFAULT '0',
  `Field13` varchar(50) NOT NULL DEFAULT '0',
  `Field14` varchar(50) NOT NULL DEFAULT '0',
  `Field15` varchar(50) NOT NULL DEFAULT '0',
  `Field16` varchar(50) NOT NULL DEFAULT '0',
  `Field17` varchar(50) NOT NULL DEFAULT '0',
  `Field18` varchar(50) NOT NULL DEFAULT '0',
  `Field19` varchar(50) NOT NULL DEFAULT '0',
  `Field20` varchar(50) NOT NULL DEFAULT '0',
  `Field21` varchar(50) NOT NULL DEFAULT '0',
  `Field22` varchar(50) NOT NULL DEFAULT '0',
  `Field23` varchar(50) NOT NULL DEFAULT '0',
  `Field24` varchar(50) NOT NULL DEFAULT '0',
  `Field25` varchar(50) NOT NULL DEFAULT '0',
  `Field26` varchar(50) NOT NULL DEFAULT '0',
  `Field27` varchar(50) NOT NULL DEFAULT '0',
  `Field28` varchar(50) NOT NULL DEFAULT '0',
  `Field29` varchar(50) NOT NULL DEFAULT '0',
  `Field30` varchar(50) NOT NULL DEFAULT '0',
  `Field31` varchar(50) NOT NULL DEFAULT '0',
  `Field32` varchar(50) NOT NULL DEFAULT '0',
  `Field33` varchar(50) NOT NULL DEFAULT '0',
  `Field34` varchar(50) NOT NULL DEFAULT '0',
  PRIMARY KEY (`Field01`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Please note that I have 34 columns entered. That's because LightIntBand.dbc has 34 columns, at least on Cataclysm. Your situation may vary depending on which version you're on and which DBC files you're using. Adjust accordingly.

Step 3: Importing CSV into MySQL:

Select your database table which should now be populated with columns, and select Tools -> Import CSV file... Browse to the CSV file that you converted with DBC Util earlier. Select INSERT under "Handling of duplicate rows". Press "Import!" You should now have a complete table for your DBC without error. Create queries and edit this however you see fit.

Step 4: Exporting Back to CSV Format:

With MySQL all you have to do is create a new query to export your database to CSV. This is what I prefer using:

SELECT * FROM lightintband 
INTO OUTFILE 'C:\\LightIntBand.dbc.csv' 
FIELDS TERMINATED BY ','

Step 5: Converting Back to DBC Format:

Now you should be ready to convert CSV back to DBC, but there's one more thing you must do. I'm not sure what to call it, but there is important information at the top of the original CSV file that is lost when added to MySQL. Before you overwrite the original CSV file, open it up in a text editor and copy the first line. In my case it's:

long,long,flags,long,long,long,flags,long,long,flags,flags,long,long,flags,flags,
flags,long,long,long,long,long,long,long,long,long,long,long,long,long,long,flags,
long,flags,flags,

Make sure you copy this from the original CSV file and paste it into the new one exported from your MySQL.
Then you should finally be ready to switch back to DBC. Just drag and drop the new CSV file onto DBC Util  

Note About Converting DB2 to SQL for Cata and Up:

If you're looking to do these same methods for DB2 editing, you'll have to use two special programs for conversion.
The DB2 to SQL was a little outdated (item-sparse.db2 conversion didn't work) so I made an adjustment and attached 
the updated version to this thread. For SQL to DB2, I've uploaded another program. 

Just remember that you can only edit Item.db2 and Item-sparse.db2. Others are not currently supported.

DBC2ConverSQL.zip

SQLtoDB2.zip