Code2care : TechZone Apps & Tutorials

How to Convert CSV file to SQL Script using Notepad++

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 few simple steps using Notepad++ Find and Replace with Regular Expressions.

CSV file to SQL Script


1. Open the CSV (Comma Separated Values) file in Notepad++



    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



2. Enclose each data values in CSV file with Single Quotes.



    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.

3. Forming the Query



    Go to Find and Replace,

    Find : ^

    Replace : insert insert myTable values\('


    ^ is a regx character to add data at the start of each line in file.

    Keep in mind to select "Regular Expression" in Search Mode. \ (slash) is an escape character for braces in regx.

    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


4. Now lets complete the Sql script.



    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 regx.

    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 regx character to add data at the end of each line in file.

Thats its!! Our SQL Script is now ready!..

Note : Data may not be as simple as we have consider in our example. We may have Single Quotes (') in within itself, So in such cases you need to first escape it by,

Find : '
Replace : ''









Top




Posts related to notepadplusplus.


1. How to Do Column Mode Editing in Notepad++

2. Setting up Cloud feature with Notepad++

3. Base64 Encoding Decoding In Notepad++

4. Add blank lines after each lines using Notepad++ text editor

5. 97 Useful Notepad++ Keyboard Shortcuts List

6. Compare Files with Notepad++ Plugin

7. Convert SQL to CSV in Notepad++

8. 15 Notepad++ Editor alternatives for Mac OS X

9. Disable Control Scroll Zoom-in and Zoom-out in Notepad++

10. How to Convert CSV file to SQL Script using Notepad++

11. Using Document Map in Notepad++

12. Add Blank Lines Notepad++

13. How to remove blank lines from a file using Notepad++

14. Add Text at Start and End of Each Line Notepad++

15. Indent XML Formatting In Notepad++

16. Replace tabs by spaces or comma Notepad++

17. ASCII to HEX and HEX to ASCII Conversion Notepad++

18. Error opening file for writing Notepad++ Setup

19. Launch Notepad++ html document in any web browser

20. How to do Multi-Line Editing in Notepad++

    more...






Popular tags
android
x 175
eclipse
x 29
notepadplusplus
x 20
macosx
x 14
sharepoint
x 14
html
x 14
mac
x 13
androidstudio
x 11




1000+ C Programs     PHP Tutorial     JSON Tutorial     Swift Tutorial     India Pinocdes     About Us     Privacy Policy


Code2care © 2012-17