EXPORTING DATA TO EXCEL
Posted: Sat Aug 27, 2016 6:49 pm
Hello, I am using the 2016 autumn version of PATSTAT, and as it is the first time I use this software or SQL language, a few issues have arisen.
A couple of questions here:
1) I am trying to find a way of exporting tables to Excel in order to create graphics and manipulate data as I wish. How may I do such export?
2) I am trying to extract a timeline of patent publications worldwide according to a certain IPC classification. I have counted patent publications which had the same publication day for acquiring a table containing patent publications, and the number of patent publications in a given day.
PATSTAT requires users to select appln_id in order to open the "results" and "statistics windows". If I select appln_id in the query, the sum of patent publication will not succeed, as there are many different patent appln_id for the same publn_date.
Is there any query to solve this problem?
I post hereby the query I have written:
select distinct a.appln_id,a.publn_date, count (a.publn_date)
from tls211_pat_publn a, tls209_appln_ipc b
where a.appln_id = b.appln_id
and publn_date <= '2015-12-31' and publn_date >= '2010-01-01'and publn_date<> '9999-12-31'
and (ipc_class_symbol = 'E04D 13/18'
or ipc_class_symbol = 'F26B 3/28'
or ipc_class_symbol like 'F03G 6/%'
or ipc_class_symbol like 'F24J 2/%'
or ipc_class_symbol ='C02F 1/14')
group by a.publn_date, a.appln_id
order by publn_date asc
Now the query which I believe gave me the best results, but without permission to access the statistics window:
select a.publn_date, count (a.publn_date)
from tls211_pat_publn a, tls209_appln_ipc b
where a.appln_id = b.appln_id
and publn_date <= '2015-12-31' and publn_date >= '2010-01-01'and publn_date<> '9999-12-31'
and (ipc_class_symbol = 'E04D 13/18'
or ipc_class_symbol = 'F26B 3/28'
or ipc_class_symbol like 'F03G 6/%'
or ipc_class_symbol like 'F24J 2/%'
or ipc_class_symbol ='C02F 1/14')
group by a.publn_date
order by publn_date asc
Thank you very much
A couple of questions here:
1) I am trying to find a way of exporting tables to Excel in order to create graphics and manipulate data as I wish. How may I do such export?
2) I am trying to extract a timeline of patent publications worldwide according to a certain IPC classification. I have counted patent publications which had the same publication day for acquiring a table containing patent publications, and the number of patent publications in a given day.
PATSTAT requires users to select appln_id in order to open the "results" and "statistics windows". If I select appln_id in the query, the sum of patent publication will not succeed, as there are many different patent appln_id for the same publn_date.
Is there any query to solve this problem?
I post hereby the query I have written:
select distinct a.appln_id,a.publn_date, count (a.publn_date)
from tls211_pat_publn a, tls209_appln_ipc b
where a.appln_id = b.appln_id
and publn_date <= '2015-12-31' and publn_date >= '2010-01-01'and publn_date<> '9999-12-31'
and (ipc_class_symbol = 'E04D 13/18'
or ipc_class_symbol = 'F26B 3/28'
or ipc_class_symbol like 'F03G 6/%'
or ipc_class_symbol like 'F24J 2/%'
or ipc_class_symbol ='C02F 1/14')
group by a.publn_date, a.appln_id
order by publn_date asc
Now the query which I believe gave me the best results, but without permission to access the statistics window:
select a.publn_date, count (a.publn_date)
from tls211_pat_publn a, tls209_appln_ipc b
where a.appln_id = b.appln_id
and publn_date <= '2015-12-31' and publn_date >= '2010-01-01'and publn_date<> '9999-12-31'
and (ipc_class_symbol = 'E04D 13/18'
or ipc_class_symbol = 'F26B 3/28'
or ipc_class_symbol like 'F03G 6/%'
or ipc_class_symbol like 'F24J 2/%'
or ipc_class_symbol ='C02F 1/14')
group by a.publn_date
order by publn_date asc
Thank you very much