416.467.9100 | Dundas Data Visualization | Login
Welcome Guest
This is the support page for the legacy Dundas Dashboard application. For assistance with the current Dundas BI application please click here.
Dashboard v5.0

This site makes extensive use of JavaScript.

Please enable JavaScript in your web browser and reload the page before proceeding.

Importing Data

Hide navigation
RSS
Modified on Tue, 15 Oct 2013 10:42 AM Categorized as Data Connectors, Data Preparation, SharePoint
Click to return to: Documentation | Connecting to Data


Overview

This article shows how to configure the Other Data Sources provider option in the Data Connector Designer. Data from flat files (⪚ CSV, Excel files) or SharePoint sources must be imported into the system. The import can be done on a one-time basis or configured to run on a periodic schedule. Supported data source types include:

  • Flat text files with fixed length columns
  • Flat text files with delimited columns (⪚ CSV)
  • Microsoft Excel files (&ie; XLS or XLSX or XLSM extension)
  • SharePoint Lists
  • SharePoint Excel Services.

Password-protected Excel files are not supported.

Each instance of a file or data source is imported into the system in the form of a single table in the &dw; application (sync) database. Multiple files or data sources can be imported per data connector. In the case of files, a usual practice is to set up a network share folder and copy the files to be imported to that folder beforehand.

Use the Data Sources grid in the Data Connector Designer (below the Provider dropdown) to specify the data sources (⪚ files) that you want to import.

The Other Data Sources provider option in the Data Connector Designer.

The Other Data Sources provider option in the Data Connector Designer.


Before you begin

Some notes to be aware of before you begin adding flat file or SharePoint sources for input:

  • In order to import certain types of data sources, additional software may be required on the server where &dw; is installed. See Data Source Requirements for more details.

  • To import SharePoint Excel data, your SharePoint server must support Excel Services (⪚ you have the MOSS 2007 Enterprise Edition).

  • It is also recommended to check your data sources for invalid or unexpected data, such as “NULL” values in the last row, and clean up the data before importing into &dw;. For example, the last row of your Excel file may contain NULL values if the data was originally copied from SQL Server Management Studio's Edit Top 200 Rows operation, which always includes a row of NULL values at the end.

  • In order for &dw; to be able to access your flat file, you may need to give read permissions on the file to the IIS user account (⪚ ASPNET user on Windows XP), or use the Connect As option from the Data Connector Designer's General tab.

  • If your flat file is on a shared network drive that is on a different domain:
    • Make sure you have a Windows account on both domains with the same username and password.
    • Use the Connect As settings in your data connector but leave the Domain field empty.

Adding data sources for import

Adding a flat file data source for import

To add a flat file (⪚ CSV, Excel) for import:

Tip: Watch the how-to video: Data Connector - Excel

  1. Make sure that you have entered the Connect As settings as needed.
  2. Choose Other Data Sources from the Provider dropdown list.
  3. Click the Add button below the Data Sources grid in the Data Connector Designer. The Define Data Structures dialog is displayed.
  4. In the Define Data Structures dialog, choose the Data Source Type that corresponds to your flat file from the following list:
    • Delimited Columns (⪚ CSV)
    • Fixed Length Columns
    • Excel
  5. In the Table name box, type the friendly name of the database table that will be created (in the &dw; sync database) to hold the imported data.
  6. Enter the fully qualified path name of your file in the File Name box (can use copy/paste instead of typing). You have two options:
    • Specify the full path name of a flat file that exists on the same server where &dw; is installed.
    • Place your flat file in a shared network folder and then specify the full network path to the file (⪚ \\server\shared folder\mydata.csv). This is the most common choice and is the recommended usage.
  7. If the first line of data in your file is a header, select the First line is a header option.
  8. Perform the next set of steps according to the type of your data source:
    • For a flat file with Fixed Length Columns:
      • Click the Code page dropdown list and choose the code page (&ie; character encoding) for your data.
    • For a flat file with Delimited Columns:
      • Click the Code page dropdown list and choose the code page (&ie; character encoding) for your data.
      • Type the delimiter character in the Delimiter box. Or, use the token drop-down menu beside the box to choose a special delimiter such as the TAB character (⪚ if you have a tab-delimited flat file).
        Choosing a delimiter character.
      • If you want to transpose the rows and columns of your flat file data, select the Transpose option.
      • If the field values in your flat file are enclosed in quotes, select the Has fields enclosed in quotes option.
    • For a Microsoft Excel format file:
      • Select the Sheet option to import a single sheet from your Excel file, and then enter the name of the sheet in the box provided (⪚ Sheet1).
      • To import a named range of cells within a sheet, select the Named range option, and then enter the name of the range in the box provided.
        Tip: To create a named range in Excel 2010, select the cells you want, right-click, and then choose Define Name.
      • If you want to transpose the rows and columns of your Excel data, select the Transpose option.
  9. Define the structure of the database table that will hold the imported data.
  10. Optionally define primary keys and/or indexes on the imported table.
  11. Click OK. The Define Data Structures dialog closes and a corresponding row is added to the Data Sources grid in the Data Connector Designer.

