CSV File

Local CSV file that you can query against

Local CSV FIle Overview

The CSV File connector will load a CSV file into a local SQLite database that can be visualized.

See the SQLiteconnector for details about querying SQLite for visualizations.

Column Headers

The first row of the CSV is considered as headers for the columns. Ensure that your CSV has column headers.

Column Data Types

The CSV data will be automatically inspected and the appropriate data type selected. All of the data in a column must resolve to the same type, or it will be considered a string.

The data types are checked in order of the following. If a Datetime, Date or Numeric is not found, the type will be string

1. Datetime

2. Date

3. Numeric

   1. Will also check for 'numberish' values (see Numberish Values below)

4. String

   1. If maximum length < 255, then data type will be VARCHAR with the length being the maximum length found

Numberish Values

Many common real world number notations will be converted to numbers during import.

• Negative parenthesis like (150) will be converted to -150

• Scientific notation like 10^2 will be converted to 100

• Thousands notation like 7.5k will be converted to 7500

All number conversions are based on US/English comma/decimal standard. For example, international 1.200,5 will get converted to 1.2005 instead of 1200.5. As such, do not currently detect commas as decimal separators.

A full example list of conversions is described below:

RawInput	ExpectedOutput	Description
100	100	Plain number
1,000	1000	Comma as thousand separator
$100	100	Dollar sign
€200	200	Euro sign
£300	300	Pound sign
50%	0.5	Percentage
(400)	-400	Parentheses for negatives
5k	5000	Text for thousands
7.5M	7500000	Text for millions
1,234.56	1234.56	Period as decimal separator
-100	-100	Negative sign
₹500	500	Indian Rupee sign
¥600	600	Yen sign
500₩	500	Won sign
200m	200000000	Text for hundreds of millions
1B	1000000000	Text for billions
2.5%	0.025	Decimal percentage
3,456.789	3456.789	Number with comma and period
(1,234.56)	-1234.56	Negative with comma and period
10^2	100	Scientific notation
2e3	2000	Exponential notation
-.5	-0.5	Leading decimal negative
.75	0.75	Leading decimal

Externally Connecting to the SQLite Database

You may wish to connect to the imported CSV with another database browser like DBeaver. You can find the sqlite file in:

./{{home}}/ChartSQL/{{installPath}}/db/csvfile/{{datasourceName}}/data.sqlite

Where as:

• {{home}} is your system user data directory

• {{installPath}} is the location of your ChartSQL Studio Installation

• {{datasourceName}} is the name of your configured CSV Datasource