Scripting Data From Excel

You have a few columns in Excel you would like to import into a SQL Server table as a one off job. You don’t have Powerpivot installed, which would be ideal to import custom reference data into a repository where is can be joined to other data.

By creating a calculated column, then entering the following formula (using starter cells A4, B4, C4, D4, E4, F4 as an example), you will have the DML to perform an insert into a SQL table called TableName. Cells, A,C,D are text, B is a date format but will need to be formatted as text and therefore bound by single quotes whilst E-F are currency hence do not have the single quotes.


=CONCATENATE("INSERT dbo.TableName VALUES ('",A4,"',","'",TEXT(B4,"dd/mm/yyyy"),"',","'",C4,"',",E4,",",F4,",'",D4,"'",")")

Note the expression used to format the date column i.e.

TEXT(B4,"dd/mm/yyyy")

Now to copy the formula to remaining cells, copy the cell where the new formula was just created, right click on remaining cells, paste special and formulas only.   You can then copy the values in all calculated cells to management studio where  the code can be executed as SQL Server DML statements (looking something like the following) ..

INSERT dbo.TableName VALUES ('String1','String2','String3',0.2)
INSERT dbo.TableName VALUES ('String1','String2','String3',0.25)
INSERT dbo.TableName VALUES ('String1','String2','String3',0.4)
INSERT dbo.TableName VALUES ('String1','String2','String3',0.3)

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s