All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
[Another Excel Question] Extracting certain data from Excel table
Hello guys,
I have a wordpress user meta file in Excel format:
It begins like this(each new line is another row in the table; other users start with other nickname, first_name etc.):
1,"1","nickname","Site Admin"
2,"1","first_name","Site"
3,"1","last_name","Admin"
4,"1","description","web design is alright.."
5,"1","rich_editing","true"
6,"1","syntax_highlighting","true"
7,"1","comment_shortcuts","false"
8,"1","admin_color","fresh"
I have another file which looks like this:
first_name,last_name
Oluwatobiloba,Dairo
Sidikat,Taiwo
Stanley,Evans
First_name and last_name are two columns, next to each other with the data in each column.
What I want is to extract a few variables from the first file: Ex. (Firstname, Lastname, Company Name, Company Location, Position In Company, Phone Number, Mobile Number) - each has its own repeated definition, like first_name and last_name and then put the data into columns in the other file - 1 column for each variable.
What's the best way to approach this in an automated manner? There are too many users to do manual data entry
Comments
Did you have a plan in mind when you exported it and imported it into Excel?
I ask because the data is already in your WP database. Why not query/transform/extract there as SQL queries?
Import file 1 into excel spreadsheet. highlight the column. Click on data tab. then select text to columns on toolbar. rename columns as desired.
are you asking this in a "programmer" sense? have you looked at libraries for handling csv files?
or making a parser? just iterate each line, split by comma, iterate that (and insert it into an array)
iterate the whole array X (the amount of rows you want to use per column) times, each time inserting the level/index of each array within the array
or wtf do you mean by this?
So everything in the first file is the same user? I guess I'd probably just use R to reshape that table. Not sure why you even need the second table, since the same data is in the first one.
No, there are multiple users with same variables.
For example, go through whole file searching for first_name, then insert data that it finds in column "First Name" of second file. Each time it finds new data for the variable, it should put it on a new row in the second file. This would be done for all variables.
So, how do you know which variables belong to which users in table 1? Are there always the same number of rows per user, grouped together? In that case it should be pretty straightforward: read the row, extract the value, write as the next column in the row for that user, etc.
In any case, since the names are in table 1, you don't need table 2.
I'm guessing the "1" in each line is the record ID.
TBH, I wouldn't do this in Excel. I'd export it as CSV, import it into a database and just run my select blah join blah query.
No, I wouldn't do it in Excel, either. Either I'd use R, or I'd just write an ad hoc program to do it.
Hell, you could do it with one monster regex in vi, if all the records for a user are grouped.
What do the "1"'s represent? Is that the user ID?
I'd suggest you create two google sheets with sample data and send us the links here so we can actually see your data as it's not clear what you want to achieve.
most likely FILTER can do what you want on the first table. at least as a first step extract certain thing into a temp table which you can use further...
https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759
PS: on the other hand, simply splitting the first file into multiple by using grep on the console will do the trick as well...
You're not answering the question wth?
But HOW WOULD YOU WANT THIS TO HAPPEN
Are you taking if a program exists that does that with a GUI?
Are you asking if excel has a tool for this?
You want to make a program that does this?
what
i messed with people like this before, i wonder if you're doing the same