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.
- Notepad++ hex editor Examples
- Are Windows Notepad files recoverable?
- How to add or remove bookmark on a line in Notepad++
- How to rename a tab in Notepad++
- Go to Line Number option in Windows Notepad
- How to Apply Themes to Notepad++
- Notepad++ Save Failed - Please check if this file is opened in another program.
- Encode/Decode URL Query String in Notepad++
- Remove duplicate lines using Notepad++
- Add comma or semicolon at end of each line Notepad++
- How to open Notepad?
- Where are Notepad++ macros stored?
- How to check about details of Notepad++ text editor
- Notepad++ Export file as HTML
- Indent/Prettify HTML File in Notepad++
- Notepad++ Reload - This file has been modified by another program. Do you want to reload it?
- List of Programming Languages Supported by Notepad++
- Notepad++ Editor alternatives for Mac OS X
- Add Custom header and footer to Windows Notepad file
- How to un-hide tab bar in notepad++
- Notepad++ is about to exit prompt message
- Launch Notepad++ html document in any web browser
- Word wrap text in Notepad++
- Disable Control Scroll Zoom-in and Zoom-out in Notepad++
- Word count in Notepad++
- Android AlertDialog with 3 buttons example - Android
- How to docker remove a container when it exits - Docker
- How to remove/delete a directory in Linux/macOs - Linux
- Java 8: Get First and Last Date of the Week for Given Date - Java
- Calculate Area of ellipse - C-Program
- Delete file using PHP code : unlink() - PHP
- Find Difference of two numbers - C-Program
- TypeError: must be str, not int [Fix Python] - Python
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.
