Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Woohoo! Qlik Community has won “Best in Class Community” in the 2024
Khoros Kudos awards!
1 Solution
Accepted Solutions
I have done a work around to export
Run pgAdmin4.exe, the location default location upon installation is the following on a windows server:
C:\Program Files\NPrintingServer\pgsql\pgAdmin 4\bin\pgAdmin4.exe
Connect to the NPrinting Server server with the following credentials:
- Hostname: localhost or "your server name"
- Port: 4992
- Database: nprinting
- User name: postgres
- Password:
Drop down through Severs → nprinting → Schemas
right click on public and select Query Tool
Before executing the following script, you need to ensure that the destination folders of the CSVs (ex: E:/Recepients/input/) to be accessible by postgres.
Also note that the user password is hashed, so has to be changed manually. if a single password applies to all the users, replace 'testuser' below with the password that is used on all the users.
-- FUNCTION: public.export_foo()
-- DROP FUNCTION public.export_foo();
CREATE OR REPLACE FUNCTION public.export_foo(
)
RETURNS void
LANGUAGE 'sql'
COST 100
VOLATILE SECURITY DEFINER
ROWS 0
AS $BODY$
Copy (
SELECT
"Name",
"Description",
"App",
"Enabled",
"Connection",
"Values",
"Numeric Values",
"Formulas"
FROM(
SELECT a.name as "Name",
a.description as "Description",
d.name as "App",
case
when a.enabled = true then upper('TRUE')
when a.enabled = false then upper('FALSE')
else null end as "Enabled",
e.name as "Connection",
case
when evaluate =false and is_numeric ='false' then b.name||'= {'||string_agg(DISTINCT c.value,',')||'}'
else null
end as "Values",
case
when is_numeric =true then b.name||'= {'||string_agg(DISTINCT c.value,',')||'}'
else null
end as "Numeric Values",
case
when evaluate =true then b.name||'= {'||ltrim(string_agg(DISTINCT c.value,','),'=')||'}'
else null
end as "Formulas"
FROM public.filter a
left join filter_field b on a.id=b.filter_id
left join filter_field_value c on b.id=c.filter_field_id
left join app d on a.app_id=d.id
left join data_connection e on b.data_connection_id=e.id
GROUP BY
a.name,
a.description,
d.name,
a.enabled,
e.name,
evaluate,
is_numeric,
b.name
)aaa
) To 'E:/Recepients/input/Filters.csv' With CSV DELIMITER ',';
Copy (
SELECT
E_mail,
Username,
Password,
Domain_Account,
Enabled,
Time_Zone,
initcap(Locale)as Locale,
Nickname,
Title,
Company,
Job_Title,
Department,
Office,
string_agg(DISTINCT Filters,',') as Filters,
string_agg(DISTINCT Groups,',') as Groups,
string_agg(DISTINCT Roles,',') as Roles
FROM(
SELECT
b.email as E_mail,
c.name as Username,
'testuser' as Password,
domain_account as Domain_Account,
case
when a.enabled = true then upper('TRUE')
when a.enabled = false then upper('FALSE')
else null end as Enabled,
timezone as Time_Zone,
locale as Locale,
nickname as Nickname,
title as Title,
company as Company,
job_title as Job_Title,
department as Department,
office as Office,
o.name as Filters,
m.name as Groups,
q.name as Roles
FROM public.usr a
LEFT JOIN email_address as b on (a.email_address_id = b.id)
LEFT JOIN recipient as c on (a.recipient_id=c.id)
LEFT JOIN user_group_to_usr as e on (a.recipient_id=e.user_id)
LEFT JOIN user_group f on (e.user_group_id=f.recipient_id)
LEFT JOIN recipient m on (f.recipient_id=m.id)
LEFT JOIN filter_to_recipient n on (a.recipient_id=n.recipient_id)
LEFT JOIN filter o on(n.filter_id=o.id)
LEFT JOIN role_recipient p on(a.recipient_id=p.recipient_id)
LEFT JOIN role q on (p.role_id = q.id)
)aaa
GROUP BY E_mail,Username,Password,
Domain_Account,
Enabled,
Time_Zone,
Locale,
Nickname,
Title,
Company,
Job_Title,
Department,
Office
) To 'E:/Recepients/input/Users.csv' With CSV DELIMITER ',';
Copy(
SELECT
m.name as "Name",
Description as "Description"
FROM user_group f
Inner JOIN recipient m on (f.recipient_id=m.id)
) To 'E:/Recepients/input/Groups.csv' With CSV DELIMITER ',';
$BODY$;
ALTER FUNCTION public.export_foo()
OWNER TO postgres;
After creating the function, you will have to call upon it using the following script:
SELECT public.export_foo();
This will create Groups.csv, Users.csv & Filters.csv
Open an new excel document and save it as Recipients.xlsx
Add 3 tabs into this xlsx and name the tabs as the following in the same order:
- Filters
- Users
- Groups
Open Filters.csv, copy the data and paste it in the first tab of Recipients.xlsx
Insert the following into the first row of the first tab: Name, Description, App, Enabled, Connection, Values, Numeric Values, Formulas
Open Users.csv, copy the data and paste it in the second tab of Recipients.xlsx
Insert the following into the first row of the second tab: E-mail, Username, Password, Domain Account, Enabled, Time Zone, Locale, Nickname, Title, Company, Job Title, Department, Office, Filters, Groups, Roles
Open Groups.csv, copy the data and paste it in the third tab of Recipients.xlsx
Insert the following into the first row of the third tab: Name, Description
Save Recipients.xlsx and you are good to go.
This xlsx should be in a format that NPrinting17+ accepts to be imported.
I have done a work around to export
Run pgAdmin4.exe, the location default location upon installation is the following on a windows server:
C:\Program Files\NPrintingServer\pgsql\pgAdmin 4\bin\pgAdmin4.exe
Connect to the NPrinting Server server with the following credentials:
- Hostname: localhost or "your server name"
- Port: 4992
- Database: nprinting
- User name: postgres
- Password:
Drop down through Severs → nprinting → Schemas
right click on public and select Query Tool
Before executing the following script, you need to ensure that the destination folders of the CSVs (ex: E:/Recepients/input/) to be accessible by postgres.
Also note that the user password is hashed, so has to be changed manually. if a single password applies to all the users, replace 'testuser' below with the password that is used on all the users.
-- FUNCTION: public.export_foo()
-- DROP FUNCTION public.export_foo();
CREATE OR REPLACE FUNCTION public.export_foo(
)
RETURNS void
LANGUAGE 'sql'
COST 100
VOLATILE SECURITY DEFINER
ROWS 0
AS $BODY$
Copy (
SELECT
"Name",
"Description",
"App",
"Enabled",
"Connection",
"Values",
"Numeric Values",
"Formulas"
FROM(
SELECT a.name as "Name",
a.description as "Description",
d.name as "App",
case
when a.enabled = true then upper('TRUE')
when a.enabled = false then upper('FALSE')
else null end as "Enabled",
e.name as "Connection",
case
when evaluate =false and is_numeric ='false' then b.name||'= {'||string_agg(DISTINCT c.value,',')||'}'
else null
end as "Values",
case
when is_numeric =true then b.name||'= {'||string_agg(DISTINCT c.value,',')||'}'
else null
end as "Numeric Values",
case
when evaluate =true then b.name||'= {'||ltrim(string_agg(DISTINCT c.value,','),'=')||'}'
else null
end as "Formulas"
FROM public.filter a
left join filter_field b on a.id=b.filter_id
left join filter_field_value c on b.id=c.filter_field_id
left join app d on a.app_id=d.id
left join data_connection e on b.data_connection_id=e.id
GROUP BY
a.name,
a.description,
d.name,
a.enabled,
e.name,
evaluate,
is_numeric,
b.name
)aaa
) To 'E:/Recepients/input/Filters.csv' With CSV DELIMITER ',';
Copy (
SELECT
E_mail,
Username,
Password,
Domain_Account,
Enabled,
Time_Zone,
initcap(Locale)as Locale,
Nickname,
Title,
Company,
Job_Title,
Department,
Office,
string_agg(DISTINCT Filters,',') as Filters,
string_agg(DISTINCT Groups,',') as Groups,
string_agg(DISTINCT Roles,',') as Roles
FROM(
SELECT
b.email as E_mail,
c.name as Username,
'testuser' as Password,
domain_account as Domain_Account,
case
when a.enabled = true then upper('TRUE')
when a.enabled = false then upper('FALSE')
else null end as Enabled,
timezone as Time_Zone,
locale as Locale,
nickname as Nickname,
title as Title,
company as Company,
job_title as Job_Title,
department as Department,
office as Office,
o.name as Filters,
m.name as Groups,
q.name as Roles
FROM public.usr a
LEFT JOIN email_address as b on (a.email_address_id = b.id)
LEFT JOIN recipient as c on (a.recipient_id=c.id)
LEFT JOIN user_group_to_usr as e on (a.recipient_id=e.user_id)
LEFT JOIN user_group f on (e.user_group_id=f.recipient_id)
LEFT JOIN recipient m on (f.recipient_id=m.id)
LEFT JOIN filter_to_recipient n on (a.recipient_id=n.recipient_id)
LEFT JOIN filter o on(n.filter_id=o.id)
LEFT JOIN role_recipient p on(a.recipient_id=p.recipient_id)
LEFT JOIN role q on (p.role_id = q.id)
)aaa
GROUP BY E_mail,Username,Password,
Domain_Account,
Enabled,
Time_Zone,
Locale,
Nickname,
Title,
Company,
Job_Title,
Department,
Office
) To 'E:/Recepients/input/Users.csv' With CSV DELIMITER ',';
Copy(
SELECT
m.name as "Name",
Description as "Description"
FROM user_group f
Inner JOIN recipient m on (f.recipient_id=m.id)
) To 'E:/Recepients/input/Groups.csv' With CSV DELIMITER ',';
$BODY$;
ALTER FUNCTION public.export_foo()
OWNER TO postgres;
After creating the function, you will have to call upon it using the following script:
SELECT public.export_foo();
This will create Groups.csv, Users.csv & Filters.csv
Open an new excel document and save it as Recipients.xlsx
Add 3 tabs into this xlsx and name the tabs as the following in the same order:
- Filters
- Users
- Groups
Open Filters.csv, copy the data and paste it in the first tab of Recipients.xlsx
Insert the following into the first row of the first tab: Name, Description, App, Enabled, Connection, Values, Numeric Values, Formulas
Open Users.csv, copy the data and paste it in the second tab of Recipients.xlsx
Insert the following into the first row of the second tab: E-mail, Username, Password, Domain Account, Enabled, Time Zone, Locale, Nickname, Title, Company, Job Title, Department, Office, Filters, Groups, Roles
Open Groups.csv, copy the data and paste it in the third tab of Recipients.xlsx
Insert the following into the first row of the third tab: Name, Description
Save Recipients.xlsx and you are good to go.
This xlsx should be in a format that NPrinting17+ accepts to be imported.
