question Re: Data Reporting with Excel in Archives of Support Questions (Read Only)

Data Reporting with Excel

rajinder_kaur — Fri, 16 Sep 2022 10:35:02 GMT

Hello,

I was doing the following tutorial

https://hortonworks.com/hadoop-tutorial/hello-world-an-introduction-to-hadoop-hcatalog-hive-and-pig/#step5.b.1

I configured the ODBC for windows 7 but i am not sure which username to use while configuring it.

After this when i click i am here:

Open a new blank workbook. Select Data tab at the top then select “Get External Data” and then select “From Other Data Sources”. Then at the bottom select “From Microsoft Query”. Choose your data source and ours is called Hadoop and you will then see the Query Wizard. We will import the avg_mileage table.

I do not see the database for hadoop. This is the step i am stuck. I see following in my excel.

Can anyone help me from this point.

TIA

Re: Data Reporting with Excel

mqureshi — Mon, 15 Aug 2016 22:52:43 GMT

@Rajinder Kaur

When you setup new ODBC connection in windows, it allows you to "test connection". Were you able to do that? It appears that your ODBC setup on windows is not complete, otherwise it will show up here. Another way to test it, is to use some other SQL tool (like winsql) instead of Excel and test from there if it works.

Re: Data Reporting with Excel

TimothySpann — Mon, 15 Aug 2016 23:05:33 GMT

select new data source

http://hortonworks.com/hadoop-tutorial/how-to-install-and-configure-the-hortonworks-odbc-driver-on-windows-7/

Re: Data Reporting with Excel

rajinder_kaur — Mon, 15 Aug 2016 23:24:48 GMT

Yes i did test and it was successful.

Re: Data Reporting with Excel

rajinder_kaur — Mon, 15 Aug 2016 23:32:30 GMT

@mqureshi

I got this response

SUCCESS! Successfully connected to data source! ODBC Version: 03.80 Driver Version: 2.1.2.1002 Bitness: 64-bit Locale: en_US

Re: Data Reporting with Excel

rajinder_kaur — Mon, 15 Aug 2016 23:33:58 GMT

@Timothy Spann

Thanks for your reply. I do all this but when i go in excel, i dnt see my datasource there.

This is the success message I get when i configure the ODBC connection

SUCCESS! Successfully connected to data source! ODBC Version: 03.80 Driver Version: 2.1.2.1002 Bitness: 64-bit Locale: en_US

Re: Data Reporting with Excel

TimothySpann — Mon, 15 Aug 2016 23:38:49 GMT

did you do it under user DSN? sometimes it shows up there. sometimes you have to add it again with new data source. ODBC is touchy

Re: Data Reporting with Excel

mqureshi — Mon, 15 Aug 2016 23:45:25 GMT

Like @Timothy Spann says, check if you did it under "user dsn" or "system dsn". If it's under system, try it under user dsn.

Re: Data Reporting with Excel

rajinder_kaur — Tue, 16 Aug 2016 01:36:01 GMT

@Timothy Spann

@mqureshi

Thanks you both! It worked. It was under system DSN, changed it under USEr DSN and it worked. Thanks.

Will move fwd and let you know if i find any other problem.

Re: Data Reporting with Excel

rajinder_kaur — Mon, 19 Aug 2019 12:06:21 GMT

@Timothy Spann I can see my hadoop datasource in Excel, but when i click on it , i get driver mismatch error.

Here is my configuration:

Re: Data Reporting with Excel

thierry_t_robin — Thu, 06 Jul 2017 14:34:58 GMT

I have the same problem

Which version of EXCEL are you using ?

I think that HortonWorks ODBC driver is not supported in EXCEL 2010.

The HortonWorks tutorial references EXCEL version 2016