Microsoft SQL Server - Modify an Excel for import
by
Jeremy Canfield |
Updated: March 17 2020
| Microsoft SQL Server articles
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.
| Number | Text1 | Text2 | Text3 | Text4 | Text5 |
| 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:
- Number
- Text1
- Text2
- Combined
To start, add a column to the left of the row, and write the beginning of the insert statement.
| Number | Text1 | Text2 | Text3 | Text4 | Text5 | |
| 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.
=SUBSTITUTE(SUBSTITUTE(B1,CHAR(13),""),CHAR(10),"")
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"), "',")
Did you find this article helpful?
If so, consider buying me a coffee over at 