XF 1.5 Is it possible to export posts data to CSV, Excel or some other format?

[sajal]

Is it possible to export posts data to CSV, Excel or some other format?

We are going to shut down our forum. And we want to preserve the posts data for future reference. Yes, I can db dump, and take it for reference, but it would be great if there's a way to export data in more "readable" way like CSV, Excel or JSON format.

Is there any way to plugin to do it easily or building the custom solution is the only option?

 

[ENF]

Is it possible to export posts data to CSV, Excel or some other format?

We are going to shut down our forum. And we want to preserve the posts data for future reference. Yes, I can db dump, and take it for reference, but it would be great if there's a way to export data in more "readable" way like CSV, Excel or JSON format.

Is there any way to plugin to do it easily or building the custom solution is the only option?

You can simply run a query against the post table (xf_post) and dump it into a .csv file. Just keep in mind that you'll get the raw posts with unparsed BBCODE, etc.

You'd probably just want username, message, post_date from the xf_post table. Importing this into excel, you can convert the post_date column into a more human format from the default unix date code. (Excel cell limit is something like 32k characters, so overly large posts would get truncated I believe.)

xf_post table contents:

+-------------------+---------------------------------------+------+-----+---------+----------------+
| Field             | Type                                  | Null | Key | Default | Extra          |
+-------------------+---------------------------------------+------+-----+---------+----------------+
| post_id           | int(10) unsigned                      | NO   | PRI | NULL    | auto_increment |
| thread_id         | int(10) unsigned                      | NO   | MUL | NULL    |                |
| user_id           | int(10) unsigned                      | NO   | MUL | NULL    |                |
| username          | varchar(50)                           | NO   |     | NULL    |                |
| post_date         | int(10) unsigned                      | NO   | MUL | NULL    |                |
| message           | mediumtext                            | NO   |     | NULL    |                |
| ip_id             | int(10) unsigned                      | NO   |     | 0       |                |
| message_state     | enum('visible','moderated','deleted') | NO   |     | visible |                |
| attach_count      | smallint(5) unsigned                  | NO   |     | 0       |                |
| position          | int(10) unsigned                      | NO   |     | NULL    |                |
| type_data         | mediumblob                            | NO   |     | NULL    |                |
| reaction_score    | int(11)                               | NO   |     | 0       |                |
| reactions         | blob                                  | YES  |     | NULL    |                |
| reaction_users    | blob                                  | NO   |     | NULL    |                |
| vote_score        | int(11)                               | NO   |     | NULL    |                |
| vote_count        | int(10) unsigned                      | NO   |     | 0       |                |
| warning_id        | int(10) unsigned                      | NO   |     | 0       |                |
| warning_message   | varchar(255)                          | NO   |     |         |                |
| last_edit_date    | int(10) unsigned                      | NO   |     | 0       |                |
| last_edit_user_id | int(10) unsigned                      | NO   |     | 0       |                |
| edit_count        | int(10) unsigned                      | NO   |     | 0       |                |
| embed_metadata    | blob                                  | YES  |     | NULL    |                |
+-------------------+---------------------------------------+------+-----+---------+----------------+

Example query: This dumps your entire xf_post table with username, date of post & the post itself with delimiters to help parse the file into Excel or some other application.

select username, post_date, message from xf_post INTO OUTFILE '/path/to/yourfilenamegoeshere.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';';

 

[sajal]

@ENF Thanks a lot for the solution!