CSV Group updates without header
496 views
Skip to first unread message
Joe Wakeman
Sep 20, 2016, 5:33:35 PM9/20/16
to Google Apps Manager
Is it possible to run a CSV update on groups when the CSV file does not contain a header?
For example, I have a nightly dump from our SIS (PowerSchool) of Parent Email addresses, but that process does not include a header. I would like to script GAM to nightly update the appropriate parent groups but I need to be able to do it without a header row....
For example, I have a nightly dump from our SIS (PowerSchool) of Parent Email addresses, but that process does not include a header. I would like to script GAM to nightly update the appropriate parent groups but I need to be able to do it without a header row....
Any tips/ideas/suggestions?
Thanks!
Thanks!
Ross Scroggs
Sep 20, 2016, 5:59:46 PM9/20/16
to google-ap...@googlegroups.com
Joe,
What are the columns in the file? What would the headers be if they were there? Is there one file per group? Inquiring minds want to know.
Ross
--
You received this message because you are subscribed to the Google Groups "Google Apps Manager" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-manager+unsub...@googlegroups.com.
To post to this group, send email to google-apps-manager@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-manager.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/8db53bc2-12c5-432a-881d-6e3c73ab1280%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Ross Scroggs
Joe Wakeman
Sep 20, 2016, 9:24:29 PM9/20/16
to Google Apps Manager
There is one file with all students; however I could create one for each group.
Column 1 is Grad Year. Column 2 is Parent1 email. Column 3 is Parent2 email (if populated).
My other hurdle is that I need to somehow script it to add -parents to the group name (IE 2017 column should actually be 2017-parents). That is where an export for each grade might be beneficial.
Maybe also worth mentioning that I'm doing this on a Windows server, so the usual *NIX tools aren't available.
Jay Lee
Sep 20, 2016, 9:29:36 PM9/20/16
to Google Apps Manager
Should be possible to do something like:
echo "header1,header2,header3" > header.txt
copy header.txt+headerless.csv properly-headed.csv
Jay
--
You received this message because you are subscribed to the Google Groups "Google Apps Manager" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-man...@googlegroups.com.
To post to this group, send email to google-ap...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-manager.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/7dd873b3-185e-4ff6-b4ae-cb7e621c49b0%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
--
Jay
Joe Wakeman
Sep 20, 2016, 9:44:57 PM9/20/16
to google-ap...@googlegroups.com
Great - will give it a try tomorrow!
Any suggestion on the group name change? The column contains grad year (2017) but the group is actually 2017-parents. Not sure how I could format the GAM command to append that to the ~GradYear header I would create.
Ross Scroggs
Sep 21, 2016, 12:40:09 AM9/21/16
to google-ap...@googlegroups.com
Joe,
When using gam csv, when an entire argument is ~Name, ~Name is replaced with values from the column Name.
You can also embed a column name within an argument like this: leadingtext~~Name~~trailingtext where leadingtext and trailingtext can be anything.
Following Jay's suggestion, suppose your data is in powerschool.txt
echo GradYear,P1Email,P2Email > header.txt
copy header.txt+powerschool.txt > powerschool.csv
What you want to say is something like this, although this won't actually work: gam csv powerschool.csv gam update group ~~GradYear~~-parents sync members ?????
The problem is how to get ?????? from the powerschool.csv; with Gam 3.71, you can't because the data is in two columns.
Hmmm. If you had PowerSchool output the data in a separate file for each grade with one column of data, then you don't even need the headers and Gam 3.71 can handle it.
Let's suppose the files are 2017.txt, 2018.txt, ... with one email address per line.
Make a CSV file gradyears.csv
GradYear
2017
2018
...
Now say: gam csv gradyears.csv gam update group ~~GradYear~~-parents sync members file ~~GradYear~~.txt
That works if you can make PowerSchool output the data in one column, if not Gam 3.71 can't help.
What to do? Go here: https://github.com/taers232c/GAM-N/releases
With this version of Gam, currently 3.74.02, you can say:
gam csv gradyears.csv gam update group ~~GradYear~~-parents sync members file ~~GradYear~~.txt delimiter ','
The delimiter ',' says to read each row and get multiple values separated by commas.
Ross
On Sep 20, 2016, at 6:44 PM, Joe Wakeman <jwak...@nevada.k12.ia.us> wrote:
Great - will give it a try tomorrow!Any suggestion on the group name change? The column contains grad year (2017) but the group is actually 2017-parents. Not sure how I could format the GAM command to append that to the ~GradYear header I would create.
--
You received this message because you are subscribed to the Google Groups "Google Apps Manager" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-man...@googlegroups.com.
To post to this group, send email to google-ap...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-manager.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/CAMs1XQp6P0CrsKw-ML1cy5PhKk%3DPZJ%2Bir%3DbcJ45CnDxpaa%2BwNw%40mail.gmail.com.
Ross Scroggs
Sep 21, 2016, 8:15:24 AM9/21/16
to google-ap...@googlegroups.com
Joe,
This is a correction to my previous post. In all of the gam command lines, members should be member. My apologies.
When using gam csv, when an entire argument is ~Name, ~Name is replaced with values from the column Name.
You can also embed a column name within an argument like this: leadingtext~~Name~~trailingtext where leadingtext and trailingtext can be anything.
Following Jay's suggestion, suppose your data is in powerschool.txt
echo GradYear,P1Email,P2Email > header.txt
copy header.txt+powerschool.txt > powerschool.csv
What you want to say is something like this, although this won't actually work: gam csv powerschool.csv gam update group ~~GradYear~~-parents sync member ?????
The problem is how to get ?????? from the powerschool.csv; with Gam 3.71, you can't because the data is in two columns.
Hmmm. If you had PowerSchool output the data in a separate file for each grade with one column of data, then you don't even need the headers and Gam 3.71 can handle it.
Let's suppose the files are 2017.txt, 2018.txt, ... with one email address per line.
Make a CSV file gradyears.csv
GradYear
2017
2018
...
Now say: gam csv gradyears.csv gam update group ~~GradYear~~-parents sync member file ~~GradYear~~.txt
That works if you can make PowerSchool output the data in one column, if not Gam 3.71 can't help.
What to do? Go here: https://github.com/taers232c/GAM-N/releases
With this version of Gam, currently 3.74.02, you can say:
gam csv gradyears.csv gam update group ~~GradYear~~-parents sync member file ~~GradYear~~.txt delimiter ','
The delimiter ',' says to read each row and get multiple values separated by commas.
Ross
Joe Wakeman
Sep 21, 2016, 10:29:30 AM9/21/16
to Google Apps Manager
Adding the headers worked great!
I can run the GAM CSV command twice - one for each email field.
Here is the command I'm running after the header addition:
c:\gam\gam.exe csv fixedParentEmails.csv gam update group ~GradYear~-parents add member user $P1Email
The output I get is this:
ERROR: Header "gradYear~-parents" not found in CSV headers of "GradYear,P1Email,P2Email".
My column is in fact GradYear - I just need to append the '-parents' to it to go to the correct group.
Ross Scroggs
Sep 21, 2016, 10:36:42 AM9/21/16
to google-ap...@googlegroups.com
Joe,
Ross
You need two ~ when a field name is withinn an argument.
c:\gam\gam.exe csv fixedParentEmails.csv gam update group ~~GradYear~~-parents add member user $P1Email
--
You received this message because you are subscribed to the Google Groups "Google Apps Manager" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-man...@googlegroups.com.
To post to this group, send email to google-ap...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-manager.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/5d21c8e9-c638-4c94-84a3-33f12d29255e%40googlegroups.com.
Joe Wakeman
Sep 21, 2016, 5:00:15 PM9/21/16
to Google Apps Manager
Fantastic, that worked wonderfully!
What's the benefits to using sync rather than add ? Currently my script runs gam update group 20##-parents clear, then runs the add users. If sync would be a better workflow though then maybe I should be using that.
What's the benefits to using sync rather than add ? Currently my script runs gam update group 20##-parents clear, then runs the add users. If sync would be a better workflow though then maybe I should be using that.
Ross Scroggs
Sep 21, 2016, 5:51:43 PM9/21/16
to google-ap...@googlegroups.com
Joe,
I was wondering where the clear was happening; sync is going to do a lot less work, but you have to do it in one step.
Make a CSV file gradyears.csv
GradYear
2017
2018
...
You have N CSV Files, 2017.csv, 2018.csv, ... with headers:
GradYear,P1Email,P2Email
...
gam csv gradyears.csv gam update group ~~GradYear~~-parents sync member csvfile ~~GradYear~~.csv:P1Email:P2Email
The magic: csvfile ~~GradYear~~.csv:P1Email:P2Email
This says to read the data from the columns labelled P1Email and P2Email in the ~~GradYear~~.csv file.
Only the adds/removes necessary to update the group are performed; on most days there are probably very few updates relative to the size of the groups.
Ross
On Wed, Sep 21, 2016 at 2:00 PM, Joe Wakeman <jwak...@nevada.k12.ia.us> wrote:
Fantastic, that worked wonderfully!
What's the benefits to using sync rather than add ? Currently my script runs gam update group 20##-parents clear, then runs the add users. If sync would be a better workflow though then maybe I should be using that.
--
You received this message because you are subscribed to the Google Groups "Google Apps Manager" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-manager+unsub...@googlegroups.com.
To post to this group, send email to google-apps-manager@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-manager.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/8e08224d-7e96-4aad-b442-0315cd0ddd4c%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages