Csv@1
Csv@1 Provider lets you read CSV files without any drivers installed.
CsvOleDB@1 provider allows you to use many SQL constructs, such as GROUP BY, HAVING, ORDER BY, but it needs a driver installed. This provider (Csv@1) is here to help you out in cases, when you don’t have that driver installed.
Example
Data Sources:
- Provider: Csv@1
Connection string: . # this directory (where the project file is)
Name: MyCsvData
Connection String
In the Connection string, you do NOT provide full path to a CSV file. Instead, you provide a path to a directory, where the file is located. This allows you to easily create one Data Source for hundreds of CSV files you might have in a directory that is a subject of your tests.
So with this data source:
- Provider: Csv@1
Connection String: "T:\\MySystemTests\GeneratedCsvFiles"
Name: generated CSVs
You can then use queries like SELECT * FROM [2020.csv], SELECT * FROM [2021.csv] etc.
Using SQL
This provider is driverless, but you still provide the query in form of a SQL statement - it is even required, because CAT needs to know the file you want to iterate (in the Connection string you specify only a directory).
Only these clauses are supported:
| SQL clause | Notes |
|---|---|
| SELECT | You can specify either * or comma separated list of column names, enclosed in square brackets. Different order of columns in SELECT clause is supported (you don’t have to follow the order of columns in the file). |
| FROM | Just name of the file that resides in the directory specified in the Connection string. No joins allowed. |
| WHERE | Only [Column name] = 'something' syntax is allowed. You can use AND operator for more conditions. No functions, no OR, no brackets, just this simple filtering based on one or more columns. |
SELECT [Last Name], [First name]
FROM [Data.csv]
WHERE [First Name] = 'Helena' AND [ID] = 2
Encoding
You can specify encoding when you define a data source (or generally when you use the reader somewhere).
Use Encoding, Encoding name or Charset:
Data Sources:
- Provider: Csv@1
Connection string: . # this directory (where the project file is)
Name: MyCsvData
Encoding: 'utf-32' # or windows-1250, ...
When you don’t specify any, default is ‘utf-8’. CAT uses .NET [System.Text.Encoding.GetEncoding] method to parse it. You can find list of all possible values here: https://learn.microsoft.com/en-us/dotnet/api/system.text.encoding?view=net-7.0.
Use value you find in Name column in that documentation in “List of encodings” section, put it to Encoding in CAT configuration. This is what MS documentation looks like:
