40

UPDATE:

Both soapi.info and skysanders.net have been offline for a while.

The application readme file and hosting will be moved to http://soapi.info/tools/soddi.aspx.

Links are in the readme text below.

??? SODDI dialog

Stack Overflow Data Dump Import v.11
  ClickOnce Installer: http://skysanders.net/tools/se/soddi/publish.htm

  (c) 2010 Sky Sanders
  licensed under MIT/GPL - see license.txt

  msi :http://skysanders.net/files/soddi.11.msi
  info:http://skysanders.net/tools/se
  bin :http://skysanders.net/files/soddi.11.zip
  src :http://bitbucket.org/bitpusher/soddi/

SODDI is a .NET 3.5 SP1 executable written in C# that quickly and cleanly imports Stack Overflow 
Data Dump XML files into [SQL Server][3] 2000/05/08, [MySQL][4] 5.1 and [SQLite][5] 3 
(MySQL and SQLite drivers are included).

SODDI can be run as a command line utility or, when invoked with no arguments or GUI argument, 
will present a [Windows Forms][6] interface.

Quick Start:
The quickest route to your own copy of the Stack Overflow databases is to use the ClickOnce installer,
browse to the uncompressed data dump, accept the default SQLite provider selection and click 'Import'.

USAGE:

soddi.exe source:"" target:"" [batch:5000] [split] [indices] [fulltext] [[meta] [so] [su] [sf]] [gui]


SOURCE          The directory containing the individual site directories.
                NOTE: do not include trailing slash in quoted path as the arg
                parser will interpret it as an escaped quote and puke.

TARGET          A valid ADO.NET connection string, including the provider invariant
                name.

                Platform specific connection string details:

                SQL Server: the database must exist. Data will be loaded into tables segregated by
                schema named as the site data being imported. e.g. so.Users, meta.Users.
                The tables are dropped before import.

                MySQL: The connection string should include server, each site's data will be loaded
                into a database named as the site imported. The databases will be dropped and 
                recreated before import.

                SQLite: The connection string should specify a directory. The data will be imported
                into separate .db3 files, each named as the site imported. Existing data files
                will be overwritten.

                The target database/datafile/schema names can be modified by explicitly specifying
                sites to import and appending the desired schema as a parameter value or editing
                the Sites list item schema in the GUI.


-- OPTIONAL ARGUMENTS

SPLIT           Normalize post tags by splitting the concatenated Posts.Tags field into individual 
                rows in a separate PostTags table.

INDICES         Enables useful indexes on each table.

FULLTEXT        Enables a full text index on Posts.Body and Posts.Title - SQL Server only.

BATCH           Number of rows inserted in each transaction. Default 5000.

GUI             Presents a Windows Forms interface. If SODDI is invoked with arguments and GUI, the
                UI will be populated with the supplied arguments.

                The console window will remain open to receive all debug and error output.

META|SO|SU|SF   Specifies which sites to import. If none are specified, all site directories found
                in SOURCE will be imported.

                To specify a different target name simply treat the site name as a parameter.

                e.g. 

                SQL Server - SO:StackOverflowData will load the data from the XXXXX SO directory 
                into the database specified in the connection string and the schema 'StackOverflowData'

                MySQL - SO:StackOverflowData will load the data from the XXXXX SO directory 
                into a new database named StackOverflowData on the server specified in the connection
                string.

                SQLite - SO:StackOverflowData will load the data from the XXXXX SO directory into a 
                new db3 file named StackOverflowData.db3 in the directory specified in the connection
                string.

                In GUI mode you may edit the schema item in the Sites list.

Options are not case sensitive.

Example command lines.

GUI Mode:
    soddi 

SQLite - all sites:
    soddi source:"F:\Export-030110" target:"data source=c:\temp;version=3;new=True;Provider=System.Data.SQLite"

