Connect to Excel Spreadsheets¶

This page describes how to connect to an Excel spreadsheet as a source or target within Jitterbit Studio, and how to select a specific worksheet within the Excel spreadsheet if required.

Connect to an Excel Spreadsheet¶

Setting up the connection to an Excel spreadsheet is done while creating your source or target within Jitterbit Studio.

1. While creating your source or target, select Database as the type. Below the type, the Connection Parameters section should now appear.

2. Next to Driver, use the Select button to choose one of the locally available ODBC Excel driver options available from the menu.

   Note

   Drivers that are locally available include those provided with your Private Agent's operating system, as well as any you have installed yourself. These drivers are not distributed with the Jitterbit software.

   Note that some operating systems may not provide the driver you want to use, so you may need to install it yourself (see Install Additional ODBC or JDBC Drivers). For example, Windows 10 comes with x32 .xls ODBC drivers that support Excel versions 3.0, 4.0, 5.0/95, and 97-2000. However, to get the newer ODBC drivers that support all of the above versions, and also Excel 12.0 (Excel 2007), you can install one of the following:

   • Microsoft Access 2016 Runtime (Win 7, 8, 10, 2008 R2, 2012)

   • Microsoft Access 2010 Redistributable (Win XP, Vista, 7, 8, 2003 R2, 2008, 2012)

3. Leave the other boxes blank, and click Options below to expand additional settings.

4. Check the box next to Construct connection string manually. In the text box, use one of the following connection strings depending on your version of Excel. You will also need to replace the folder path and file name with those specific to the file located on your Private Agent.

   Excel 97, Excel 2000, Excel 2002, Excel 2003

   Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
   DBQ=C:\Folder Path\My File Name.xls;
   

   Excel 2007, Excel 2010, Excel 2013

   Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
   DBQ=C:\Folder Path\My File Name.xlsx;
   

   To specify the connection to be updateable (e.g. you want to allow writing to a target file), you can add ReadOnly=0; to the end of the connection string. For example:

   Updateable

   Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
   DBQ=C:\Folder Path\My File Name.xlsx;ReadOnly=0;
   

   Tip

   See additional options for Excel connection strings at Microsoft Excel 2007 ODBC Driver connection strings in the The Connection Strings Reference.

   Example

   The following is an example of a configured source using a 97-2003 Excel spreadsheet located on the Private Agent:

   

5. Use the Test Connection button to verify your connection is successful.

   Note

   If you cannot connect, check to make sure your Excel file is not open and double-check that your connection string matches the specifications of your file.

   

Select a Worksheet within the Excel Spreadsheet¶

If your Excel spreadsheet has multiple tabs, or worksheets, you can also specify which one to use. This is done while creating a transformation, during the Table Selection step.

1. Before creating the transformation, make sure you have followed the steps above to create a connection to your Excel spreadsheet.

2. The Table Selection screen asks you to select the tables and/or views you want to use in the transformation.

   1. Prior to downloading a list of tables, you can limit your search by entering search criteria or by selecting the appropriate checkbox(es) for Tables, Views, or System Tables.

   2. Click the button to Download List of Tables, and you should see the tabs within your Excel file.

   3. Select one or more worksheets from your spreadsheet and move them to Selected Tables using the arrow buttons.

      

      Note

      If no available tables appear, try toggling on the Tables, Views, or System Tables checkboxes depending on your file, then click Download List of Tables again to refresh the results.

      Caution

      If you receive an error "[Microsoft][ODBC Excel Driver] Operation must use an updateable query," then your Excel file is read only. You can allow your Excel file to be updateable by appending ReadOnly=0; to your connection string as described in step 4 of Connect to an Excel Spreadsheet above.

3. Click Next to continue. Then continue setting up your transformation as normal.