The Vertica Database Forums

Hi, Can we connect to Vertica using ODBC in windows? That is, can we use something like Excel to connect?

Hi,

You can connect to a Vertica database in MS Windows via the Vertica ODBC driver which is bundled with the JDBC driver in the 32-Bit and 64-Bit Windows Client software available for download from Vertica's website. Installation of the software is pretty straight forward, just make sure the bit count of the version you are installing is correct for your version of Windows!

Once you have the client software installed you'll want to set up a DSN (Data Source Name) in the ODBC Data Source Administrator Windows component which you can get to under the Administrative Tools program group in the Control Panel. You can also open the run dialog box and type ODBCAD32.EXE...

In the Administrative Tools click the "Add" button and you'll get to the "Create a New Data Source" screen:

• 

Pick the appropriate Vertica driver for your database. My screen shows both driver version 4.1.19 and 5.1.1.

When you click the Finish button you'll see a screen similar to this (if you chose the 4.1.19 drivers):

• 

The window looks a little different if using the 5.1.1 drivers but the core details are the same.

Once you complete the form data you can test the connection with the Test Connection button.

• 

Now that you have the ODBC DSN set up you can easily connect to Vertica in Excel using it. Here is a quick example using Excel 2010...

Open a blank workbook and click on the "From Data Connection Wizard" menu item as shown here:

• 

In the Wizard choose ODBC DSN and then click Next and you should be able to then select the DSN you created earlier.

• 

The next screen will give you a drop down list box containing the databases accessible by your user and below it a list of records showing the tables and views in the selected database.

• 

Pick a table or view and click through the next few formatting screens to copy the data from the database onto your spreadsheet!

This is a very simple example. Play around in Excel and you'll find that you can write your own very detailed queries to pass through the ODBC driver to get any results back you'd want from Vertica.

Have fun!

Wow! Thanks for the great example!

may i ask how do you sever name ? Do you create your own ? if yes how can i create ? How do you do about the database also please kindly help me : ( very impportant .Thanks you

khine wrote:may i ask how do you sever name ? Do you create your own ? if yes how can i create ? How do you do about the database also please kindly help me : ( very impportant .Thanks you

What do you mean ?
Is if you need a running database on server ! is this ?

Yeah, the "server name" is the Vertica database server name...

How do i know sever name ? Sorry for stupid question By the way i am using community edition.