User posts export as csv or text

data & reporting
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
1 Like