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.
- Enabling Notepad++ Dark Theme
- Base 64 Encoding Decoding In Notepad++
- Notepad++ Editor alternatives for Mac OS X
- Replace tabs by spaces or comma Notepad++
- Word wrap text in Notepad++
- How to Convert CSV file to SQL Script using Notepad++
- Add Custom header and footer to Windows Notepad file
- Show Notepad++ tabs in multiple lines instead of scroll on tab bar
- Change default language highlighting in Notepad++
- Delete blank lines in a file using Notepad++
- [Nopepad++] How to add text at end of each line
- Setting up Cloud feature with Notepad++
- Notepad++ Save Failed - Please check if this file is opened in another program.
- Find and Replace Multiple different words with the same word in a text file
- How to un-hide tab bar in notepad++
- Convert text to random case using Notepad++
- Word count in Notepad++
- Compare two text files in Notepad++
- How to check about details of Notepad++ text editor
- How to connect to an FTP or SFTP location using Notepad++
- How do I run HTML file in Notepad? (Windows)
- Where are Notepad++ macros stored?
- How to turn off CR LF CRLF in Notepad++
- Notepad++ Ghost typing Examples
- Add Text at Start and End of Each Line Notepad++
- SharePoint Designer Workflow error - Coercion Failed: Input cannot be null for this coercion - SharePoint
- How to check uptime of Linux/Unix/macOS system/server using console command? - Linux
- How to change font, apply bold or italic styles, font size in Windows Notepad - HowTos
- Indent XML Formatting In Notepad++ - NotepadPlusPlus
- 300+ Eclipse IDE Keyboard Shortcuts for Mac - Eclipse
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,328Open 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','328Now 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\('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','328Go 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.