How to modify an Excel for a SQL import

Home > Search > How-to
  by

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"), "',")

 



Add a Comment




We will never share your name or email with anyone. Enter your email if you would like to be notified when we respond to your comment.




Please enter in the box below so that we can be sure you are a human.




Comments