Populate SQL Rows from Excel Columns

Question

Populate SQL Rows from Excel Columns

Philip Davis

Old Hand

Points: 307
More actions
June 20, 2006 at 5:42 am

#190960

I have a SQL Table with the following fields:
Product, Price_List, Price
I need to import data into this from a price list held as an Excel spreadsheet which has the Product in Column A, the name of the Price_List in Row 1, and the Price (or x to say exclude it) in the table body:
Row1: Product List1 List99 List42
Row2: Prod1 1.45 x 2.25
This should create 2 rows in the table:
Prod1, List1, 1.45
Prod1, List42, 2.25
I know I could easily do this with VB in Excel, except that I have 2000 rows and over 100 columns. I can import the spreadsheet into a temporary table via DTS, but how do I then automatically populate the main SQL table?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ted Bouskill-339394 SSC Veteran Points: 201 More actions · Answer 1

If you are comfortable using VB then DTS is the choice for you. You can execute an ActiveX transformation during the import process. Essentially you can execute custom VB Script logic as each row is retrieved from the source before inserting it into the target. You don't even need a temporary table.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Or, if this is a one-off, you can use the Pivot Table feature in Excel to get your worksheet into the same structure as your table.

John

RyanRandall SSChampion Points: 13623 More actions · Answer 3

If you do prefer to use sql to do you transformation, you can do something like this...

--data (create using dts, bcp or bulk insert)

declare @StagingTable table (Product varchar(10), List1 varchar(10), List99 varchar(10), List42 varchar(10))

insert @StagingTable

select 'Prod1', '1.45', 'x', '2.25'

union all select 'Prod2', 'x', '12.34', '567.89'

--calculation

declare @PermanentTable table (Product varchar(10), ColumnName varchar(10), Value money)

insert @PermanentTable

select Product, ColumnName, cast(Value as money) from (

select Product, 'List1' as ColumnName, List1 as Value from @StagingTable

union all select Product, 'List99', List99 from @StagingTable

union all select Product, 'List42', List42 from @StagingTable) a

where not Value = 'x'

select * from @PermanentTable order by Product, ColumnName

/*results

Product ColumnName Value

---------- ---------- ---------------------

Prod1 List1 1.4500

Prod1 List42 2.2500

Prod2 List42 567.8900

Prod2 List99 12.3400

*/

I don't have a particular preference for any of these 3 methods, but I think it's good to know them all so you can chose the most appropriate one for a given situation.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Philip Davis Old Hand Points: 307 More actions · Answer 4

Ryan:

Many thanks for this, but it seems to require the values to be manually included in the code. The whole point is to avoid this otherwise I could have manually type the Insert statements in Query Analyzer. As I have over 100 columns and 2000 rows, I need the process to automatically calculate and produce the 200,000 Inserts.

John:

Again, the quantity of the data is the issue. I often use pivot tables, but to me they are more akin to what I am starting with. If I were to try to pivot and re-organise it to produce the simple three column result I require (Product, Price_List_Name, Price), I would exceed Excel's limits.

Ted:

'Comfortable with VB'? To be honest, I have played with a few Excel macro's but not free-standing VB. What is Active X and how does it relate, or how do I code VB within a transform? It does sound as if there may be an answer in here somewhere, but I can't quite follow it through.

RyanRandall SSChampion Points: 13623 More actions · Answer 5

Philip - I'm confused!

You said "I can import the spreadsheet into a temporary table via DTS". The 'data' section I wrote was just my test data to illustrate the method to use after you've imported it. To get it to that point, you can use dts (as you said you could), bcp or bulk insert.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Ted Bouskill-339394 SSC Veteran Points: 201 More actions · Answer 6

Philip:

An 'ActiveX' transformation allows you to write Visual Basic code that can execute the calculations for you on the fly.

Search the web for examples of DTS packages using ActiveX. I am sure you can find something similar as an example.

Cheers

Philip Davis Old Hand Points: 307 More actions · Answer 7

I have only ever used the DTS Import wizard before and simply let it dump the spreadsheet in as a table. I see that there is a 'transform' button in there. Are you saying that I could put something in there which (in terms of logic) is the equivalent of:

for a = 2 to activesheet.rows

for b = 2 to activesheet.columns

product = r(a)c1.value

price_list_name = r1c(b).value

price = r(a)c(b).value

if price not = 'x' then

(insert product, price_list_name, price)

endif

next b

next a

Or am I thinking along completely the wrong lines?

Ted Bouskill-339394 SSC Veteran Points: 201 More actions · Answer 8

Hi Philip

You are getting warmer!

Yes that is along the right lines, but the transformation will actually do the 'for' loops for you. Basically there is a data pump that will feed you one source row at a time. Your transformation logic will then have access to all of the columns on that row so that you can create a target row for the new table using computations on the values in the source row.

Cheers