Import data from excel - PATSTAT 2017 Autumn

Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
Post Reply

pardeep.naik
Posts: 5
Joined: Fri Feb 16, 2018 2:20 pm

Import data from excel - PATSTAT 2017 Autumn

Post by pardeep.naik » Fri Feb 16, 2018 2:30 pm

Hi all,

I am using PATSTAT 2017 Autumn version. I have a list of publication number kept in excel file and I want to extract IPC codes associated with those publication numbers. But the problem is that this list contains approximate 8000 unique publication numbers. Its very time consuming for me to write them all in a single query. Can anybody please tell me that is there any way through which I can import all publication numbers from Excel file to PATSTAT OR any other way through which I can get IPC codes of these all publication numbers at once (or in few queries) without copy pasting every publication number in WHERE statement.

Thanks in advance!
Regards,
Pardeep


EPO / PATSTAT Support
Posts: 425
Joined: Thu Feb 22, 2007 5:33 pm
Contact:

Re: Import data from excel - PATSTAT 2017 Autumn

Post by EPO / PATSTAT Support » Fri Feb 16, 2018 4:21 pm

Hello Pardeep,
It depends a bit how your publication numbers are formatted.
In pure SQL you could use the "IN" function in the WHERE clause.
Something like this:

Code: Select all

select publn_auth, publn_nr, publn_nr_original,ipc_class_symbol
from  tls211_pat_publn join tls209_appln_ipc on tls211_pat_publn.appln_id = tls209_appln_ipc.appln_id
where publn_auth+publn_nr in ('ep1000000', 'ep2000000', 'ep3000000')
group by publn_auth, publn_nr, publn_nr_original,ipc_class_symbol
order by publn_auth+publn_nr, ipc_class_symbol
But I assume your 8000 publication numbers are already the result from some other data aggregation exercise, so it might be easier to translate that into the query instead of a 2 step approach.

ALSO: you have to keep in mind that 10.000 characters is the limitation of the length of the query. So you can not make a query with 8000 numbers I one go.
PATSTAT Raw Data installed on a local server does ofcourse not have these limitations.


Geert Boedt
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Fr3dY
Posts: 26
Joined: Mon Oct 17, 2016 8:57 am

Re: Import data from excel - PATSTAT 2017 Autumn

Post by Fr3dY » Tue Feb 20, 2018 9:56 am

pardeep.naik wrote:
Fri Feb 16, 2018 2:30 pm
Hi all,

I am using PATSTAT 2017 Autumn version. I have a list of publication number kept in excel file and I want to extract IPC codes associated with those publication numbers. But the problem is that this list contains approximate 8000 unique publication numbers. Its very time consuming for me to write them all in a single query. Can anybody please tell me that is there any way through which I can import all publication numbers from Excel file to PATSTAT OR any other way through which I can get IPC codes of these all publication numbers at once (or in few queries) without copy pasting every publication number in WHERE statement.

Thanks in advance!
Regards,
Pardeep
<<Assuming you're using PATSTAT on a local DB server, not PATSTAT ONLINE>>
Hi,

Try to load the excel file into the database as a new table, create an index for the pub. number and then JOIN it with PATSTAT source tables by publication_number (much better than using the IN clause with many values).




Regards,


Post Reply