7

I want to export the list of tags as a plain text file. It's fine if I have to massage the data a bit, as long as I don't need to click through pages and pages of tags. Why? Because I use that input to create interesting maps to help develop tag guidance on smaller sites.

Is there a fairly straightforward way to do this? Or a less straightforward way with SEDE? Or a relatively un-straightforward way with the API?

2
5

Not exactly as plain text but if I assume a CSV format will work for you then you can leverage The Stack Exchange Data Explorer.

There is a small problem. On Stack Overflow there are over 50,000 tags at this moment. SEDE limit the number of rows in a single resultset to 50,000 so you either have to do two passes or use a little trickery to get more then 50,000 rows in one CSV. I have chosen to do the trickery part here.

This SEDE query uses a common table expression to get all tags and then uses a union which allows to split the resultset in two columnsets. The first column set for the rows 1 - 50,000, the second columnset for rows 50,001 and higher.

declare @cutoff int = 50000  -- this is the max 
;with alltags as
(
select row_number() over (order by id) [rownum]
     ,  *
from tags

)

select max(id) id
     , max(tagname) tagname
     , max(count) count
     , max(excerptpostid) excerptpostid
     , max(wikipostid) wikipostid
     , max(id2) id
     , max(tagname2) tagname
     , max(count2) count
     , max(excerptpostid2) excerptpostid
     , max(wikipostid2) wikipostid
from (
select
       rownum
     , id
     , tagname
     , count
     , excerptpostid
     , wikipostid
     , null id2
     , null tagname2
     , null count2
     , null excerptpostid2
     , null wikipostid2
from alltags firstSet
where rownum < @cutoff
union 
select 
       (rownum - @cutoff + 1) 
     , null 
     , null 
     , null 
     , null 
     , null 
     , id
     , tagname
     , count
     , excerptpostid
     , wikipostid
from alltags SecondSet
where rownum between  (@cutoff) and (@cutoff * 2)
-- add extra  unions here and double the number of fields
) alldata
group by rownum
order by [rownum]

Once you've run this query you can download the results by clicking the Download CSV link found near the top right of the result grid:

result grid showing link named Download CSV at the top right

An alternative is using the relatively new SQL instruction FOR JSON AUTO. This will give you a one column result set where each row is part of a complete JSON. Once you have collected these rows you need to stitch them all together and then parse that JSON text. Benefit of these approach is that you can output much more rows in one go. For example the Tags table now only requires 2,410 rows.

Here is that query in its full glory:

select *
from tags 
for JSON AUTO

So with some post-processing you have all tags for one site.

Keep in mind SEDE is updated once a week on Sunday.
Use the awesome SEDE Tutorial written by the magnificent Monica Cellio.
Say "Hi" in SEDE chat.

You must log in to answer this question.

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