Updating MSSQL table with info from Excel Sheet

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Updating MSSQL table with info from Excel Sheet

Author

Topic

rumblepup
Starting Member

14 Posts

Posted - 2007-05-11 : 15:17:06

MSSQL noob here. Sorry guys, but I really need some help.

I have a MSSQL (2000) database and a Products Table. Now, there was three columns used for pricing that I was not able to populate on the initial usage. So I exported the table as an Excel document, and got rid of everything except the UID (unique identifier column) the Name, and the Three columns, so that the customer could take their time in filling out the information.

Ok, so These are what are common to both the MSSQL database and the Excel sheet

column uid = unique id integer
column Name = text
column eBay = number
column PriceGrabber = number
column Amazon = number

So, any idea how I can update my Products Table with this?

Please HELP!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-11 : 23:45:52

See if you find answer here
http://sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

rumblepup
Starting Member

14 Posts

Posted - 2007-05-15 : 14:26:28

Madhivanan,

THANK YOU FOR THAT WONDERFUL POST!

Not only has it got me in the right direction, but I see a whole slew of stored procs coming out of this as well.

However, the thread is very long and I need to refine the query a bit.

From what I can tell, I need to begin the function like this -

quote:

Insert into tbl.name select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=c:\\locationfolder\doc.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

However, how do I make sure that it compares the two UID columns to make sure SQL is updating the right information?

What I don't want is the info from say Row 125 that has a column UID of 6 to appear in the SQL table under UID 165.

So, just to clarify. I have to run this proc 3 times. One for each one of the columns I have to import. I have to make sure that the information in the Excel doc, let's say Row 125, which is UID 6, updates to the SQL table UID 6.

So it should look something like this:

Insert into tbl.name select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=c:\\locationfolder\doc.xls;HDR=YES',
'SELECT [eBay] FROM [Products]' where [UID] = UID)

hmmmm.

Am I even close?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-15 : 23:17:08

Do you want to add new data to table or update table with data from EXCEL?

Madhivanan

Failing to plan is Planning to fail

rumblepup
Starting Member

14 Posts

Posted - 2007-05-16 : 01:03:06

I'm sorry, but it's a little difficult to answer because I don't have the sql vernacular.

I want to add the missing information from the three columsn in my excel document to the sql database table.

Just to clarify my original post.

I have a MSSQL table called Products, that at the time of populating the table with data, we did not have 3 sets of special pricing that fall into a column named eBay, a column named Pricegrabber, and a column named Amazon.

So what I did was export the table to Excel, so that someone could work on it while I worked on the rest of the project. I elimanet all of the columns EXCEPT for

uid - which is each products Unique Identifier
Name - Product name
eBay -
Pricegrabber -
Amazon -

So now that the Excel doc has those pricings, I have to update the table with that data. However, I have to be absolutely sure that the right correct pricing will update to the correct Product.

So I thought that a SELECT statement would be best. Logic being
SELECT
the eBay Price
from
Excel document
INSERT
to SQL table Products
WHERE
the UID from the Excel doc
EQUALS
the UID from the SQL table

Is that understandable, or did I just gum up the works?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-16 : 10:34:05

Try this

select columns into #temp FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=c:\\locationfolder\doc.xls;HDR=YES',
'SELECT [eBay] FROM [Products]')

Insert into tablename(columns) T
Selectr columns from #temp
where exists
(select * from #temp where id=T.id)

Drop table #temp

Madhivanan

Failing to plan is Planning to fail

rumblepup
Starting Member

14 Posts

Posted - 2007-05-16 : 10:55:11

That looks awesome. However I'm getting this message in SQL Query Analyzer.

Line 4: Incorrect syntax near 'T'.

Any help?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-16 : 11:03:09

That should be

Insert into tablename(columns)
Selectr columns from #temp T
where exists
(select * from tablename where id=T.id)

Madhivanan

Failing to plan is Planning to fail

rumblepup
Starting Member

14 Posts

Posted - 2007-05-16 : 11:10:25

That worked. I imagine it's best to do this per column so I can check as I go. Here is what the query looks like

select eBay into #temp FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=M:\Data\banler-products.xls;HDR=YES',
'SELECT [eBay] FROM [Products_Update$]')

