Create SQL from CSV files for larger updation or insertion
Few days back I came across one problem where I have to create a bulk update script based on a given CSV or Excel file. First I thought of finding something in excel level by writing some macros or functions.
Then my friend helped me to use the easiest method using a sublime text editor. Would like to know about this approach ?
As I said above it's simpler to create and update scripts. Let's take an example I have below CSV file content.
mumbai,firstname.lastname@example.org thane,email@example.com bangalore,firstname.lastname@example.org
Follow below steps :
- Open CSV file in sublime text editor.
- Open Replace toolbar from Menu -> Find -> Replace ( You can directly open by short command Option+Command+F for Mac user, Control+Shift+F for Window or ubuntu user.
- Enable regex option in toolbar ( It will look like .*). You can see 👇🏻 below image.
- Considering two fields in CSV, write
(.*),(.*)in the Find field on the toolbar.
update table tablename set fieldname1 = '$1' where fieldname = '$2'in Replace field.
$1stand for first field and
$2stand for second first. Vice versa if you have multiple fields you can mention
$3and so on.
- Try to click on the find button and check whether it is highlighting properly or not.
- Then you click on "Replace All" and see this output.
Update tablename set fieldname1 = 'mumbai' where fieldname = 'email@example.com'; Update tablename set fieldname1 = 'thane' where fieldname = 'firstname.lastname@example.org'; Update tablename set fieldname1 = 'bangalore' where fieldname = 'email@example.com';
Note : You can write any other SQL like insert or delete to create a SQL script. This is a little hack which can help to save time while creating a script when you are dealing with more numbers of records.
Hope this helps you. Thanks for reading this blog. If any feedback feel free to reach out to me.