Connecting to Flat Files
This article shows you how to connect to flat files such as CSV and other text files where columns of data are separated by delimiter characters.
2. Drag and drop a flat file
Just like with Excel files, you can drag a CSV or other text file from Windows Explorer and drop it onto the Dundas BI Explore window or the dashboard canvas.
This will automatically import the flat file data to Dundas BI and create a corresponding data connector.
3. Connect to a CSV file
The following walkthrough shows you how to manually create a data connector for a text file which contains comma-delimited stock price data.
Date,Close,Volume,Open,Low,High 2006-01-03,82.06,11715200,82.45,80.81,82.55 2006-01-04,81.95,9837800,82.2,81.33,82.5 2006-01-05,82.5,7213500,81.4,80.999,82.9 2006-01-06,84.95,8197300,83.95,83.41,85.03 2006-01-09,83.73,6857800,83.9,83.38,84.25 2006-01-10,84.07,5701000,83.15,83.12,84.12 2006-01-11,84.17,5776400,84.37,83.4,84.81 2006-01-12,83.57,4924100,83.82,83.4,83.96 2006-01-13,83.17,6921700,83,82.5,83.45 ...
To begin, go to the main menu, click New, and then select Data Connector.
In the New Data Connector dialog, click inside the File Name box. Enter a name for your data connector.
Click the Data Provider dropdown and choose Flat file.
Click the Authentcation dropdown to choose an authentication method. For example, set it to Specified Windows credentials and enter your domain, username, and password on Windows.
Next, specify the CSV file using one of two options:
- Click Choose File and use the file selector to select the CSV file to be imported. Note that changes to the original file will no longer affect the generated data connector.
- Or, use the UNC Path option to specify the full network path to the file (e.g. \\server1\shared\stockdata.csv). Note that changes to the original file will affect the data once the data connector gets refreshed.
For CSV files, the first line of data often contains the header text values. In this case, make sure the First Line Is Header option is checked.
A comma is assumed to be the delimiter character by default. If your text file is delimited by TAB characters, just click inside the Character Used As Delimiter box, delete any exisiting character, and then press the TAB key.
If your data values are not surrounded by quote characters, uncheck the Fields Are Enclosed In Quotes option.
The Define Structure option lets you add a table, add columns to the table, and then define each column by setting its data type and more. While you can define the columns at this point, it is recommended to just click Submit and let the auto-discovery run first. Then you can go back and edit the discovered columns.
Go to Explore and locate the newly added data connector. Expand it to see the CSV file and its discovered columns. Drag the file to the dashboard canvas to see its values displayed in a table visualization (in Raw Data format).
4. Define columns
After the initial creation and discovery of the flat file data connector, you can redefine properties of the columns as needed.
First, go to Explore and locate the flat file data connector. Right-click (or long top) on the data connector and click Edit from the context menu.
In the Edit Data Connector dialog, scroll down and click Define Structure.
In the Define Data Structure dialog, select the table and then scroll down to see the discovered columns. The columns are discovered on-demand so if you don't see any listed, click Re-discover table.
Select a column and then set the details for the column as needed. For example, date/time columns are discovered as string columns so you'll want to change the data type to Date/Time.
Starting in Version 3.0, it is possible to read multiple files under the same folder. To do this, specify the path using a wildcard character as a filename. Files with the same structure will be loaded simultaneously.
For example, the folder below contains 2 CSV files with the same structure:
In the UNC Path, specifiy the path, using wildcard character in the filename part:
"File Name" is included in the structure of the table:
6. See also
- Drag and drop an Excel file
- Connecting to Excel
- Connect to data and view it on a dashboard
- Flat File Connection Issues