Follow these steps to modify an Excel for a SQL import. In this example, let's say there is an Excel file with the following data.
|1234||Plain Text||Text with "apostrophes" in the string||combine1||combine2||combine3|
Let's say we want to import this data into a SQL table with the following columns:
To start, add a column to the left of the row, and write the beginning of the insert statement.
|insert into tablename (number, text1, text2, combined) values (||1234||Plain Text||Text with "apostrophes" in the string||combine1||combine2||combine3|
Some cells will have a carriage returns, which causes double quotes to appear around the text when copying the data from Excel to SQL. Use this formula on each cell to remove the carriage returns.
Use this to replace double quotes with single quotes.
=SUBSTITUTE(B1, "'", "''")
Use the =CONCATENATE function to place single quotes and a comma around the data.
=CONCATENATE(" '", B1, "',")
Use this form of CONCATENATE for date columns.
=CONCATENATE(" '",TEXT(B1,"MM/DD/YYYY"), "',")