Members Real Name from Memberlist to Excel

Started by Stampie, December 12, 2013, 01:41:01 AM

Previous topic - Next topic

Stampie

hi, i have a new board and it already contains 600+ members. Now i'm using Custom Profiles field for Real Name of the members..

my question is:

how to export them to microsoft Excel? so it will look like:

Username | Real Name | DOB | etc...

in my forum it is mandatory to fill in real names for security reasons and for making it more friendly to one another members, but my moderators have difficulties to sort them one by one by clicking their profiles and copy paste them to excel and to some topics called "Real Name Index"

i hope you guys understands my points, mind my broken english 

Thanks

Wisena

Oldiesmann

What specific info do you want - just real name / username / birthdate, or did you want more?

The only way to really do this is to run a query in phpMyAdmin then export the results of that query to CSV from there (a built-in feature in phpMyAdmin).
Michael Eshom
Christian Metal Fans

Stampie

#2
Quote from: Oldiesmann on December 12, 2013, 02:11:16 AM
What specific info do you want - just real name / username / birthdate, or did you want more?

The only way to really do this is to run a query in phpMyAdmin then export the results of that query to CSV from there (a built-in feature in phpMyAdmin).

thanks for the quick respons Oldies, all i want is:

Username, Real Name and Birthday only...

how do i  run phpMyadmin, and where exactly it is? cpanel?

i found it in cpanel, but i don't know how to use it, can you give me a simple tutor? thanks, sorry for the trouble

margarett

Go to phpmyadmin, select your SMF database, then select your smf_members table (click "Browse").
Click tab "Export", choose "CSV for MS Excel" and click "Go".

That's it, you'll have an excel file with all the contents of your members table, just delete the columns you're not interested in.
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

Kindred

except for the fact tha custom profile fields are not stored in the members table... they are stored in the themes table.
Слaва
Украинi

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

margarett

Ups :P

Forgot that small detail :P So it needs a query with a JOIN and then he needs to export the results.
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

Stampie

Quote from: margarett on December 12, 2013, 06:31:06 AM
Go to phpmyadmin, select your SMF database, then select your smf_members table (click "Browse").
Click tab "Export", choose "CSV for MS Excel" and click "Go".

That's it, you'll have an excel file with all the contents of your members table, just delete the columns you're not interested in.

thanks for the answer, but this is all i got when i did what you've told me to do:



hahahaha i'm sorry for being stupid...

Stampie

Quote from: margarett on December 12, 2013, 07:51:55 AM
Ups :P

Forgot that small detail :P So it needs a query with a JOIN and then he needs to export the results.

hahahah now i need to JOIN-ed them up...sounds like a hard-to-solve calculus problem for me hahhahaha

margarett

Actually it's quite simple, but there are some variables you need to take care about.

I created a custom field called "Real Name". That results, in the table "smf_tables", in a field called "cust_realna".
Also, my database is named "smf2" and my table prefix is "smf_"

So, you go to phpmyadmin, tab "SQL" and paste this:

SELECT
m.member_name,
mt.value AS real_name,
m.birthdate
FROM smf2.smf_members AS m
JOIN smf2.smf_themes AS mt ON ((mt.id_member = m.id_member) AND (mt.variable = 'cust_realna'))
ORDER BY m.id_member

Adjust your database name, table prefix and the name of your custom field in "smf_tables" --> 'cust_realna' check what's yours

That will produce an output that you can export to, again, a CSV file. You can also add more fields to export but take care of the correct syntax.
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

Stampie

Quote from: margarett on December 12, 2013, 08:09:39 AM
Actually it's quite simple, but there are some variables you need to take care about.

I created a custom field called "Real Name". That results, in the table "smf_tables", in a field called "cust_realna".
Also, my database is named "smf2" and my table prefix is "smf_"

So, you go to phpmyadmin, tab "SQL" and paste this:

SELECT
m.member_name,
mt.value AS real_name,
m.birthdate
FROM smf2.smf_members AS m
JOIN smf2.smf_themes AS mt ON ((mt.id_member = m.id_member) AND (mt.variable = 'cust_realna'))
ORDER BY m.id_member

Adjust your database name, table prefix and the name of your custom field in "smf_tables" --> 'cust_realna' check what's yours

That will produce an output that you can export to, again, a CSV file. You can also add more fields to export but take care of the correct syntax.

if, just if i did a mistake when i'm doing this, will it affect my site?

margarett

Nop. Those are just SELECT queries, they just retrieve data.
Or they retrieve the data or they don't, that's just it ;)
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

Kindred

no...  the statement is justa  "select" (which means, in SQL terms, "display this for me.")

use your own database name in place of smf2
use your own prefix in place of smf_
and check what the actual field name was called in the database something like cust_realna   (where the letters realna migth be something else, depending on what you actually called your field) 
Слaва
Украинi

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

Stampie

my database name is: k7473524_MU001
my prefix is the default: smf_
my cust field name is: cust_namabe

so the code has to be look like this?


SELECT
m.member_name,
mt.value AS real_name,
m.birthdate
FROM k7473524_MU001.smf_members AS m
JOIN k7473524_MU001.smf_themes AS mt ON ((mt.id_member = m.id_member) AND (mt.variable = 'cust_namabe'))
ORDER BY m.id_member

margarett

Yes, if your custom field name is "cust_namabe" ;)
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

Stampie

#14
and here what i got:



although it displayed the username, real name, DOB at the bottom

is that okay?

Stampie

WOW !!!!!!!

it works !!!!!

instead of exporting to CSV i tried to MS words 2000

and it shows exactly  the way that i want it to be !!!

THANKS THANKS THANKS THANKS Margarett !!! Kindred !!!

you guys rock !!!!

margarett

Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

Stampie

Quote from: margarett on December 12, 2013, 10:25:03 AM
;) Glad it worked out for you.

just out of curiousity, is the "token mismatch" thing is normal?

margarett

It isn't and I have no idea why it happens :P
But that's something related to phpmyadmin. I just googled "phpmyadmin token mismatch" and there are a ton of results, so you are not the only one :P
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

Arantor

* Arantor Beeblebrox the First really needs to make a plugin for exporting member data sometime.

Stampie

Quote from: margarett on December 12, 2013, 11:00:50 AM
It isn't and I have no idea why it happens :P
But that's something related to phpmyadmin. I just googled "phpmyadmin token mismatch" and there are a ton of results, so you are not the only one :P

i tried that again today and the "token mismatch" is gone, i thought you should know this... weird :D

Advertisement: