User posts export as csv or text

Community Building Data & reporting

AI-generated summary

User posts export as csv or text

A discussion on exporting a user’s public posts as a CSV or text file is ongoing. kynic initially asked for help with this task using the Data Explorer. They found a query that works but is limited to 336 results, despite the user having around 5,000 posts kynic.

Stephen suggested that users can request a full copy of their data, including posts, in CSV format via the Export Your Data section of Preferences. However, kynic pointed out that this includes private messages and they only want public posts.

Canapin speculated that the 5,000 posts may include private messages and deleted posts, which could be filtered out. JammyDodger suggested a revised query that should solve the issue, including only public posts and filtering out deleted posts.

1

Hi all,

I want to export a user’s posts as a text/CSV file do anyone know how I can achieve this with data explorer or any other way?

2

I found this code works for data explorer

SELECT t.title, p.raw as text, p.created_at as dateposted
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
WHERE t.archetype != 'private_message'
AND t.user_id = 1
AND p.user_id = 1
AND t.category_id IN (13,7,1,)
AND t.deleted_at is null

But it only shows 336 results is there any way to pass this limit? as there are around 5k posts.

3

A user can request a full copy of their posts and other data in CSV format via the Export Your Data section of Preferences.

For example here on meta it’s at:

https://meta.discourse.org/my/preferences/account

System will message them with a link to a zip file. The user_archive.csv contains the post data.

4

But this includes PM also and I want only public posts.

5

Isn’t that because the 5000 posts include PMs (maybe deleted messages as well) and you filter your categories?

6

Including t.user_id would only give posts where the user was also the OP. Could that be the main issue?

I noticed a couple of extra bits that may be useful too. Something like:

-- [params]
-- user_id :user

SELECT t.id AS topic_id, 
       t.title, 
       p.raw as text, 
       p.created_at as dateposted
FROM posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.archetype != 'private_message'
  AND p.post_type IN (1, 4)
  AND p.user_id = :user
  AND t.deleted_at ISNULL
  AND p.deleted_at ISNULL
ORDER BY p.created_at