Re: [Dev] create single dss service by join two different excel workbooks
Hi Akila, You can use Nested queries in DSS to achieve the requirement you have mentioned. This is explained in [1]. You have to define two data sources for your two excel files. Then you can use them in the nested queries as explained in [1]. [1] - https://docs.wso2.com/display/DSS321/Defining+Nested+Queries Thanks, On Mon, Aug 11, 2014 at 6:43 PM, Akila Nimantha [IT/EKO/LOITS] aki...@lolctech.com wrote: Hi all, I am trying to make a single data service server service by using two different excel workbooks. In both excel files common ‘ID’ column. I want to extract data from both excel files by joining those files. How can I do that with dss …??? Regards, Akila This message (including any attachments) is intended only for the use of the individual or entity to which it is addressed and may contain information that is non-public, proprietary, privileged, confidential, and exempt from disclosure under applicable law or may constitute as attorney work product. If you are not the intended recipient, you are hereby notified that any use, dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, notify us immediately by telephone and (i) destroy this message if a facsimile or (ii) delete this message immediately if this is an electronic communication. Thank you. ___ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev -- Rajith Vitharana Software Engineer, WSO2 Inc. : wso2.com Mobile : +94715883223 Blog : http://lankavitharana.blogspot.com/ ___ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev
Re: [Dev] create single dss service by join two different excel workbooks
Hi Akila, I have tried out your scenario. Attached here with are the sample Excel files I have used and the dataservice I have created. You'll need to change the excel file paths in dataservice file, and then deploy it in DSS. Thanks, On Tue, Aug 12, 2014 at 12:04 PM, Rajith Vitharana raji...@wso2.com wrote: Hi Akila, You can use Nested queries in DSS to achieve the requirement you have mentioned. This is explained in [1]. You have to define two data sources for your two excel files. Then you can use them in the nested queries as explained in [1]. [1] - https://docs.wso2.com/display/DSS321/Defining+Nested+Queries Thanks, On Mon, Aug 11, 2014 at 6:43 PM, Akila Nimantha [IT/EKO/LOITS] aki...@lolctech.com wrote: Hi all, I am trying to make a single data service server service by using two different excel workbooks. In both excel files common ‘ID’ column. I want to extract data from both excel files by joining those files. How can I do that with dss …??? Regards, Akila This message (including any attachments) is intended only for the use of the individual or entity to which it is addressed and may contain information that is non-public, proprietary, privileged, confidential, and exempt from disclosure under applicable law or may constitute as attorney work product. If you are not the intended recipient, you are hereby notified that any use, dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, notify us immediately by telephone and (i) destroy this message if a facsimile or (ii) delete this message immediately if this is an electronic communication. Thank you. ___ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev -- Rajith Vitharana Software Engineer, WSO2 Inc. : wso2.com Mobile : +94715883223 Blog : http://lankavitharana.blogspot.com/ -- Rajith Vitharana Software Engineer, WSO2 Inc. : wso2.com Mobile : +94715883223 Blog : http://lankavitharana.blogspot.com/ excel1.dbs Description: Binary data sheetV1.xls Description: MS-Excel spreadsheet sheetV2.xls Description: MS-Excel spreadsheet ___ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev
Re: [Dev] create single dss service by join two different excel workbooks
Hi Rajith, Thank you very much for the amazing reply and its working fine. When you reply second mail I was trying the same in two different sheets but in one excel document(only one source created). But it gives some error like ‘canot find ‘FROM’ in the statement’. Can we access two(or more) sheets in same excel document…??? Thank you very much for your time. Regards, Akila From: Rajith Vitharana [mailto:raji...@wso2.com] Sent: Tuesday, August 12, 2014 3:39 PM To: Akila Nimantha [IT/EKO/LOITS] Cc: dev@wso2.org Subject: Re: [Dev] create single dss service by join two different excel workbooks Hi Akila, I have tried out your scenario. Attached here with are the sample Excel files I have used and the dataservice I have created. You'll need to change the excel file paths in dataservice file, and then deploy it in DSS. Thanks, On Tue, Aug 12, 2014 at 12:04 PM, Rajith Vitharana raji...@wso2.commailto:raji...@wso2.com wrote: Hi Akila, You can use Nested queries in DSS to achieve the requirement you have mentioned. This is explained in [1]. You have to define two data sources for your two excel files. Then you can use them in the nested queries as explained in [1]. [1] - https://docs.wso2.com/display/DSS321/Defining+Nested+Queries Thanks, On Mon, Aug 11, 2014 at 6:43 PM, Akila Nimantha [IT/EKO/LOITS] aki...@lolctech.commailto:aki...@lolctech.com wrote: Hi all, I am trying to make a single data service server service by using two different excel workbooks. In both excel files common ‘ID’ column. I want to extract data from both excel files by joining those files. How can I do that with dss …??? Regards, Akila This message (including any attachments) is intended only for the use of the individual or entity to which it is addressed and may contain information that is non-public, proprietary, privileged, confidential, and exempt from disclosure under applicable law or may constitute as attorney work product. If you are not the intended recipient, you are hereby notified that any use, dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, notify us immediately by telephone and (i) destroy this message if a facsimile or (ii) delete this message immediately if this is an electronic communication. Thank you. ___ Dev mailing list Dev@wso2.orgmailto:Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev -- Rajith Vitharana Software Engineer, WSO2 Inc. : wso2.comhttp://wso2.com/ Mobile : +94715883223 Blog : http://lankavitharana.blogspot.com/ -- Rajith Vitharana Software Engineer, WSO2 Inc. : wso2.comhttp://wso2.com/ Mobile : +94715883223 Blog : http://lankavitharana.blogspot.com/ This message (including any attachments) is intended only for the use of the individual or entity to which it is addressed and may contain information that is non-public, proprietary, privileged, confidential, and exempt from disclosure under applicable law or may constitute as attorney work product. If you are not the intended recipient, you are hereby notified that any use, dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, notify us immediately by telephone and (i) destroy this message if a facsimile or (ii) delete this message immediately if this is an electronic communication. Thank you. ___ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev
Re: [Dev] create single dss service by join two different excel workbooks
Hi Akila, I assume you have enabled the Use Query Mode( as you can see in the attached screenshot) option when you created the data sources using excel sheets(I assume you have done this, otherwise wizard window won't show a sql query editor in the Add new query step). To answer the question about accessing two sheets in same excel document, Yes you can do that. For that treat worksheet as a table and use sheet name as the table name. You'll see this has been done in the attached excel1.dbs file in previous mail. (FYI - This dbs file is just a xml document, so you can open it in any text editor, there you'll see the sql queries I have used to extract data.) Thanks, On Tue, Aug 12, 2014 at 7:24 PM, Akila Nimantha [IT/EKO/LOITS] aki...@lolctech.com wrote: Hi Rajith, Thank you very much for the amazing reply and its working fine. When you reply second mail I was trying the same in two different sheets but in one excel document(only one source created). But it gives some error like ‘canot find ‘FROM’ in the statement’. Can we access two(or more) sheets in same excel document…??? Thank you very much for your time. Regards, Akila *From:* Rajith Vitharana [mailto:raji...@wso2.com] *Sent:* Tuesday, August 12, 2014 3:39 PM *To:* Akila Nimantha [IT/EKO/LOITS] *Cc:* dev@wso2.org *Subject:* Re: [Dev] create single dss service by join two different excel workbooks Hi Akila, I have tried out your scenario. Attached here with are the sample Excel files I have used and the dataservice I have created. You'll need to change the excel file paths in dataservice file, and then deploy it in DSS. Thanks, On Tue, Aug 12, 2014 at 12:04 PM, Rajith Vitharana raji...@wso2.com wrote: Hi Akila, You can use Nested queries in DSS to achieve the requirement you have mentioned. This is explained in [1]. You have to define two data sources for your two excel files. Then you can use them in the nested queries as explained in [1]. [1] - https://docs.wso2.com/display/DSS321/Defining+Nested+Queries Thanks, On Mon, Aug 11, 2014 at 6:43 PM, Akila Nimantha [IT/EKO/LOITS] aki...@lolctech.com wrote: Hi all, I am trying to make a single data service server service by using two different excel workbooks. In both excel files common ‘ID’ column. I want to extract data from both excel files by joining those files. How can I do that with dss …??? Regards, Akila This message (including any attachments) is intended only for the use of the individual or entity to which it is addressed and may contain information that is non-public, proprietary, privileged, confidential, and exempt from disclosure under applicable law or may constitute as attorney work product. If you are not the intended recipient, you are hereby notified that any use, dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, notify us immediately by telephone and (i) destroy this message if a facsimile or (ii) delete this message immediately if this is an electronic communication. Thank you. ___ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev -- Rajith Vitharana Software Engineer, WSO2 Inc. : wso2.com Mobile : +94715883223 Blog : http://lankavitharana.blogspot.com/ -- Rajith Vitharana Software Engineer, WSO2 Inc. : wso2.com Mobile : +94715883223 Blog : http://lankavitharana.blogspot.com/ This message (including any attachments) is intended only for the use of the individual or entity to which it is addressed and may contain information that is non-public, proprietary, privileged, confidential, and exempt from disclosure under applicable law or may constitute as attorney work product. If you are not the intended recipient, you are hereby notified that any use, dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, notify us immediately by telephone and (i) destroy this message if a facsimile or (ii) delete this message immediately if this is an electronic communication. Thank you. -- Rajith Vitharana Software Engineer, WSO2 Inc. : wso2.com Mobile : +94715883223 Blog : http://lankavitharana.blogspot.com/ creatingDataSource Description: Binary data ___ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev
Re: [Dev] create single dss service by join two different excel workbooks
Hi Rajith, Done it. Now its working and extract data from sheets in same excel file. I think I forgot to add namespace in to the result element. FYI I attached new modified excel( with two Sheets)(I put your two excel files as one ☺ ) and dbs file also. Thank you very much for your instant replies Rajith. Regards, Akila From: Rajith Vitharana [mailto:raji...@wso2.com] Sent: Wednesday, August 13, 2014 8:21 AM To: Akila Nimantha [IT/EKO/LOITS] Cc: dev@wso2.org Subject: Re: [Dev] create single dss service by join two different excel workbooks Hi Akila, I assume you have enabled the Use Query Mode( as you can see in the attached screenshot) option when you created the data sources using excel sheets(I assume you have done this, otherwise wizard window won't show a sql query editor in the Add new query step). To answer the question about accessing two sheets in same excel document, Yes you can do that. For that treat worksheet as a table and use sheet name as the table name. You'll see this has been done in the attached excel1.dbs file in previous mail. (FYI - This dbs file is just a xml document, so you can open it in any text editor, there you'll see the sql queries I have used to extract data.) Thanks, On Tue, Aug 12, 2014 at 7:24 PM, Akila Nimantha [IT/EKO/LOITS] aki...@lolctech.commailto:aki...@lolctech.com wrote: Hi Rajith, Thank you very much for the amazing reply and its working fine. When you reply second mail I was trying the same in two different sheets but in one excel document(only one source created). But it gives some error like ‘canot find ‘FROM’ in the statement’. Can we access two(or more) sheets in same excel document…??? Thank you very much for your time. Regards, Akila From: Rajith Vitharana [mailto:raji...@wso2.commailto:raji...@wso2.com] Sent: Tuesday, August 12, 2014 3:39 PM To: Akila Nimantha [IT/EKO/LOITS] Cc: dev@wso2.orgmailto:dev@wso2.org Subject: Re: [Dev] create single dss service by join two different excel workbooks Hi Akila, I have tried out your scenario. Attached here with are the sample Excel files I have used and the dataservice I have created. You'll need to change the excel file paths in dataservice file, and then deploy it in DSS. Thanks, On Tue, Aug 12, 2014 at 12:04 PM, Rajith Vitharana raji...@wso2.commailto:raji...@wso2.com wrote: Hi Akila, You can use Nested queries in DSS to achieve the requirement you have mentioned. This is explained in [1]. You have to define two data sources for your two excel files. Then you can use them in the nested queries as explained in [1]. [1] - https://docs.wso2.com/display/DSS321/Defining+Nested+Queries Thanks, On Mon, Aug 11, 2014 at 6:43 PM, Akila Nimantha [IT/EKO/LOITS] aki...@lolctech.commailto:aki...@lolctech.com wrote: Hi all, I am trying to make a single data service server service by using two different excel workbooks. In both excel files common ‘ID’ column. I want to extract data from both excel files by joining those files. How can I do that with dss …??? Regards, Akila This message (including any attachments) is intended only for the use of the individual or entity to which it is addressed and may contain information that is non-public, proprietary, privileged, confidential, and exempt from disclosure under applicable law or may constitute as attorney work product. If you are not the intended recipient, you are hereby notified that any use, dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, notify us immediately by telephone and (i) destroy this message if a facsimile or (ii) delete this message immediately if this is an electronic communication. Thank you. ___ Dev mailing list Dev@wso2.orgmailto:Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev -- Rajith Vitharana Software Engineer, WSO2 Inc. : wso2.comhttp://wso2.com/ Mobile : +94715883223tel:%2B94715883223 Blog : http://lankavitharana.blogspot.com/ -- Rajith Vitharana Software Engineer, WSO2 Inc. : wso2.comhttp://wso2.com/ Mobile : +94715883223tel:%2B94715883223 Blog : http://lankavitharana.blogspot.com/ This message (including any attachments) is intended only for the use of the individual or entity to which it is addressed and may contain information that is non-public, proprietary, privileged, confidential, and exempt from disclosure under applicable law or may constitute as attorney work product. If you are not the intended recipient, you are hereby notified that any use, dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, notify us immediately by telephone and (i) destroy this message if a facsimile or (ii) delete this message immediately if this is an electronic communication. Thank you. -- Rajith Vitharana Software Engineer, WSO2 Inc. : wso2.comhttp://wso2.com/ Mobile
Re: [Dev] create single dss service by join two different excel workbooks
Hi Akila, Great to hear that it works fine and being able to assist you in the process :). Thanks, On Wed, Aug 13, 2014 at 9:37 AM, Akila Nimantha [IT/EKO/LOITS] aki...@lolctech.com wrote: Hi Rajith, Done it. Now its working and extract data from sheets in same excel file. I think I forgot to add namespace in to the result element. FYI I attached new modified excel( with two Sheets)(I put your two excel files as one J ) and dbs file also. Thank you very much for your instant replies Rajith. Regards, Akila *From:* Rajith Vitharana [mailto:raji...@wso2.com] *Sent:* Wednesday, August 13, 2014 8:21 AM *To:* Akila Nimantha [IT/EKO/LOITS] *Cc:* dev@wso2.org *Subject:* Re: [Dev] create single dss service by join two different excel workbooks Hi Akila, I assume you have enabled the Use Query Mode( as you can see in the attached screenshot) option when you created the data sources using excel sheets(I assume you have done this, otherwise wizard window won't show a sql query editor in the Add new query step). To answer the question about accessing two sheets in same excel document, Yes you can do that. For that treat worksheet as a table and use sheet name as the table name. You'll see this has been done in the attached excel1.dbs file in previous mail. (FYI - This dbs file is just a xml document, so you can open it in any text editor, there you'll see the sql queries I have used to extract data.) Thanks, On Tue, Aug 12, 2014 at 7:24 PM, Akila Nimantha [IT/EKO/LOITS] aki...@lolctech.com wrote: Hi Rajith, Thank you very much for the amazing reply and its working fine. When you reply second mail I was trying the same in two different sheets but in one excel document(only one source created). But it gives some error like ‘canot find ‘FROM’ in the statement’. Can we access two(or more) sheets in same excel document…??? Thank you very much for your time. Regards, Akila *From:* Rajith Vitharana [mailto:raji...@wso2.com] *Sent:* Tuesday, August 12, 2014 3:39 PM *To:* Akila Nimantha [IT/EKO/LOITS] *Cc:* dev@wso2.org *Subject:* Re: [Dev] create single dss service by join two different excel workbooks Hi Akila, I have tried out your scenario. Attached here with are the sample Excel files I have used and the dataservice I have created. You'll need to change the excel file paths in dataservice file, and then deploy it in DSS. Thanks, On Tue, Aug 12, 2014 at 12:04 PM, Rajith Vitharana raji...@wso2.com wrote: Hi Akila, You can use Nested queries in DSS to achieve the requirement you have mentioned. This is explained in [1]. You have to define two data sources for your two excel files. Then you can use them in the nested queries as explained in [1]. [1] - https://docs.wso2.com/display/DSS321/Defining+Nested+Queries Thanks, On Mon, Aug 11, 2014 at 6:43 PM, Akila Nimantha [IT/EKO/LOITS] aki...@lolctech.com wrote: Hi all, I am trying to make a single data service server service by using two different excel workbooks. In both excel files common ‘ID’ column. I want to extract data from both excel files by joining those files. How can I do that with dss …??? Regards, Akila This message (including any attachments) is intended only for the use of the individual or entity to which it is addressed and may contain information that is non-public, proprietary, privileged, confidential, and exempt from disclosure under applicable law or may constitute as attorney work product. If you are not the intended recipient, you are hereby notified that any use, dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, notify us immediately by telephone and (i) destroy this message if a facsimile or (ii) delete this message immediately if this is an electronic communication. Thank you. ___ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev -- Rajith Vitharana Software Engineer, WSO2 Inc. : wso2.com Mobile : +94715883223 Blog : http://lankavitharana.blogspot.com/ -- Rajith Vitharana Software Engineer, WSO2 Inc. : wso2.com Mobile : +94715883223 Blog : http://lankavitharana.blogspot.com/ This message (including any attachments) is intended only for the use of the individual or entity to which it is addressed and may contain information that is non-public, proprietary, privileged, confidential, and exempt from disclosure under applicable law or may constitute as attorney work product. If you are not the intended recipient, you are hereby notified that any use, dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, notify us immediately by telephone and (i) destroy this message if a facsimile or (ii) delete this message immediately
[Dev] create single dss service by join two different excel workbooks
Hi all, I am trying to make a single data service server service by using two different excel workbooks. In both excel files common 'ID' column. I want to extract data from both excel files by joining those files. How can I do that with dss ...??? Regards, Akila This message (including any attachments) is intended only for the use of the individual or entity to which it is addressed and may contain information that is non-public, proprietary, privileged, confidential, and exempt from disclosure under applicable law or may constitute as attorney work product. If you are not the intended recipient, you are hereby notified that any use, dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, notify us immediately by telephone and (i) destroy this message if a facsimile or (ii) delete this message immediately if this is an electronic communication. Thank you. ___ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev
