Is there a way to quickly save all my Stack Exchange content across all Stack Exchange sites, as hundreds of HTML pages? (for me probably 15 or 20 sites: SO, dsp.SE, unix.SE, etc.)?
The ideal way would be one .HTML file for each question that I asked / answered / commented? What would be the ideal tool for that?
Remark: I've already read interesting questions like this one but it doesn't exactly answer my concern here.
To get all the posts you've posted until last weekend (when SEDE gets updated) you can use the following query
This query asks you to provide your own network profile id which is 1522906 (the number in the url), my network profile id is 281857 and that of Shog9 is 620. You can reach the network profile of a user by visiting their user profile on a site and then click the Network Profile link near the top right of the page.
---- accountid: Account on stackexchange.com! "In the url on stackexchange.com"
-- start create url from dbname
IF OBJECT_ID ( '#siteurl', 'P' ) IS NOT NULL
DROP PROCEDURE #siteurl;
GO
create procedure #siteurl
@dbname nvarchar(250),
@res nvarchar(250) OUT
as
begin
-- we have three sites that have borked DNS entries
set @dbname = (case @dbname
when 'StackExchange.Audio' then 'StackExchange.Avp'
when 'StackExchange.Audio.Meta' then 'StackExchange.Avp.Meta'
when 'StackExchange.Mathoverflow.Meta' then 'net.Mathoverflow.meta'
else @dbname
end)
-- and one of those doesn't want to end with .com
if @dbname <> 'net.Mathoverflow.meta'
set @dbname = 'com.' + @dbname
exec #recursesiteurl @dbname, @res OUTPUT
end
GO
-- create url from dbname
IF OBJECT_ID ( '#recursesiteurl', 'P' ) IS NOT NULL
DROP PROCEDURE #recursesiteurl;
GO
create procedure #recursesiteurl
@dbname nvarchar(250),
@res nvarchar(250) OUT
as
begin
declare @pos integer
declare @rec nvarchar(250)
set @res = @dbname
set @pos = CHARINDEX('.', @dbname)
if (@pos > 0 )
begin
set @rec = substring(@dbname, @pos+1, len(@dbname))
exec #recursesiteurl @rec, @rec output
set @res = @rec
+ '.'
+ substring(@dbname, 0, @pos)
end
end;
GO
declare @accountid int = ##accountid:int## -- 93484
-- all databases
declare db_c cursor for select [name]
from sys.databases
where database_id > 5 -- skip master, temp, model, msdb, Data.SE
declare @db_c_name sysname -- holds name of db after fetch
declare @sql nvarchar(max) -- holds build up sql string
-- result table
create table #all_posts ( site nvarchar(250)
, id int
, title nvarchar(200)
, aid int
, creationdate datetime
, score int
, body nvarchar(max)
);
open db_c
fetch next from db_c into @db_c_name
while(@@FETCH_STATUS = 0)
begin
set @sql = N'use '+ QUOTENAME(@db_c_name) +';
declare @url nvarchar(250)
exec #siteurl ''' + @db_c_name + ''', @url output
insert into #all_posts
select @url
, q.id
, q.title
, p.id
, p.creationdate
, p.score
, p.body
from posts p
inner join posts q on q.id = coalesce(p.parentid, p.id)
inner join users pu on pu.id = p.owneruserid
where pu.accountid = '+ cast(@accountid as nvarchar) + ';'
exec (@sql)
fetch next from db_c into @db_c_name
end;
close db_c;
deallocate db_c;
-- process results
declare db_r cursor for select replace(
replace(
replace(site,
'.StackExchange.com',''),
'.com',''),
'.net','') as [site]
, 'http://'
+ site
+ '/q/'
+ cast(id as nvarchar)
-- + '|'
-- + title
as question
, title
, body
from #all_posts
declare @db_r_body nvarchar(max)
declare @db_r_site nvarchar(250)
declare @db_r_title nvarchar(250)
declare @db_r_url nvarchar(250)
print '<html><meta charset="UTF-8"><body>'
open db_r
fetch next from db_r into @db_r_site, @db_r_url, @db_r_title, @db_r_body
while(@@FETCH_STATUS = 0)
begin
print '<!-- start of q -->'
print '<div>'
print '<div>'
print '<div>site:' + @db_r_site +'</div>'
print '<div>url:' + @db_r_url +'</div>'
print '<div>title:' + @db_r_title +'</div>'
print '</div>'
print @db_r_body
print '</div>'
print '<!-- end of q -->'
fetch next from db_r into @db_r_site, @db_r_url, @db_r_title, @db_r_body
end;
close db_r;
deallocate db_r;
print '</body></html>'
-- plain output for CSV
-- remove the comment start and end markers
/*
select replace(
replace(
replace(site,
'.StackExchange.com',''),
'.com',''),
'.net','') as [site]
, 'http://'
+ site
+ '/q/'
+ cast(id as nvarchar)
-- + '|'
-- + title
as question
, title
, body
from #all_posts
*/
-- end of CSV output
-- clean up
drop table #all_posts
drop procedure #recursesiteurl
drop procedure #siteurl
The query creates one big html formatted text block, separating each post in a div and a distinct comment markup. You'll need to copy the result to your favorite text-editor and save it as html.
The result will look like this screenshot
If you rather have the result of the query in the CSV downloadable format you can use this newer query that also includes the post markdown:
declare @sql nvarchar(max) -- holds build up sql string
-- result table
create table #result ( site nvarchar(250)
, host nvarchar(250)
, id int
, title nvarchar(200)
, postid int
, creationdate datetime
, score int
, body nvarchar(max)
, text nvarchar(max)
);
select @sql = N'insert into #result' + STRING_AGG(concat(N'
select ''', name ,N''' as site
, ''',hostname ,N'''
, q.id
, q.title
, p.id
, p.creationdate
, p.score
, p.body
, ph.text
from ', quotename(name), N'.dbo.posts p
inner join ', quotename(name), N'.dbo.posthistory ph on ph.postid = p.id
inner join ', quotename(name), N'.dbo.posts q on q.id = coalesce(p.parentid, p.id)
inner join ', quotename(name), N'.dbo.users pu on pu.id = p.owneruserid
where pu.accountid = ##accountid?1522906##
and ph.id = (select max(id)
from ', quotename(name), N'.dbo.posthistory
where posthistorytypeid in (2,5)
and postid = p.id)
'),N'
union all')
from (select convert(nvarchar(max),name) name
-- , convert(nvarchar(max),sitename) sitename
-- , meta
-- , domain
, concat(
-- based on an idea from Glorfindel
-- https://meta.stackexchange.com/a/321181
(case sitename
WHEN 'Audio' THEN 'video'
WHEN 'Beer' THEN 'alcohol'
WHEN 'CogSci' THEN 'psychology'
WHEN 'Garage' THEN 'mechanics'
WHEN 'Health' THEN 'medicalsciences'
WHEN 'Moderators' THEN 'communitybuilding'
WHEN 'Photography' THEN 'photo'
WHEN 'Programmers' THEN 'softwareengineering'
WHEN 'Vegetarian' THEN 'vegetarianism'
WHEN 'Writers' THEN 'writing'
WHEN 'Br' THEN 'pt'
WHEN 'Mathoverflow' THEN concat((meta+'.'), sitename)
ELSE case when sitename = domain then null else sitename end
end +'.')
, (case
when sitename <> 'mathoverflow' then (meta+'.')
else null
end)
, (case
when sitename <> 'mathoverflow' then concat((domain + '.'), 'com')
else 'net'
end)
) hostname
from (
select name
, case parsename(name,1)
when 'Meta' then parsename(name,2)
else parsename(name,1)
end [sitename]
, case parsename(name,1)
when 'Meta' then 'meta'
else null
end [meta]
, coalesce(parsename(name,3), parsename(name,2)) [domain]
from sys.databases
where database_id > 5
-- (name not like '%.Meta' or name = 'StackExchange.Meta')
) dbs
) dbsall
exec (@sql)
select site
, concat('https://'
, host
, '/q/'
, postid
-- + '|'
-- + title
) as question
, title
, body
, text
from #result
Keep in mind SEDE is updated each Sunday.
Do try the awesome tutorial written by Monica Cellio
Say "Hi!" in Chat
set @sql= statement because that is the query that is run per database (so you can easily test/adapt that your self by running just that part and integrate it back later). You can get #1 and #2 with and extra outer join on the posts table, #3 with an outer join on the comments table and #4 with an outer join on the votes table for votetypeid = 5. All those table have a foreignkey to the users table. You can't get the data for #5 because voting is anonymous.
order by creationdate will work, as will order by site. Postypeid (1= Q, 2=A) isn't in the #all_posts table so you have to add that column first and then insert postypeid in the insert statement.
order by creationdate) as a new line below from #all_posts? Also, would more than one sorting statement work (and if so, are they just added as separate lines in order of rank of sorting desired)?
Commented
Nov 17, 2018 at 17:38
order by creationdate desc, site asc you might like the sede tutorial
4153260 , as found in the url of your network profile: stackexchange.com/users/4153260/volker-siegel (you can reach the network profile of any user by visiting their user profile and then click the link network profile, found near the top right on the screen).
There's a way that gets you almost all of the way there with an absolute minimum of hassle, and using supported interfaces only.
Use Stack Exchange's GDPR Data Access Request interface.
Start at /legal/gdpr/request on a site where you have an account and are logged in. (It looks like the process should work equally well if you aren't logged in but just have access to the e-mail address associated with your content, but I haven't tried that myself.)
Choose to make an "Export my data" request. Verify that the e-mail address shown is accurate (you'll get a confirmation request sent there), leave the details field empty, and submit the request.
You'll get an e-mail seeking confirmation that the request is legitimate. Click the appropriate link in that email to proceed.
You'll get an e-mail confirming that the request is being processed. Wait.
You'll get a third e-mail when the data dump is ready, which contains a link to a landing page where the data can be downloaded as a ZIP archive of JSON files.
In my case, the whole process from beginning to when I had the ZIP file downloaded took about 15 minutes.
Once you have that, you can look over the JSON files to extract raw post content (there's PostHistory.json and PostComments.json which are likely to be of particular interest). Those files also contain post IDs, which can be inserted into URLs that can then be fed into any web downloader, such as wget or curl, to download rendered copies. For example, if the JSON in your Meta Stack Exchange PostHistory.json (qa/meta.stackexchange.com/PostHistory.json within the archive) says
{"type":"Initial Body","postId":1234567,"revisionGUID":"141d266a-ac6a-4e4a-a72e-b8fe137e37dd","creationDate":"2019-01-02T03:04:05.678Z","ipAddress":"192.0.2.123","text":"elided for brevity"},
then you can make a web request for https://meta.stackexchange.com/a/1234567 which will get you a rendered page where your answer appears in the context of the question.
As an aside, if you're so inclined, there's probably a way to use the Stack Exchange API to get machine-parsable content also for the question and answers other than your own; or for answers to your question.
At a glance, the export looks to be quite complete; the only obvious thing that appears to be missing is content you've posted on sites which have since been shut down.
This should work just as well for people who are not EU citizens or residents and therefore not ordinarily covered by the GDPR specifically. Compare Brace yourselves: The GDPR is coming!, in which Tim Post (a Stack Exchange employee) wrote, emphasis mine:
While this was done to be compliant with the GDPR, we strongly believe in the intent and spirit of the GDPR, and have extended its protection to everyone.
Besides, for an automated system, it just makes things more complicated to try to determine based on geographic location or citizenship whether a user should be allowed to use it or not. It's much easier to just make the automated data export feature available to everyone.
I've made a small script to get all your questions and answers, and list their URLs.
1522906)
I finally used @rene's latest query:
https://data.stackexchange.com/stackoverflow/query/1114488?accountid=YOURUSERIDHERE
(for me 1522906)
Then this Python code extracts each question/answer into HTML files and Markdown files (in /html/ and /markdown/):
import csv, html, os
def sanitize(s):
return "".join([c for c in s if c.isalpha() or c.isdigit() or c in ',;. -']).rstrip()
with open('QueryResults.csv', 'r', encoding='utf8') as f:
reader = csv.DictReader(f)
for row in reader:
title = html.escape(row['title'])
filename = sanitize(row['title'])
if os.path.exists('html/%s.html' % filename):
filename += '1'
with open('html/%s.html' % filename, 'w', encoding='utf8') as g:
with open('markdown/%s.txt' % filename, 'w', encoding='utf8') as h:
g.write('<html><head><style>.posttitle { font-size: 2em; }</style></head><body>')
g.write('<a class="posttitle" href="%s">%s - %s</a>%s</div>\n' % (row['question'], row['site'].replace('StackExchange.',''), title, row['body']))
g.write('</body></html>')
h.write(row['text'])
As an improvement to @Basj's answer, I created a Python script that does it all in one step: it uses the Stack Exchange API and the requests Python package to download all your questions and answers across all sites and then writes them to Markdown files in sub-directories. All it takes is a single execution of a Python script. The Python script, as well as instructions on how to use it, can be found here: https://github.com/mhdadk/stack-exchange-backup
No. There is not. The best you have is, as the other question and answers say, use SEDE or the API to get your data out and then go through all the pages to get the content.
This is feasible since all the data you request is there but it would require quite some work.
If you go through SEDE it is quite easy to get all your questions or answers out, and so the comments. Then putting together a list of urls to get and that for all sites you are active in. Depending on the number of accounts, this may take a few hours to get all data out and then process all the urls.
