If you have a .csv file and you want to convert it into an SQL insert Query (be MySql, SQLite, Oracle), this can be done in just a few simple steps using Notepad++ Find and Replace with some Regular Expressions.
- Open the CSV (Comma Separated Values) file in Notepad++
- Enclose each data values in CSV file with Single Quotes.
- Forming the Query Go to Find and Replace,
- Now lets complete the Sql script.
- How to hide toolbar on Notepad++
- Change the default Line Encoding Notepad++ (CR LF)
- Notepad++ is about to exit prompt message
- How to force quit or kill Notepad++ Process
- Add Blank Lines Between Each Lines in Notepad++
- Install Notepad++ silently using Windows Powershell
- Prettify JSON in Notepad++
- Notepad++ Convert text from lower to upper case
- Must have CSV Plugins for Notepad++
- How to lock Notepad++ tabs?
- Column Mode Editing in Notepad++
- How to change background color in Notepad++
- Notepad++ sort by name with example
- How to rename a tab in Notepad++
- [Solved] Notepad++ Menu Bar Missing
- Where are Notepad++ temp unsaved files stored?
- Notepad++: How to add Quotes to CSV File
- How to check about details of Notepad++ text editor
- How to close all tabs of Notepad++?
- How choose alternate Tab Bar icon in Notepad++
- How to copy file name and path to clipboard in Notepad++
- Change default language highlighting in Notepad++
- Add Line Number before each line in Notepad++ using Column Editor
- Go to Line Number option in Windows Notepad
- How to show End of Line Characters in File using Notepad++
- [Fix] zsh: command not found: awscli [Mac Terminal] - AWS
- Download Google Chrome setup exe file using PowerShell - Powershell
- New-SPLogFile PowerShell - create new SharePoint log file - SharePoint
- Two Ways to Extract rar (*.rar) files on Mac - MacOS
- Check version of pip package installer for Python - Python
- SharePoint - Use Today's Date Time in list view filter and calculated column - SharePoint
- Notepad++ Save Failed - Please check if this file is opened in another program. - NotepadPlusPlus
- How to fix Microsoft Windows 10 update error 80070020 - Microsoft
Our Sample CSV Data :
1,data1,data-a,323
2,data2,data-b,324
3,data3,data-a,325
4,data4,data-b,326
5,data5,data-a,327
6,data6,data-b,328
Open Find and Replace by pressing "Ctrl + F", go to replace tab and,
Find : ,
Replace with : ','
Result :
1','data1','data-a','323
2','data2','data-b','324
3','data3','data-a','325
4','data4','data-b','326
5','data5','data-a','327
6','data6','data-b','328
Now data values are enclosed with Quotes, expect the row start and end. For this we need to use Regular Expression.
Find : ^
Replace : insert insert myTable values\('
^ is a regex character to add data at the start of each line in the file.
Keep in mind to select "Regular Expression" in Search Mode. \ (slash) is an escape character for braces in the regex.
Result :
insert insert myTable values('1','data1','data-a','323
insert insert myTable values('2','data2','data-b','324
insert insert myTable values('3','data3','data-a','325
insert insert myTable values('4','data4','data-b','326
insert insert myTable values('5','data5','data-a','327
insert insert myTable values('6','data6','data-b','328
Go to Find and Replace again and,
Find : $
Replace : '\);
Make sure that you select "Regular Expression" in Search Mode. \ (slash) is an escape character for braces in regex.
Result :
insert insert myTable values('1','data1','data-a','323');
insert insert myTable values('2','data2','data-b','324');
insert insert myTable values('3','data3','data-a','325');
insert insert myTable values('4','data4','data-b','326');
insert insert myTable values('5','data5','data-a','327');
insert insert myTable values('6','data6','data-b','328');
$ is a regex character to add data at the end of each line in file.
Thats it!! Our SQL Script is now ready!..
Note: Data may not be as simple as we have considered in our example. We may have Single Quotes (') in within itself, So in such cases, you need to first escape it by,
Find : '
Replace : ''
⚠️ Remember to move search mode in "Regular Expression" while using RegEx or else you may not get results.