MySQL - all sites:
    soddi source:"F:\Export-030110" target:"server=localhost;user id=root;password=p@ssW0rd;Provider=MySql.Data.MySqlClient"

MySQL - Meta StackOverflow and StackOverflow data into specified databases:
    soddi source:"F:\Export-030110" target:"server=localhost;user id=root;password=p@ssW0rd;Provider=MySql.Data.MySqlClient" meta:MetaDb so:SoDb

SQL Server - all sites:
    soddi source:"F:\Export-030110" target:"data source=(local);initial catalog=SOData;integrated security=true;Provider=System.Data.SqlClient"

SQL Server - Stack Overflow data only (SO):
    soddi source:"F:\Export-030110" target:"data source=(local);initial catalog=SOData;integrated security=true;Provider=System.Data.SqlClient" so

SQL Server - Stack Overflow data only into schema dbo:
    soddi source:"F:\Export-030110" target:"data source=(local);initial catalog=SOData;integrated security=true;Provider=System.Data.SqlClient" so:dbo


SQL Server - Stack Overflow data only, split tags and add indices:
    soddi source:"F:\Export-030110" target:"data source=(local);initial catalog=SOData;integrated security=true;Provider=System.Data.SqlClient" so split indices

The latest data dump can be found at
http://blog.stackoverflow.com/category/cc-wiki-dump/


04/01/2010 - Sky Sanders

04/09/2010 - Explicitly set platform to x86 to allow same binaries to run on x64.
Improve this question
4
  • 2
    @Code-poet Congrats on an awesome tool!
    – Marty Pitt
    Commented Jun 23, 2010 at 15:19
  • This tool no longer works on the April 2011 data dump. Posts gets an error "Input string was not in a correct format" and Users gets an error of "The given value of type String from the data source cannot be..." and the window isn't allowed to maximize, so I can't tell you the rest.
    – Brent Ozar
    Commented May 12, 2011 at 20:43
  • For some reason when importing the January 2011 dump I can only use the gaming, mathematics, photography, programmming and webmaster data - rather than being given a choice between meta, SF, SO and SU like in your screenshot. What am I doing wrong here?
    – daniel_ev
    Commented Apr 16, 2012 at 16:59
  • 1
    MYSQL and SQL Questions and Solutions allinonescript.com/search/label/MYSQL
    – Vadivel S
    Commented Apr 7, 2017 at 4:16

7 Answers 7

Reset to default
9

My experience with the October and November 2010 data dumps importing to MSSQL.

The latest data dump requires that you do a couple of things in order to use this.

  1. Rename the folders.
    • The format of the folder needs to be MMYYYY Schema.
    • So, for the Web Apps data dump, I have 2 folders, 112010 WA and 112010 MWA.
    • The database tables will be <database>.WA.Users and <database>.MWA.Users etc.
  2. Expand the About Me column.
    • The About Me column within the built project is 2100 characters. This is too short.
    • I didn't play around with what the length needs to be - I just went with NVARCHAR(MAX).

If you don't care about the Users table, step 2 isn't necessary. Chances are you'll want the users table though I suppose.

Improve this answer
2
  • thanks for the heads up. i have not run an import in several months. i will try to make time to fix this next week
    – Sky Sanders
    Commented Nov 11, 2010 at 11:49
  • 1
    @Sky Sanders: Does SODDI support the new data dump format? I used it to import the November data dump and the posthistory table wasn't created.
    – ZainabZF
    Commented Jan 18, 2011 at 21:06
7

If there is enough interest, I will do the work to get this to compile and run on Mono for the LAMP folks.

update:

I see a little interest.

I am currently focusing my free time, free as in beer as I am currently without job (hint, big freakin hint), on polishing http://soapi.info and it's offerings.

A bit more interest and I will carve out a day to make this happen.

