/CSVLint

CSV Lint plug-in for Notepad++ for syntax highlighting, automatic column and datatype detecting, validate csv datasets, fixed width data, change datetime format, decimal separator, sort data, count unique values etc. A plugin for data cleaning and working with messy data files.

Primary LanguageC#GNU General Public License v3.0GPL-3.0

CSV Lint - Notepad++ plugin

Release version GitHub all releases GitHub latest release

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.

preview screenshot

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 like the CSV Lint plug-in you can support the project by buying me a coffee!
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

  1. Open your dataset in Notepad++
  2. Open the "CSV Lint window" from the plug-in menu or toolbar
  3. Press "Detect columns" to automatically detect format
  4. Optionally, manually enter or adjust metadata
  5. 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.

Watch video, CSV Lint plug-in features oveview Watch video, CSV File: View, Add Column Colors and Sort Data using Notepad++

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

When you press "Validate data", the input data will be checked 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)

  • Convert datetime values to different formats
  • Convert decimal symbol to point/comma
  • Toggle between comma, semicolon, tab separated formats
  • Improve file reading, to process/edit large files (>1MB)
  • Count unique values based on column(s)
  • Allow format masks per individual column
  • Support quoted strings
  • Support two-digit year date values
  • Support for currency/thousand separator "12.345,00" or "1,250,000.00" etc.
  • Load/save schema.ini
  • Improve automatic datatype detection
  • Add feature GUI click to jump to error line
  • Support code=label values (in schema.json?) + error check + replace-code-with-label
  • Support FrictionlessData schema.json format
  • Improve GUI instead of plain text
  • Add feature generate scripts (SQL, Python, R)
  • Syntax highlighting, display columns as colors
  • Search for value in column, search next empty/non-empty in column
  • Search/replace in single column, multiple columns (option only replace n-th occurance? example "datetime(2008, 1, 1, 12, 59, 00)" replace ', ' with '-' or ':')
  • Search/replace only n-th occurance? Or only empty occurance?
  • Split column into new column ("123/456" -> "123", "456")

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:

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 randomly generated test data.

History

15-dec-2019 - v0.1 first release
02-may-2021 - v0.2 reformat data, double-click jumps to line, various bugfixes
25-aug-2021 - v0.3 quoted string values, syntax highlighting, SQL export
26-sep-2021 - v0.4 performance improvement, save/load metadata, split column option, count unique values
17-oct-2021 - v0.4.1 various bugfixes
29-oct-2021 - v0.4.2 startup error "CSVLint.xml is missing" fixed, toggle toolbar icon, clean up settings
12-nov-2021 - v0.4.3 dark mode icons and colors, save form settings, documentation, bugfixes
19-dec-2021 - v0.4.4 Support large integer values, various bugfixes
12-mar-2022 - v0.4.5 Render on background thread, transparent cursor line, convert to XML/JSON, generate metadata, quotes reformat, help icons, bugfixes
27-apr-2022 - v0.4.5.1 bugfix for Notepad++ 8.4 Lexer v5 update
03-jun-2022 - v0.4.5.2 Another Lexer v5 bugfix, generate Python script
25-jul-2022 - v0.4.5.3 Manually detect columns, improved fixed width support
14-aug-2022 - v0.4.5.4 Improved datatype and datetime mask detection, various bugfixes
30-sep-2022 - v0.4.6 Improved unicode support, sort data option, default color sets 12 colors, various bugfixes
09-oct-2022 - v0.4.6.1 Improved sort data and split options, various bugfixes
29-oct-2022 - v0.4.6.2 Fixed width positions parameter, docked window font, syntax highlighting toggle button

BdR©2021 Free to use - send questions or comments: Bas de Reuver - bdr1976@gmail.com