35

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.

  • 1
    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
  • 1
    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
26
+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 network profile id which is 1522906 (the number in the url).

---- 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:

html sample of generated output

If you rather have the result of the query in the CSV downloadable format you can uncomment this part near the end of the SEDE query:

/*
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
*/

You'll have to provide your own parsing in that case.

  • @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
  • Thanks again for your answer. Here is what I currently use: data.stackexchange.com/stackoverflow/revision/398537/510913/… – Basj Nov 5 '17 at 22:50
  • 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 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 at 17:26
10

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
  • 1
    Thanks for this solution! But it seems that it only shows 30% of my content. Here is what I finally use: data.stackexchange.com/stackoverflow/revision/398537/510913/… – Basj Nov 5 '17 at 22:56
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 .