Improve this answer
3
  • If they use Mono, wouldn't it be LAMM?
    – Aarobot
    Commented Apr 5, 2010 at 23:54
  • @Aaronaught - the google says there is no LAMM stack. ;-O
    – Sky Sanders
    Commented Apr 9, 2010 at 15:20
  • 7
    I heard Mary had a little LAMM. Maybe you should collaborate.
    – Adam Davis
    Commented Apr 9, 2010 at 19:30
5

I have the XML files (for example, users.xml and votes.xml) for Stack Overflow data in a folder called 'c:\sodump'. I fire the command below (I am using Windows 7), but it does not import any records. I would appreciate any help or pointers to possible solutions. I would like to import Stack Overflow data into MySQL.

C:\Program Files\Salient Solutions\SODDI v.10>soddi source:"c:\sodump" target:"server=localhost;user id=root password=blah;Provider=MySql.Data.MySqlClient" so:SoDb

StackOverflow Data Dump Import v.10

Source  : c:\sodump
Target  : server=localhost;user id=root;password=blah;Provider=MySql.Data.MySql
Client
Options : None
Import complete. Approximately 0 rows in 0.00 minutes.
Improve this answer
2
3

There is an issue with a case sensitivity of table names within SQL generated for MySQL. Table creation statements use lower case names of tables and insert statements use mixed case names so the importer cannot work with a MySQL database on Linux. Is there a way to make it work with MySQL on a Unix platform?

Improve this answer
3

can anyone please explain HOW the source directories should be arranged, I have the stack-overflow xml dump (users.xml , posts.xml , votes.xml) inside a directory , I select it as the source directory , but can't get them imported , it tells me "Invalid source path." and also the sites list is empty!

Improve this answer
1
  • 1
    Directory format should be "MMYYYY Schema", for instance "082012 SO". this folder should contains all the xml files. You should pass to SODDI tool the folder containing directories in form "MMYYYY Schema". However, I got an error importing Posts.xml, in the Aug-2012 data dump, "Input string was not in a correct format".
    – Lester
    Commented Nov 30, 2012 at 23:17
3

A very useful tool and very well programmed. However, it doesn't work with the latest Aug-2012 data dump. Probably XML schema of dumped data changed from the previous version, and now for questions and answers without views, the value for the attribute ViewCount is an empty string. That cause the error mentioned by others in this post "Input string was not in a correct format" when the empty string is trying to be converted into a number (Int32).

The solution (that worked for me) was to make a simple modification to the source code to handle this case.

File SoBase.cs, line 83 show this:

if (attr == null)

It should be replaced by

if (attr == null || (string.IsNullOrWhiteSpace(attr.Value) && returnType.IsValueType))

Thanks Sky Sanders for this awesome tool, performance is very good too!

Improve this answer
2
  • Excellent comment. An alternative to modifying the sourcecode and compiling is to modify the xml data source instead. You could use a tool like Notepad++ to search (Search in Files) the parent directory for occurrences of ViewCount="" and replace with ViewCount="0". Using Search in Files allows the replacement to be made without opening the files in the editor, avoiding a memory overrun.
    – user2324003
    Commented May 29, 2013 at 22:05
  • I tried to do import for Photo stack-exchange on 2011 data dump. Tables for Users, Posts, Badges, Comments and Votes were created successfully. However, as there is no xml file for PostLink in the dump (instead there is PostHistory.xml), so for that table I received an error. How could I modify SODDI to run successfully on other stack exchange sites besides Stack overflow for my 2011 data dump. I am using SODDI version 1.0.
    – user2256866
    Commented Apr 7, 2014 at 21:07
2

Additionally, if you need to import a data-dump to PostGreSQL (or MS-SQL), this will help:

https://github.com/ststeiger/StackExchangeDataDumpImporter

It's very efficient with memory consumption, it uses the original schema, it checks the data (serialization to type - exception if invalid), and it works on Linux/Mono, too.

You could also add Firebird easily (just create the schema and change the DataProviderFactory initialization).

Improve this answer

You must log in to answer this question.

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