Importing Excel data to SQL insert statements

Using Excel formulas to construct SQL insert statements is easy and powerful. It is much better than the alternatives (eg copy and paste). It works great for simple values, but the problem is sometimes text columns contain quotes or line breaks. 

Here is a simple template - use this if you don't have any apostrophes, quotes or line breaks in the source text.

="insert into usercode (user_code,Tariff,Concession,description) values ('"&A2&"','"&B2&"','"&C2&"')"

Here is a template that escapes quotes and removes line breaks from the description column. It also trims the length on the description column to 70 chars (in this case D2). You should adjust it to suit actual columns.

Here is the whole statement:

="insert into usercode (user_code,Tariff,Concession,description) values ('"&A2&"','"&B2&"','"&C2&"','"&trim(SUBSTITUTE(left(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"`","'"),CHAR(34),"'"),CHAR(13)," "),CHAR(10),""),70),"'","''"))&"')"

This part does the replacements of bad chars. It is easier to copy and paste this part below rather than findomg where the brackets and quotes end in the above:

TRIM(SUBSTITUTE(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"`","'"),CHAR(34),"'"),CHAR(13)," "),CHAR(10),""),999),"'","''"))

Here is a template that creates update statements where you have data in the following columns:

column 1 - ProductID field

column 2 - long text field Description

="update product set description='"&TRIM(SUBSTITUTE(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"`","'"),CHAR(34),"'"),CHAR(13)," "),CHAR(10),""),8000),"'","''"))&"' where productid="&A1

Here's an example of using this technique for creating C# code from a spreadsheet. 

="{"&A1&","""&TRIM(SUBSTITUTE(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"`","'"),CHAR(34),"'"),CHAR(13)," "),CHAR(10),""),999),"'","''"))&"""},"