Insert into dbo.Products(eBay)
Select eBay from #temp T
where exists
(select * from #temp where id=T.id)

Drop table #temp

But, I did get this

Server: Msg 207, Level 16, State 3, Line 4
Invalid column name 'id'.
Server: Msg 207, Level 16, State 1, Line 4
Invalid column name 'id'.

rumblepup
Starting Member

14 Posts

Posted - 2007-05-16 : 11:13:14

Did I mention that the Unique Identifier column is called uid?

so should it look like

Insert into dbo.Products(eBay)
Select eBay from #temp T
where exists
(select * from #temp where uid=T.uid)

Drop table #temp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-16 : 11:15:35

So, is this working?

Insert into dbo.Products(eBay)
Select eBay from #temp T
where exists
(select * from #temp where uid=T.uid)

Madhivanan

Failing to plan is Planning to fail

rumblepup
Starting Member

14 Posts

Posted - 2007-05-16 : 11:20:16

nope. Got this

Server: Msg 207, Level 16, State 3, Line 4
Invalid column name 'uid'.
Server: Msg 207, Level 16, State 1, Line 4
Invalid column name 'uid'.

But that IS the column name

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-16 : 11:30:42

Does your EXCEL file has uid column?

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-05-16 : 11:38:30

Sorry if I'm stating the obvious but make a copy of the table before you try anything !

rumblepup
Starting Member

14 Posts

Posted - 2007-05-16 : 11:39:49

Yes it does.

I broke up the query to see if it will do the first part.

Then I noticed that the uid column was not being imported, so I wrote up this

select uid,eBay into ProductTemp FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=M:\Data\banler-products.xls;HDR=YES',
'SELECT [uid],[eBay] FROM [Products_Update$]')

And I checked, and it all there.

So, my next step is to rewrite this to import over all of the columns I need and have a ProductTemp table with the data I need, then I'll work with the second part.

But, as a test I ran the second part, and even though SQL Query Analyzer 431 row(s) affected, the info was not imported, those columns where left blank.

I went back to the ProductTemp table I created and in Design View, noticed that both the uid and eBay column where designated float, when in the original table, uid is an int and eBay is an nvarchar.

Do you think that is why the data is not migrating over from the ProductTemp table? I think it's better not to drop the table until I'm sure everything is cool.

rumblepup
Starting Member

14 Posts

Posted - 2007-05-16 : 12:02:24

I'm also noticing that pricing info, let's say 72.00, does not update correctly. Instead of going in as 72.00, it goes in at 72, not even the decimal.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-17 : 00:29:29

Before importing data to table format the cells in EXCEL to int and text so that when imported they will be resumed as such

Madhivanan

Failing to plan is Planning to fail

rumblepup
Starting Member

14 Posts

Posted - 2007-05-17 : 00:57:27

Well, even though I was able to create a tempTable to import all of this information, my project time got way to short, so I had to do a major hack.

Dirty hack, but it works.

I exported the SQL table to an excel sheet in the same doc, and just copied and pasted the rows into the corresponding columns. It could have been worse. Only 431 lines.

So then, when experimenting with a backup of the database, I would delete the products table, then do an import from the excel doc. Make the name of the sheet the new table and...it didn't work. That float problem again, even though I would use transforms.

So, I just finally imported the sheet into an access database as a table, checked all the columns for data types, then imported from MSSQL the access table.

I told you, real ugly hack.

But it worked like a charm. Yeap. The project works fine, and there is data consistency. Pretty much had to do it by hand, but this three step process wasn't that bad.

Wish I could have gotten your method to work. I'll keep trying it though.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-17 : 01:17:31

Well. Glad to know finally you sorted out the problem. All the best for your project

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources