6

I am writing several papers in which I use answers which I received in various StackExchange websites. Naturally I would like to acknowlege and thank the SE members that gave the useful answers. But there are many such answers, and each answer is relevant to a different paper.

So, I would like to download all the questions that I asked in the various SE websites, including the answerer names, to an Excel worksheet in which I can organize the questions by subject.

Is this possible?

  • Well, using SEDE it's possible to easily export the results to Excel, and cross-site query there is possible, as far as I know. The data is not live though. – Shadow The Princess Wizard Jul 28 '15 at 6:30
8

You already got a per site query from Patrick and that leaves the cross-site-query for me.

This query outputs all your questions network-wide with the answers given, listing score, creationdate and the user (linked).

The query requires an accountid and that one can be found by visiting your network account on StackExchange.com and then take the id from the url. Yours is 439169, mine is 281857.

---- 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
                            , bodyexcerpt nvarchar(100)
                            , owneruserid int
                            , displayname nvarchar(200));

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
               , left(p.body,100)
               , p.owneruserid
               , pu.displayname
               from posts p 
               inner join posts q on p.parentid = q.id
               inner join users pu on pu.id = p.owneruserid 
               inner join users qu on qu.id = q.owneruserid
               where qu.accountid = '+ cast(@accountid as nvarchar) + ';'
    exec (@sql)
   fetch next from db_c into @db_c_name
end;
close db_c;
deallocate db_c;

select replace(
       replace(
       replace(site,
         '.StackExchange.com',''),
         '.com',''),
         '.net','') as [site]
     , 'http://' 
     + site 
     + '/q/'
     + cast(id as nvarchar)
     + '|'
     + title as question
     , 'http://' 
     + site 
     + '/a/'
     + cast(aid as nvarchar)
     + '|answer' as AnswerLink
     , score
     , creationdate
     --, bodyexcerpt
     , 'http://' 
     + site 
     + '/users/'
     + cast(owneruserid as nvarchar)
     + '|'
     + displayname as [user]
from #all_posts

drop table #all_posts

drop procedure #recursesiteurl
drop procedure #siteurl

I start with creating a procedure (functions are not allowed) to translate a databasename to a site url. The db's are named StackExchange.Photography.meta and the url for that site is meta.photography.stackexchange.com

A temporary table is going to hold all our data. Then I open a cursor to iterate over all databases (except the system db's). In that loop I create a sql statement to switch to the correct database, create an insert/select to insert your posts with the answers in the temp table. When that is done the sql statement is executed.

After the loop is done the final select statement produces the result where I buildup the final url to the answers, question and user. The links work because SEDE interprets column values that contain http://site/url/tosomething|title as link and the front end renders a nice <a href="http://site/url/tosomething">title</a> for us.

  • FYI, when you're converting DB names to URLs, you need to special-case StackExchange.MathOverflow.Meta to meta.mathoverflow.net (cf. meta.stackexchange.com/q/215071) and StackExchange.Audio to video.stackexchange.net (and likewise for its meta; this is because of the weird merge/split there where AVP split half into Sound and half into Video). – senshin Jul 28 '15 at 20:27
  • MathOverflow main gets redirected, but its meta doesn't. Dunno why. – senshin Jul 28 '15 at 20:31
  • 1
    @senshin I have the result here. 3 sites need compensating. – rene Jul 28 '15 at 22:03
  • @senshin I have updated the procedure to compensate for the 3 sites with borked dns entries. – rene Jul 29 '15 at 9:44
  • Thank you very much! I even managed to insert my topics into the table. – Erel Segal-Halevi Aug 3 '15 at 16:19
8

Yes, you can using SEDE.

Here is the query you want:

select q.Id [Question Id]
,      q.Id [Post Link]
,      a.Id [Answer Id]
,      a.Id [Post Link]
,      a.OwnerUserId
,      a.OwnerUserId [User Link]
from   posts q
join   posts a
on     q.Id = a.ParentId
join   users u
on     a.OwnerUserId = u.Id
where  q.PostTypeId = 1
and    q.OwnerUserId = ##userid##

You can download the results as CSV, which you can import into Excel. I am not sure how to make this query cross-site, maybe someone else can enlighten me on that, but you can run it one-by-one for your sites.

  • 3
    summons @rene – Shadow The Princess Wizard Jul 28 '15 at 8:02
  • @rene Thanks. I think that is a little to much on my time frame too. Will check later if I have some time. – Patrick Hofman Jul 28 '15 at 8:08
  • @rene. No, had a busy day. Good answer. +1 – Patrick Hofman Jul 28 '15 at 20:21

You must log in to answer this question.

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