Bulk user import
19 July, 2011
With Yellowfin you can load a large set of users into your application through the Bulk User import process. This is available through the user management forms.
1. To load you bulk users navigate to the manage users page. Below the list of your users you will see the Bulk Import Link. Click this link to continue.
2. You users should be contained in a CSV format with data contained within quotes. See the example below and on the screen for more detail. The file must contain the following parameters:
First Name, Last Name, Initial, Salutation Code, Language Code, Time Zone Code, Email Address, UserName, Password, Role Code
3. Click the browse button to locate and load your file.
4. Click the Upload Data File to load and save your new users.
1. To load you bulk users navigate to the manage users page. Below the list of your users you will see the Bulk Import Link. Click this link to continue.
2. You users should be contained in a CSV format with data contained within quotes. See the example below and on the screen for more detail. The file must contain the following parameters:
First Name, Last Name, Initial, Salutation Code, Language Code, Time Zone Code, Email Address, UserName, Password, Role Code
3. Click the browse button to locate and load your file.
4. Click the Upload Data File to load and save your new users.
Hi,
What is the "time zone code"? The sample csv format is blank for this parameter...
Cheers
What is the "time zone code"? The sample csv format is blank for this parameter...
Cheers
Hi,
I have put all available timezone codes into the following CSV : AvailableTimezones.csv
I have also created a report which you can run against your Yellowfin repository DB to find a list of the current user roles. You can get the report xml here :
YellowfinUserRoles.xml
Please let us know if you have any issues with this.
Regards,
David
I have put all available timezone codes into the following CSV : AvailableTimezones.csv
I have also created a report which you can run against your Yellowfin repository DB to find a list of the current user roles. You can get the report xml here :
YellowfinUserRoles.xml
Please let us know if you have any issues with this.
Regards,
David
Thanks David.
Is it possible to overwrite existing users' timezones without impacting on existing user settings?
Cheers
Is it possible to overwrite existing users' timezones without impacting on existing user settings?
Cheers
Hi,
The only way to update the Timezone is to open the user account and modify. However since you are doing this with a bulk user import, your best option is to delete the users from user management and then put the correct timezones in the CSV file.
You can then re-upload the CSV .
Since you have just done your import, I wouldn't expect you to lose many settings.
Also doing it this way means, the CSV file is fine, so it can be used again at a later date.
If you cannot do it this way there may be a way to do it directly in the DB, though it won't be supported and cannot discuss this via the support forum.
If you wish to go down this path, please email support@yellowfin.bi and reference this post.
Regards,
David
The only way to update the Timezone is to open the user account and modify. However since you are doing this with a bulk user import, your best option is to delete the users from user management and then put the correct timezones in the CSV file.
You can then re-upload the CSV .
Since you have just done your import, I wouldn't expect you to lose many settings.
Also doing it this way means, the CSV file is fine, so it can be used again at a later date.
If you cannot do it this way there may be a way to do it directly in the DB, though it won't be supported and cannot discuss this via the support forum.
If you wish to go down this path, please email support@yellowfin.bi and reference this post.
Regards,
David
Hello to all our readers,
due to popular demand...here is a query to populate your bulk user CSV file:
[code]SELECT p.FirstName, p.LastName, p.MiddleInitial, p.SalutationCode, p.PreferredLanguageCode, p.LocalTimeZoneCode, c.emailAddress, i.EmailLeft, i.Password, s.RoleCode
FROM person p
INNER JOIN IpContact c ON p.IpPerson = c.IpID
INNER JOIN IpClass i ON p.IpPerson = i.IpID
INNER JOIN StaffMemberRole s ON p.IpPerson = s.IpEmployee[/code]
due to popular demand...here is a query to populate your bulk user CSV file:
[code]SELECT p.FirstName, p.LastName, p.MiddleInitial, p.SalutationCode, p.PreferredLanguageCode, p.LocalTimeZoneCode, c.emailAddress, i.EmailLeft, i.Password, s.RoleCode
FROM person p
INNER JOIN IpContact c ON p.IpPerson = c.IpID
INNER JOIN IpClass i ON p.IpPerson = i.IpID
INNER JOIN StaffMemberRole s ON p.IpPerson = s.IpEmployee[/code]
I'd just like to add a couple of extras to David's query - if you have expired users they need to be excluded, and users who have changed roles then only the current role code should be included.
[code]SELECT p.FirstName, p.LastName, p.MiddleInitial, p.SalutationCode, p.PreferredLanguageCode, p.LocalTimeZoneCode, c.emailAddress, i.EmailLeft, i.Password, s.RoleCode
FROM person p
INNER JOIN IpContact c ON p.IpPerson = c.IpID
INNER JOIN IpClass i ON p.IpPerson = i.IpID
INNER JOIN StaffMemberRole s ON p.IpPerson = s.IpEmployee
WHERE i.enddate > now() and s.enddate > now();[/code]
Filtering on the 'enddate' fields as shown above ensures the export is properly filtered. The above syntax is correct for PostgreSQL. For HSQLDB replace 'now()' with 'today', for MS SQL Server replace 'now()' with 'getdate()'
Also worth noting is that unless there is a blank line after the last record in the resulting CSV file, the user import program will not recognise that 10 fields are present and the file cannot be loaded.
[code]SELECT p.FirstName, p.LastName, p.MiddleInitial, p.SalutationCode, p.PreferredLanguageCode, p.LocalTimeZoneCode, c.emailAddress, i.EmailLeft, i.Password, s.RoleCode
FROM person p
INNER JOIN IpContact c ON p.IpPerson = c.IpID
INNER JOIN IpClass i ON p.IpPerson = i.IpID
INNER JOIN StaffMemberRole s ON p.IpPerson = s.IpEmployee
WHERE i.enddate > now() and s.enddate > now();[/code]
Filtering on the 'enddate' fields as shown above ensures the export is properly filtered. The above syntax is correct for PostgreSQL. For HSQLDB replace 'now()' with 'today', for MS SQL Server replace 'now()' with 'getdate()'
Also worth noting is that unless there is a blank line after the last record in the resulting CSV file, the user import program will not recognise that 10 fields are present and the file cannot be loaded.
Hi David,
Can we filter by particular client org the user has access to in the SQL you provided?
Thanks
Can we filter by particular client org the user has access to in the SQL you provided?
Thanks
Hi Paul,
Apologies for the delayed response.
Unfortunately we don't have a query to do this, as it involves including an extra 2 tables.
Something we will need to write up, and once we have done so can post it here.
However, you can do this manually by getting a list of users across ALL orgs.
The following query will help with that:
select IpParent, IpChild, RltshpTypeCode from IpRltshp where EndDate='9999-12-31 00:00:00.000' and RltshpTypeCode='STAFFMEMBER'
IpParent=Client Org ID
IpChild=Person ID
You can link Client Org ID to organisation table to get org name.
You can link Person ID to IpClass/Person table to get user tables.
Sorry, I don't have an ETA though when we will have the 1 SQL to get all this info for you. But hopefully the above gets you there with some manual work, as I assume it's not something you would do on a weekly basis.
Thanks
David
Apologies for the delayed response.
Unfortunately we don't have a query to do this, as it involves including an extra 2 tables.
Something we will need to write up, and once we have done so can post it here.
However, you can do this manually by getting a list of users across ALL orgs.
The following query will help with that:
select IpParent, IpChild, RltshpTypeCode from IpRltshp where EndDate='9999-12-31 00:00:00.000' and RltshpTypeCode='STAFFMEMBER'
IpParent=Client Org ID
IpChild=Person ID
You can link Client Org ID to organisation table to get org name.
You can link Person ID to IpClass/Person table to get user tables.
Sorry, I don't have an ETA though when we will have the 1 SQL to get all this info for you. But hopefully the above gets you there with some manual work, as I assume it's not something you would do on a weekly basis.
Thanks
David