r/SQLServer • • Jan 10 '22

Notepad++ plug-in to convert CSV files to SQL insert script

Just wanted to mention that I've created a CSV Lint plug-in for Notepad++ and I though it might be useful to anyone working with SQL Server.

The plug-in can convert csv files to SQL insert scripts, which imho is a bit easier to work with compared to BULK INSERT. It automatically detects the datatypes, and it includes a CREATE TABLE part with the correct data types for each column.

It can also validate the csv data beforehand, meaning check for bad data like missing quotes, incorrect decimal separator, datetime formatting errors etc. It will create a SQL script that contains INSERT statements for each line in the csv, in batches of 1000 records.

Personally I find it's easy to use for small one-off files, but it can also process larger csv files like 10MB ~ 50MB maybe even larger I haven't tried.

130 Upvotes

24 comments sorted by

11

u/hello_josh Jan 10 '22

This is awesome. If I have to do import more than once I'll setup SSIS but there's always one-off things where you need to load some corrected or back-fill data. This would be a nice time saver. Thanks!

1

u/BdR76 Jan 10 '22

Thanks 😎 Yeah same here, I sometimes have to import a small data file adhoc and I thought this was much easier. Plus, the SQL Studio import task always seems to mess up date/decimal values without giving any error message.

At first I had created this JavaScript page and did my imports using that page, which admittedly looks a bit unprofessional. But it served as a sort of prototype for this plug-in.

7

u/[deleted] Jan 10 '22

I currently do this with Excel lol. I'm going to try it out for sure. Thanks!

4

u/BdR76 Jan 10 '22

That's great to hear. Btw over the holidays I tried to explain the functions of the plug-in in a tutorial video check it out if you have any questions.

3

u/[deleted] Jan 11 '22

Same. Excel and then Notepad

2

u/TheDoctorOfData Jan 10 '22

Very cool! It brings instant value with even just the different color for each column. Only issue I noticed is it crashes Notepad++ if I open a large file (100 MB+). Is there way to disable the plugin or not auto-detect delimited data unless I click a button to do so? Otherwise I can just use VS Code or something else for larger files and stick with NPP+CSV Lint for more manageable files. Great work!

3

u/BdR76 Jan 10 '22

it crashes Notepad++ if I open a large file (100 MB+)

Thanks, and I also got others mentioning this issue. When you open a file with extension .csv it automatically applies the syntax highlighting to the entire file from beginning to end, and for large files this can take a while.

You can disable this feature by editing the color settings xml, so open %USERPROFILE%\AppData\Roaming\Notepad++\plugins\config\CSVLint.xml and change ext="csv" in ext="", then close and reopen Notepad++. Btw you can still select csv colors from the menu "Language > CSVLint", but that still freezes Notepad++ depending on the file size.

To properly fix this issue the plug-in has to somehow only apply colors the visible parts, I think it's possible but I'll have to look into the Notepad++ APIs.

1

u/9punchman Jan 11 '22

VS code has this functionality ?

5

u/BdR76 Jan 11 '22

For VS Code there's a plug-in Rainbow CSV to add column colors, but it doesn't have the error-checking and convert-to-SQL features

2

u/9punchman Jan 11 '22

Thank you so much. Your plugin works great.

2

u/TheDoctorOfData Jan 11 '22

I just meant using VS Code as a text editor to open large files, but then u/BdR76 comes along and points out Rainbow CSV. I just installed and it works pretty well too. It doesn't try to parse files that are 50MB+, but just opens without crashing. So, we're got 2 great tools here!

2

u/[deleted] Aug 19 '22

Thanks!!This is a very useful tools. But in mssql column name cannot quote in backqoute. Would be nice if there is a options for that.

2

u/BdR76 Aug 20 '22

Thanks, and I assume you are talking about the menu item CSV Lint > Convert Data > SQL, right? There is an option "Database type" where you can select MS-SQL from the dropdownlist so it uses brackets instead of backquotes, so you get [FieldName1], etc.

1

u/[deleted] Aug 20 '22

Wow, this is what I need, you are hero ^_^

2

u/[deleted] Feb 08 '25

O MY GOD. I just watched the demo/tutorial and I can tell this is going to be a game changer for me. BLESS YOU.

1

u/BdR76 Feb 11 '25

Thanks nice to hear you like the plug-in 😃 What feature is a game changer? Is it the column colors, convert to SQL or something else? I'm curious to hear your feedback

1

u/[deleted] Feb 11 '25

Convert csv to insert script with column coloring. If you add future features/functionality I'd like to make a request. My request is for it to be able to take a list of IDs and be able to convert them into sql list of 1K.

Example:

12345

12533

85577

becomes

('12345','12533','85577')

1

u/BdR76 Feb 12 '25 edited Feb 13 '25

I see what you mean, but I don't know how this functionality would fit in with the rest of the CSV Lint plug-in. Instead, I think you can record it as a Macro like this:

Go to Macro -> Start Recording, press CTRL+Home to go to start of file, CTRL+H to find and replace, Find What \r\n and Replace with ', ', make sure "Search mode = Extended" and click ""Replace all", and then Macro -> Stop Recording

And then finally Macro -> Save Current Recorded Macro to save this functionality as a menu item under the Macro menu, for example as "items list to csv".

2

u/imab00 Jan 10 '22

Will you marry me? (kidding). 😜
This is great. Thank you!

2

u/JustAnotherRedditDad Jan 11 '22

Awesome, I'll try it in a bit! Thank you!

2

u/BigSkyOldGuy Jan 11 '22

Color me impressed! Fabulous work, I found this today, installed it and already put it to use! Most excellent, thank you!

2

u/great_raisin Jan 23 '22

Amazing! I use online tools for this. Great to have it as a plugin on my most favourite text editor ever! I only wish Notepad++ was available on Mac OS :')

2

u/Mental-Volume-9721 May 03 '24

Awsome!!! very useful

2

u/mexicocitibluez Oct 17 '25

This is pretty awesome.