Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 153 Stack Exchange communities.

What is meta?
Here's how it works:
  1. Any Stack Exchange user can ask a question
  2. The community provides support, votes on ideas, and reports bugs
  3. Your voice helps shape the way Stack Exchange operates

Inspired by this question, perhaps we could collect together a bunch of scripts to convert the XML data-dump files into other formats, such as SQLite, MySQL and the likes.

To keep things consistent, some guidelines:

If something changes in the data-dump, and the old script no longer works, make a new post for the updated script.

Don't depend on the machine having lots of memory, reading the entire set of XML files into memory is probably going to cause a lot of machines to run out of memory - some of these questions should help with this..

Mention version numbers of things you are using, well, relevant things, like the programming language and any libraries. Linking to non-standard modules and such would be nice too

Use the following template for all posted scripts:

# {New format name}

{Implementation language}

Tested and working on release(s):

- {List of releases the script is known to work on}



For example:


Python 2.6.4

Tested and working on release(s):

  • Oct 2009
  • Nov 2009

import sqlite3
import magic
share|improve this question
It would be pretty nice to have a way to render the dumps the same way the SE sites are normally rendered in the browser. – DanielSank Jun 17 '15 at 1:16

Python script to import/create SQLite3 database from SO data dump


  • Python 2.5+
  • lxml
import sqlite3
import os
import xml.etree.cElementTree as etree
import logging

 'badges': {
 'comments': {
 'posts': {
  'PostTypeId':'INTEGER', # 1: Question, 2: Answer
  'ParentID':'INTEGER', # (only present if PostTypeId is 2)
  'AcceptedAnswerId':'INTEGER', # (only present if PostTypeId is 1)
  'OwnerUserId':'INTEGER', # (present only if user has not been deleted) 
  'LastEditorDisplayName':'TEXT', #="Rich B" 
  'LastEditDate':'DATETIME', #="2009-03-05T22:28:34.823" 
  'LastActivityDate':'DATETIME', #="2009-03-11T12:51:01.480" 
  'CommunityOwnedDate':'DATETIME', #(present only if post is community wikied)
 'votes': {
           # -   1: AcceptedByOriginator
           # -   2: UpMod
           # -   3: DownMod
           # -   4: Offensive
           # -   5: Favorite
           # -   6: Close
           # -   7: Reopen
           # -   8: BountyStart
           # -   9: BountyClose
           # -  10: Deletion
           # -  11: Undeletion
           # -  12: Spam
           # -  13: InformModerator
 'users': {

def dump_files(file_names, anathomy, 
    dump_database_name = 'so-dump.db',
    create_query='CREATE TABLE IF NOT EXISTS [{table}]({fields})',
    insert_query='INSERT INTO {table} ({columns}) VALUES ({values})',

 logging.basicConfig(filename=os.path.join(dump_path, log_filename),level=logging.INFO)
 db = sqlite3.connect(os.path.join(dump_path, dump_database_name))

 for file in file_names:
  print "Opening {0}.xml".format(file)
  with open(os.path.join(dump_path, file + '.xml')) as xml_file:
   tree = etree.iterparse(xml_file)
   table_name = file

   sql_create = create_query.format(
        fields=", ".join(['{0} {1}'.format(name, type) for name, type in anathomy[table_name].items()]))
   print('Creating table {0}'.format(table_name))

   except Exception, e:

   for events, row in tree:

        columns=', '.join(row.attrib.keys()), 
        values=('?, ' * len(row.attrib.keys()))[:-2]),
     print ".",
    except Exception, e:
     print "x",
   print "\n"

if __name__ == '__main__':
 dump_files(ANATHOMY.keys(), ANATHOMY)
share|improve this answer
Could someone with edit permission please update the ANATHOMY declaration in the script above with the following: - Under 'votes', add 'BountyAmount':'INTEGER', - Under 'users', change 'DisplayName':'DATETIME' to 'DisplayName':'TEXT' - Under 'users', add 'EmailHash':'TEXT' - Under 'badges', change 'name' to 'Name' and 'date' to 'Date' Not sure if the badges change affects anything, but the others definitely affect what ends up in the database. – Bryce Thomas Aug 27 '10 at 12:58
Nearly one year later, I did so. :) On Linux, change dump_path='c:\\temp\\' to something else - I would suggest "." for all systems. Executing it, I get an error about missing 'vote.xml' - guess I have to find it somewhere. :) – user unknown Jul 29 '11 at 3:33
I slightly improved this code. It's on github. Now it works well with 2014.05 stackoverflow dump. – testlnord Aug 6 '14 at 14:10

C++ (SQLite3, PostgreSQL)

Tested with the 2009-11-01, 2009-12-01, 2010-01-01 dumps of the trilogy.

Columns containing numeric values are converted to numbers, columns containing dates are converted to Unix timestamps and stored as numbers. Optionally indexes are added for all numeric columns. The import takes about 5-10 minutes for the pure data and about 10 minutes more if generating indexes.


To generate a SQLite database, start the sqliteimport program in a directory containing the data dumps XML files and it creates a dump.db in the same directory. If you don't want indexes (creating them takes some time), start the program with sqliteimport -I.

PostgreSQL support is still a little bit experimental. To generate a PostgreSQL database, start the pgcopyimport program in a directory containing the data dumps XML files. Pass a -c flag with the necessary database information, like pgcopyimport -c "dbname=sodump user=so password=abc". The database needs to exist. Warning: This program creates big temporary files in /var/tmp, about as big as the single XML files. This is done to quickly load the data into the database with COPY FROM. The the temporary files are named badges.pgdata,...

If you don't want to create temporary files use the pgimport program instead, but this will be a lot slower. PostgreSQL import also skips the generation of indexes if the -I flag is added.

Other uses

The design should also be fairly flexible if you want to extract special data from the XML files, parse different XML files, or create something else than a SQLite/PostgreSQL database from the input.

share|improve this answer

I have a short C# program to help import the Posts table into SQL Server. You can see an old version here:
Scripts to convert data-dump to other formats.

If I remember and have time at home later I'll post updated code. The other tables were imported via Brent Ozar's instructions (with some modifications for schema changes):

share|improve this answer

So-Slow, is by far the fastest way to get data into SQL Server. It is still able to process current dumps in under 10 minutes, including tag splitting.

share|improve this answer

If you are importing into MySQL, you might want to use the following MySQL script:

No extra dependencies required

share|improve this answer
I just used this, it was a little clunky but totally handled its business – Kristian Jan 22 '15 at 18:48

MS SQL Server 2000/05/08 - MySQL 5.1 - SQLite3


Tested and working on release(s):

  • March 2010 - all sites, any way you want it. 5 to 17 minutes depending on options

Here is a Windows application that will import to MySQL, MS SQL Server and SQLite:

share|improve this answer

.xml data dump files to R data frame

I think that this code could be (somewhat) useful if someone need data in R but for some reason want to read this .xml data directly. But remember that R is still R and (if dataset will be to big for resources you have) could crash due to lack of RAM.


xmlDataDumpToDataFrame<- function(xmlFilePath){

  doc <- xmlParse(xmlFilePath)  
  xmlList<- xmlToList(doc)


  print(paste0("Parsing file : ", xmlFilePath ))
  progressBar <- txtProgressBar(min = 0, max = total, style = 3)


  for(i in 1: total){
    data <- rbind.fill(data, xmlList[[i]])))
    setTxtProgressBar(progressBar, i)

  print(paste0("created object size : ", format(object.size(data), units= "Mb") ))


Usage example:

usersData <- xmlDataDumpToDataFrame("")

As you can noticed, I also added a progress bar and an information how big created data frame is.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .