AWK – Generate Insert Statements
As I have mentioned before a good reason to know sed, awk and vim is that you may run into a situation where you have no access to other power tools to accomplish a job. Below is an awk script which will generate an insert statement for each line of a delimited input file. You may then use the statements to run on any database so that you can import data. Take note of the example command-line and be sure you make your call appropriate to whether you are on Linux of Windows. Originally the below was written for when I was working on Windows and I had to modify it so that it works properly for Linux. If you are running on Windows you will need to use the escape backslash “\” to escape the parentheses (that appear in the generated input statement) and also the quote and then just use the Windows example statement. If you are on Linux the below should be good to go … just make sure you use the Linux example command-line.
# Program Name: awkIns
# Purpose: Generate INSERT statement for delimited input file
# Created By: Ted Colman ( tedsedawkandvim.com )
# Date: 05/19/2017
# To use:
# In Section 1:
# modify BEGIN clause as indicated in below comment
# modify array variable "t" as directed in below comment
# In the example command-line change
# testTblInput.txt and test_Results_output.txt according
# to your input / output files
# Remember to place this awk script and input / output script
# in the same directory and then run script!
# example command-line (Windows):
# awk -F\t -f awkIns testTblInput.txt > test_Results_output.txt
# example command-line (Linux):
# awk -F "\t" -f awkIns testTblInput.txt > test_Results_output.txt
BEGIN{
################## Section 1
# Modify INSERT clause between the below dotted lines to correspond
# to your table
# ..................................................................
x="INSERT compare.testTbl (rowID, desc, typeID, desc2) \nVALUES (";
# ..................................................................
# Modify below so that fields requiring quotes have a quote
# Note that fields 2 and 4 are varchar fields and require a quote
t[2]="'";
t[4]="'";
}
################## Section 2
# Note: Nothing to change in this section
# initialize variable y to blank at beginning of each new line
{y=""}
# build VALUES clause
{for (j=1; j<=NF; ++j)
{if (j != NF)
y=y" "t[j]$j t[j]","
else y=y" "t[j]$j t[j];}
}
# print entire INSERT statement
{print x y " );\n\n"}