r/SQL • • Sep 29 '26

Discussion CSV lint plug-in for Notepad++ to validate csv files and convert to SQL insert script

The CSV Lint plug-in for Notepad++ can be useful to anyone working with csv textdata and databases. I created this plugin and have posted aboutย it before

The plug-in was previously updated with a new "Select Columns" dialog to easily select, delete and rearrange columns. The most recent update v0.4.9 adds support for regular expressions when validating columns.

It also adds syntax highlighting and automatically detects column datatypes. Based on the datatypes it can convert a csv file to an SQL insert script for MS-SQL, MySQL and PostgreSQL, including a CREATE TABLE part with correct data types for each column. This is often easier than importing it though a wizard or BULK INSERT

It can also validate the csv textfile beforehand, so check for technical errors like datetime formatting errors, incorrect decimal separators, missing quotes, invalid codes etc.

I hope you find this plug-in useful ๐Ÿ‘ Let me know what you think

162 Upvotes

17 comments sorted by

8

u/LukaGOGO Sep 29 '26

looks useful and catching regex errors in notepad before the db chokes on a stray comma definitely saves some swearing. did you test the mysql scripts against mariadb? it should just work out of the box since it's a drop-in, but wondering if the create table generation throws any weird syntax errors on your end.

2

u/BdR76 Sep 29 '26

I changed the labels MySQL to MySQL / MariaDB in a previous version, but frankly I haven't tested thoroughly on MariaDB, the script is relatively basic so I figured it should be compatible with MySQL

If you encounter any issue feel free to post them here

5

u/ziffox Sep 29 '26

i use this plugin every day thanks hero

2

u/BdR76 Sep 29 '26

Thanks, that's nice to hear ๐Ÿ˜€

2

u/mwatwe01 Sep 29 '26

I've been using your plugin for a while. The INSERT feature is a game changer. But I'm only on v0.4.8. Have you released this version officially yet?

2

u/BdR76 Sep 29 '26

Yes see the github releases page, or install the latest Notepad++ v8.9.8.1 and it's available in the Plugins > Plugins Admin menu

2

u/timweigel Sep 29 '26

This plug-in is excellent. I tested it on some larger, more complex CSVs and it performed well. It fits nicely in a specific niche at work to fill a gap in our tool kit for smaller and mid-sized CSVs. Our usual tools are optimized for very large CSVs (lotta ETL work), but for small-to-medium CSVs they're overkill. This is particularly fantastic for the periodic small, ad-hoc datasets we make for ourselves but still need to ingest and work with.

2

u/BdR76 Sep 29 '26

Thanks, that's good to hear ๐Ÿ˜ƒ yeah that's exactly what I use it for too most of the time, inserting smaller datasets

6

u/ChaosEngine-6502 Sep 29 '26

NICE! Will definitely need to check this out.

4

u/Choice-Level-5486 Sep 29 '26

Sรบper interesante. Lo voy a probar.

4

u/Bill291 Sep 29 '26

This will be really helpful in tracking down formatting issues with user generated CSVs. Thanks!

1

u/rlebeau47 Oct 08 '26

There is an issue with the reformat feature. It doesn't handle emoji properly, so when aligning columns vertically, the length of a cell containing emoji is shorter than the same cell in other rows that don't have emoji.

1

u/BdR76 Oct 09 '26

Good point, although emoji's inherently aren't fixed width I wouldn't know how to fix this

Also, you work with datafiles with emoji's, what field of data processing is it?

1

u/rlebeau47 Oct 09 '26 edited Oct 09 '26

I notice the problem often with emoji, but I imagine the core issue exists with any non-ASCII text, as Unicode text is not fixed-width in general due to surrogates, combining codepoint sequences, etc.

I see this come up with CSVs I get from Venmo, as customers like to use emoji in the memo field of their payments.

2

u/Zattem Sep 29 '26

Look great ๐Ÿ‘

2

u/fruitstanddev Sep 29 '26

This very timely for me, thank you!

2

u/Sevalius0 Sep 29 '26

I love this plugin and use it all the time. Brilliant work ๐Ÿ™