The Define Data Structures dialog (for a CSV file).

The Define Data Structures dialog (for a CSV file).


Note: If you encounter an error message, "data source is unavailable" when trying to discover table structure:
  1. Check that the file exists in the specified location by attempting to open the file in SharePoint.
  2. Dundas Dashboard does not allow connection to https SharePoint sites that do not have a valid certificate. The issue can be overcome by adding the SharePoint certificate to the Dundas server.
  3. Make sure the Application Pool Identity you're using has access to the folder where your flat file is saved. By default, Dundas Dashboard is using
    the Network Service Identity account, which does not have access to a Windows user's personal folder such as C:/Users/johnd. Possible
    workarounds are:
    • Move the file to a different folder.
    • In the Data Connector setup, use the Connect As option.
    • Change the Identity account the App Pool is running.




Adding a SharePoint data source for import

To import data from a SharePoint List or Excel Services:

  1. In order for &dw; to connect to your SharePoint server, you will typically need to use the Connect As option from the Data Connector Designer's General tab.
  2. Choose Other Data Sources from the Provider dropdown list.
  3. Click the Add button below the Data Sources grid in the Data Connector Designer. The Define Data Structures dialog is displayed.
  4. In the Define Data Structures dialog, choose the Data Source Type from the following options:
    • SharePoint List
    • SharePoint Excel Services.
  5. In the Table name box, type the friendly name of the database table that will be created (in the &dw; sync database) to hold the imported data.
  6. Enter the URL of your SharePoint server in the SharePoint Site URL box (⪚ http://myserver).
  7. Perform the next steps according to the type of SharePoint data source:
    • For a SharePoint List:
      • Select the List Name using the dropdown provided. This dropdown will be populated automatically with SharePoint lists discovered from your SharePoint server.
      • Select the View Name using the dropdown provided. This dropdown will be populated automatically with views corresponding to the SharePoint list chosen in the previous step.
    • For a SharePoint Excel file:
      • Enter the path name of the Excel file in the Excel File Name box. This can be a full path to the file (⪚ http://myserver/salesdept/DocLib/SalesData.xlsx) or a relative path (⪚ salesdept/DocLib/SalesData.xlsx).
      • Enter the name of the Excel sheet in the Sheet Name box (⪚ Sheet1).
      • If the first line of data in your file is a header, select the First line is a header option.
        SharePoint Excel file.
      • If you want to transpose the rows and columns of your Excel data, select the Transpose option.
  8. Define the structure of the database table that will hold the imported data.
  9. Optionally define primary keys and/or indexes on the imported table.
  10. Click OK. The Define Data Structures dialog closes and a corresponding row is added to the Data Sources grid in the Data Connector Designer.

Note: In order to connect to SharePoint document libraries using a UNC path, the Dundas Dashboard server computer must have the WebClient service running. This will allow for OLE connections to a WebDAV resource. On Windows Server 2008 or higher, the WebDAV component is optional but it can be installed through the Desktop Experience. This is a feature of Windows Server 2008 which you can enable through the Server Management console.

Note: If you encounter an error message "Data source is unavailable" when trying to discover the table structure:
  1. Check that the file exists in the specified location by attempting to open the file in SharePoint.
  2. Dundas Dashboard does not allow connection to https SharePoint sites that do not have a valid certificate. This issue can be overcome by adding the SharePoint certificate to the Dundas server.
  3. Make sure the Application Pool Identity you're using has access to the folder where your flat file is saved. By default, Dundas Dashboard is using
    the Network Service Identity account, which does not have access to a Windows user's personal folder such as C:/Users/johnd. Possible
    workarounds are:
    • Move the file to a different folder.
    • In the Data Connector setup, use the Connect As option.
    • Change the Identity account the App Pool is running.


The Define Data Structures dialog (for a SharePoint List).

The Define Data Structures dialog (for a SharePoint List).


The Data Sources grid in the Data Connector Designer.

The Data Sources grid in the Data Connector Designer.


Defining the database table structure

Once you've entered the details for your data source, such as specifying the file name or delimiter character, you can proceed to define the structure of the database table that will hold the imported data.

The Columns grid in the Define Data Structures dialog shows the current column definitions for your data source. One column definition (&ie; grid row) appears in the grid initially. You can choose to edit existing or add new column definitions manually. Or, you can begin by having &dw; automatically discover the table structure by analyzing your data source.

Automatic discovery

To automatically discover the table structure, click the Discover Table Structure button in the top-right corner of the dialog. If the discovery is successful, the Columns grid will be updated with the discovered column definitions. Note that the automatic discovery option is not available if your Data Source Type is Fixed Length Columns.

You will likely still need to update some of the column definitions manually (as shown in the next section), such as changing the data type of a column, or specifying that Nulls are allowed for a particular column because you know that your data contains such values.

Make sure you perform automatic discovery after you have selected/entered all of your desired settings in the Define Data Structures dialog. For example, if you want to transpose the data, select the Transpose option first before clicking Discover Table Structure. Otherwise, you won't get the expected results once the data is actually imported.

Note: If the discovery operation ends with a network/timeout error (which may happen if you're importing a large file), go back to the General tab and increase the Command timeout to a larger value.

Manual definition

To manually configure the column definitions, follow these steps:

  1. To add a column definition to the grid, which will appear as a new grid row, click the Add button below the Columns grid.
  2. To remove an existing column definition from the grid, select the corresponding grid row, and then click Delete.
  3. Add as many column definitions to the grid as needed to match the number of columns of data in your data source.
  4. Select the first column definition in the Columns grid. The attributes of the column definition appear below the grid. Configure the column attributes as follows:
    • If your data source is a flat file with fixed length columns, specify the length of the column in characters using the Length in file box.
    • Select the Skip option if you do not want to import the current column from your data source.
    • Enter a name for the column in the Name box.
    • Select the Allow Nulls option if your table column will allow Null values.
    • Click the Data Type dropdown list and choose the data type for your column. Supported types include:
      • Binary
      • Boolean
      • Currency
      • DateTime
      • Decimal
      • Double
      • Guid
      • String.
    • Depending on your choice of data type, there are additional options to set:
      • For Binary and String data types, enter the length in characters in the Length box.
      • For the Decimal data type, specify the precision and scale for the column in the boxes provided.
      • For the DateTime data type, optionally specify a format string in the Format box (⪚ yyyy-MM-dd). If you do not specify a format string, an automatic conversion to the DateTime data type will be performed by default using the locale of the server. However, this automatic conversion may give incorrect results if the server locale is different than the intended locale of the dates stored in your data source. In this case, you must specify a format string. For examples of valid formats, see Date and Time Format Strings. Note also that if you do specify a format string, but the format doesn't match your data properly, the import will revert to the default conversion (which may give you incorrect results).
    • Optionally enter a description for the column in the Description box.
  5. Configure the attributes of the remaining columns.

Defining indexes and primary keys

Once you have defined the table structure, you can define primary keys and/or indexes on the imported table to improve query performance.

To define a primary key or index:

  1. Click the Indexes/Keys button in the top right corner of the dialog.
    Indexes/Keys button.
  2. In the Editing indexes/keys dialog, click the Add button. A new index is added to the list on the left and the properties of the (selected) index are displayed in the properties grid on the right.
    The Indexes/Keys dialog.
  3. The Columns property lets you choose the column(s) to build the index or primary key upon, and to choose a sort order. For example, you may want to build an index on a ProductName column and set the sort order to ASC (ascending). To select a different set of columns or change the sort order:
    • Click the Columns property value. An ellipsis button appears.
    • Click the ellipsis button. The Columns dialog is displayed.
    • Use the Columns dialog to add other columns, remove columns, or change the name or sort order of the selected column.
    • Click Close to close the Columns dialog.
  4. To define a primary key instead of an index, set the Type property to Primary Key. For example, you may want to build a primary key on a ProductID column. (The Primary Key option is available if the column does not allow nulls as defined in your table structure.)
  5. Enter a description for your index or primary key using the Description property.
  6. Click Close to close the Indexes/Keys dialog.

The indexes/keys will be created once you save your data connector and run the import process. Note that indexes will be non-unique.

Editing a data source import definition

To edit an existing entry in the Data Sources grid of the Data Connector Designer:

  1. Select the entry that you want to modify from the Data Sources grid.

    The Data Sources grid.
  2. Click Edit. The Define Data Structures dialog is displayed. Use the dialog to modify the data source import definition as required.

Deleting a data source import definition

To delete an existing entry in the Data Sources grid of the Data Connector Designer:

  1. Select the entry that you want to remove from the Data Sources grid.
  2. Click Delete. When prompted to confirm the operation, click Yes. The corresponding entry is removed from the Data Sources grid.

Importing the data

Once you have finished adding or editing data source import definitions (in the Data Sources grid), you can begin importing the actual data as follows:

  1. Click Save from the Toolbar. An information dialog lets you know that the import process will run in the background and may take a long time to complete. Click OK to close this dialog.
    Importing data sources.
  2. A notification message is displayed at the bottom of the General tab in the Data Connector Designer when the import process has completed. The Data Source Description values in the Data Sources grid are updated as well.
    Import completed.
  3. Go to the Design Explorer to view your newly imported table(s).
    Imported table in the Design Explorer.

Note: The timeout for the import process is set to a period of 1 hour, or the value specified by the clientCommunicationTimeout entry in the &dw; Configuration File, whichever is larger.

Scheduling the import process

To schedule a data source import operation from the Data Connector Designer:

  1. Click the Import scheduled button to launch the Define Import Schedule dialog. Use this dialog to schedule the import operation on a one-time or recurring basis. See Setting up a Schedule for more details. Once a schedule has been defined, the Import scheduled grid will display an updated summary of the schedule for the import operation.
  2. If you want to modify an existing schedule, click Edit to launch the Define Import Schedule dialog.
  3. If you want to perform the import manually, click the Run Now button. The import process will run in the background as described earlier in the case of saving your data connector.

The Import scheduled grid shows the last date/time the import process ran and the corresponding last result. If the last run resulted in an error, click the copy to clipboard link to copy the error text to your clipboard. For example, the figure below shows the error that occurs when the file you specified for import cannot be found:

Copy the text of the last result error to the clipboard.

Copy the text of the last result error to the clipboard.


Notes and limitations

Excel

  • Excel tables are not supported for import.
  • If your Excel file contains a column of General type, and you try to import it as a Dundas Dashboard string column, some of the values in the Excel column may not be imported correctly. This is because the ACE.OLEDB driver which is used to import Excel data tries to determine the column type by checking the actual data in the first 8 rows (excluding the header row). There are two workarounds for this issue:
    • Change the Excel column type from General to Text, and then re-import the data.
    • Change a registry setting on the server and then re-import the data. The steps for changing the registry setting are as follows:
      1. Navigate to the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel\
      2. Double-click on the TypeGuessRows item. This is the number of rows scanned by the ACE.OLEDB driver. Change the value to 0 to force the scanning of 16384 rows. This will make the detection more accurate, but may incur a small performance hit.
  • If your Excel file has more than 255 columns, only the first 255 columns can be imported. This is due to a limitation with the Microsoft Access Database Engine 2010 which Dundas Dashboard uses to connect to Excel. Here is a partial workaround though:
    1. Create a new data connector using the Microsoft Access data provider.
    2. Go to the Advanced properties screen for the data connector.
    3. Set the Extended Properties value to: Excel 12.0;HDR=Yes;
    4. Create a new manual virtual table based on this data connector using a SQL query like this:
      select * from [Sheet1$ColumnA:ColumnB]This query retrieves a range of named columns, which can extend past the 255th column. However, note that the query cannot return more than 255 columns in total.

Related topics


Click to return to: Documentation | Connecting to Data

About Dundas | Contact Us Follow us on Twitter! | Privacy Statement | Report Site Issues

Copyright © 2009-2014 Dundas Data Visualization, Inc.