CSV Lint - Notepad++ plugin

CSV Lint plug-in for Notepad++ adds syntax highlighting to comma-separated values (csv) and fixed width data files. It automatically detects column datatypes, can detect data formatting errors and adjust datetime and decimal formats.

Use CSV Lint for metadata discovery, technical data validation and reformatting on tabular data files. It is not meant to be a replacement for spreadsheet programs like Excel or SPSS, but rather it's a quality control tool to examine, verify or polish up a dataset before further processing.

With CSV Lint you can take a dataset and:

Scan for metadata, i.e. detect columns and datatypes
Create schema.ini based on metadata
Validate data against schema.ini
Convert datetime/decimal values to different formats
Convert between comma, semicolon, tab separated, fixed width formats
Split valid/invalid values into two separate columns
Count unique values of one or more columns
Convert csv data to SQL insert script

CSV Lint is stable and usable for most general use-cases, but it is a work-in-progress, so if you encounter any bugs or unexpected output I encourage you to report issues here. CSVLint is based on a prototype project Dataset MultiTool

If you find the CSV Lint plug-in useful you can buy me a coffee! ☕

How to install

The CSV Lint plugin is available in Notepad++ in the Plugins Admin menu.

Install Notepad++
In Notepad++ go to menu item Plugins > Plugins Admin...
On tab Available search for csv lint
Check the checkbox and press Install button
Click Yes to quit Notepad++ and "continue the operations"
Click Yes on the Windows notification "Allow app to make changes"

Note: the plug-in is available in the Plugins Admin menu starting with Notepad++ v8.1.9.1. If you have a Notepad++ version older than v8.1.9.1 or want to install the plug-in manually:

Go to the releases page
Find the latest release
32bit version; unzip CSVLint.dll (32bit) to folder .\Program Files (x86)\Notepad++\plugins\CSVLint\CSVLint.dll.
64bit version; unzip CSVLint.dll (64bit) to folder .\Program Files\Notepad++\plugins\CSVLint\CSVLint.dll.
restart Notepad++

How to use it

Open your dataset in Notepad++
Open the "CSV Lint window" from the plug-in menu or toolbar
Press "Detect columns" to automatically detect format
Optionally, manually enter or adjust metadata
Press "Validate data" to detect any data errors

If there are no errors in the data, you can click "Reformat data" for data reformatting options, or select "Convert to SQL" menu item to generate an SQL insert script.

Also see this quick tour video, which shows how the plug-in works.

Schema.ini

The metadata uses the standard schema.ini format, see documentation here

When you open a csv file the plug-in try to determine the column meta data. It will first look for a schema.ini file in the same folder as the data file, and check to see if it contains a section with the filename. If the file or section doesn't exist, it will scan the data and try to infer the columns and datatypes. You can manually change the meta data and press the blue disk icon to save it to a schema.ini file in the same folder as the data file for later use.

See schema.ini example below:

[mydata.csv]
Format=TabDelimited
DateTimeFormat=dd-mm-yyyy
DecimalSymbol=.
NumberDigits=2
Col1=OrderId Integer Width 8
Col2=Price Float Width 7
Col3=PartName Text Width 50
Col4=OrderDate DateTime Width 10

Format can be TabDelimited for tabs, CSVDelimited for commas, for any other delimiter use for example Format=Delimited(;). Use FixedLength for fixed width text files and set the Width for each column.

DateTimeFormat is not case sensitive and uses dd/mm/yyyy or yyyy-mm-dd hh:nn:ss etc.

DecimalSymbol can be either . or , and CSV Lint will assume the thousand separators symbol is the opposite of the DecimalSymbol. Define the maximum decimals digits for example NumberDigits=2 for values like "1.23" or "-45.67" etc.

Validating

The plug-in can be used as a CSV validator, press "Validate data" to check the input data for technical errors, based on the metadata in the textbox on the left. The line numbers of any errors will be logged in the textbox on the right. It will check the input data for the following errors:

Values that are too long, example value "abcde" when column is "Width 4"
Non-numeric values in numeric columns, example value "n/a" when column datatype is Integer
Incorrect decimal separator, example value "12.34" when DecimalSymbol is set to comma
Too many decimals, example value "12.345" when NumberDigits=2.
Incorrect date format, example value "12/31/2018" when DateTimeFormat=dd/mm/yyyy

Roadmap/goals

The CSV Lint plugin is work-in-progress, here is list of features I want to add (~~strikethrough~~ is done)

Trouble shooting / Known issues

When you press the "Validate Data" button after editing the data file, the text and metadata are not always synchronised immediately. if you get unexpected validation results, try saving the datafile or refreshing the meta data before clicking "Detect columns".
When you press "Detect columns" the datetime format of the data isn't always detected correctly. Especially when the data contains values like 05/06/2022 the order of day and month can be incorrect. You can adjust it manually to match your data before pressing the "Validate data" button.
When you select Language > CSVLint to enable the syntax highlighing colors, or change the metadata manually, the column colors aren't always updated immediately. Click inside the textfile or switch tabs to a different file and then back and it should display correctly.

Acknowledgements

With thanks to:

kbilsted for providing the excellent NotepadPlusPlusPluginPack.Net
jokedst, CsvQuery was the inspiration for converting datasetmultitool to a Notepad++ plug-in
DonHo for creating Notepad++ and everyone at the Notepad++ Community forum for providing feedback
Anyone who shared their plug-in on GitHub

The CSV Lint plug-in couldn't have been created without their source examples, suggestions and valuable feedback. For a similar plug-in for Visual Studio Code check out Rainbow CSV.

Disclaimer

This software is free-to-use and it is provided as-is without warranty of any kind, always back-up your data files to prevent data loss.
The test data, examples and screenshots provided in this github repository do not contain real data, it is all randomly generated test data.

BdR76/CSVLint

BdR76

Reviews

Repository Details