64

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.

  • 2
    Short answer, no. Long answer, someone might have written a script to do it. – Kevin Nov 22 '15 at 19:20
  • This would definitely be possible as a script, but might be unrealistic, for most users, because they have loads of accounts with quite a few questions+answers, so using the API would make too many requests, and you'd almost definitely be throttled... – ᔕᖺᘎᕊ Nov 22 '15 at 19:32
  • @Basj I imagine the API would be the easiest and most acceptable way. But you'd probably want to rate-limit yourself so as not to trip the server-side throttling ᔕᖺᘎᕊ mentioned. – Kevin Nov 22 '15 at 19:58
  • @Basj api.stackexchange.com/docs/throttle – ᔕᖺᘎᕊ Nov 22 '15 at 20:07
  • Well... what's easy? ;) you'd have to use the associated method to get all your accounts, then use the questions, answers, comments routes and parse the data and save it as a html file – ᔕᖺᘎᕊ Nov 22 '15 at 20:19
  • You might find something on stackapps.com. that's where people post scripts they make – ᔕᖺᘎᕊ Nov 22 '15 at 20:28
  • 1
    @Basj are you asking for a list of URLs or separate HTML files (as you said in the question)...? – ᔕᖺᘎᕊ Nov 24 '15 at 19:51
  • 2
  • 2
    I posted an answer here of my free open-source code that lets you download as JSON files all of your content from across all Stack Exchange sites: meta.stackexchange.com/a/315243/189207 – Ryan Sep 9 '18 at 12:15
44
+100

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

  • @Basj you can almost do that by changing the sql query that is build up in the 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. – rene Jan 31 '16 at 19:20
  • @Basj I'm a bit short on time today, I'll give that a go tomorrow, ping me on tuesday if you didn't hear back from me. – rene Jan 31 '16 at 20:05
  • @Basj I couldn't get it done with a join so I used some unions instead: data.stackexchange.com/stackoverflow/query/… – rene Feb 1 '16 at 19:21
  • I created a fork of your SEDE query that added two things: 1. a quick hot fix that casts the post body as ntext to increase the truncation threshold (see: stackoverflow.com/a/10444950). 2. a meta tag to specify utf-8 as the default encoding. I also did some formatting and ordering of the posts by sites. – Gao Nov 14 '17 at 21:59
  • Awesome! works great! +1. However, is there some way to sort this by either date of post, by site, by post type (Q or A), etc...??? – theforestecologist Nov 17 '18 at 17:26
  • 1
    @theforestecologist yes on the last select statement adding 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. – rene Nov 17 '18 at 17:32
  • thanks @rene! So I would add one of your indicated statements (e.g., 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)? – theforestecologist Nov 17 '18 at 17:38
  • 1
    yes, @theforestecologist you can do order by creationdate desc, site asc you might like the sede tutorial – rene Nov 17 '18 at 17:55
  • rene, do you plain to improve your query? For example, my dump have 932 Kb size (I'm not very active user) → it slowly opened in text editors. Maybe to split all big dump file by several sites? For example: for Stack Overflow 1 dump file, for SuperUser — another dump file. Thanks. – Саша Черных Jan 21 '19 at 17:22
  • @СашаЧерных I don't think I will. I suggest you use the CSV output instead and split that file your self in several files – rene Jan 21 '19 at 17:26
  • @rene: Amazing! 1) the query code is shorter, 2) it gives both questions and answers I've made (that it seems this was already true in past versions, my bad), 3) it gives both the HTML and the Markdown! Thanks so much! – Basj Sep 26 '19 at 7:45
  • This is great. Thank you so much. Do you know of a way to get the questions one has "favorited" by clicking the star? – Bryan Hanson Jan 17 at 1:40
  • @BryanHanson yes, that is stored in the votes table, votetypeid = 5 and userid = [your userid on the site], join votes.postid with posts.id – rene Jan 17 at 8:34
  • @rene It looks like the id that is shown in the answer text is dynamically generated for the reader - but is it? It's not obvious from the source text. Is 1522906, shown in the text as my network profile ID. Is 1522906 the personal network profile ID of the user "Volker Siegel" (me, who is writing a comment)? – Volker Siegel Jan 18 at 7:22
  • @VolkerSiegel no, you have to find that on your own and adapt accordingly. Your network account id is 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). – rene Jan 18 at 9:19
12

I've made a small script to get all your questions and answers, and list their URLs.

Usage

  1. Go to http://shu8.github.io/SE-PostUrlDump/
  2. Get an access token (info is given on above URL)
  3. Get your network ID: Go to http://stackexchange.com/users/current, and get your ID from the URL (for you it's 1522906)

enter image description here

  • 1
    @Basj whoops! I forgot to account for multiple pages! Shouldn't be hard to change, but I'm away till friday now... I'll fix it on Saturday and ping you when its done! Sorry :/ BTW the error should be fixed now (small typo!). It might take a few minutes to take effect! – ᔕᖺᘎᕊ Nov 24 '15 at 21:09
  • Thanks for this solution! But it seems that it only shows 30% of my content, maybe it doesn't take all SE sites in consideration? – Basj Sep 25 '19 at 18:42
  • Ah, I had completely forgotten to do that multiple pages fix a few years ago! I see you've found a way now but I'll try getting that done this weekend anyway! – ᔕᖺᘎᕊ Sep 25 '19 at 19:39
  • 1
    Yes @ᔕᖺᘎᕊ I have found a solution thanks to rene's query, but I wanted to report this little issue to you in case you want to still maintain your (useful!) tool! By the way, do you think you can publish the query that you're using on your linked website? The github is empty (only LICENSE). Thanks again! – Basj Sep 26 '19 at 7:40
  • The tool I wrote uses the API rather than SEDE, the code is actually on the gh-pages branch, so you should be able to see it here :) Thanks again for letting me know! I'll try improving the repo itself and the readme over the weekend too – ᔕᖺᘎᕊ Sep 26 '19 at 10:41
  • 1
    @Basj you probably don't need this anymore but the bug should be fixed now :) I've cleaned up the repo a bit more too – ᔕᖺᘎᕊ Oct 4 '19 at 14:43
12

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.

3

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'])
  • So this way one can download all of their contributed questions and answers, right? – 202324 Sep 25 '19 at 19:21
  • Yes I think so @john, I still need to test it a little bit more. – Basj Sep 25 '19 at 19:22
  • Awesome. Would you release the code? – 202324 Sep 25 '19 at 19:24
  • @john You mean the query? It's in the link. About the page downloader, any tool for which we can give a list of URLs will do it, like HTTrack I think. – Basj Sep 25 '19 at 19:27
0

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.

You must log in to answer this question.

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