Import .xls file into Exisiting SQL Table

[chrisppppp]

Sql Server 2005

Hi, i've got an .xls file with about 500 lines in it and only 3 columns. What i would like to do is match this file to the table Account by the AccountID in the .xls file, and then update the ExtraInfo 1 and ExtraInfo30 with the two columns i have in my excel file.

I've looked at the Import/Export wizard but i can't seem to get it to do what i want it to. Does anyone have a query that i could run that would update these fields? Sorry my SQL knowledge is a bit limited.

Cheers

 

[simian336]

An easy stratagy since it is in Excel is just use Excel to create Update statements out of your date and then copy and paste that in to query window.

So if your data is 5, bla, bla2

You can use empty columns and rearrange columns to add the sql like this...

Column A= update account set ExtraInfo1='
Column B= bla
Column C= '', ExtraInfo30='
Column D= bla
Column E= '' where AccountID =
Column F= 5

Once you copy and paste you may have to replace some space or tabs.

Simi

 

[chrisppppp]

Well what i've been thinking of doing is just inserting the data into a brand new table. And then updating from the updated table using an sql script. I've never used the update feature before, so how would i update ExtraInfo 1 & 30 making sure i match the accountID's from both tables?

 

[simian336]

Columns C and E start with 2 single quotes...

Simi

 

[simian336]

Yes.. Create your new Table. Import the data if you can. If not, use the same stratagy as above but use insert statements. Then just use an Update statement with the joined table.

But the first strategy would be the easiest.

Simi

 

[gmmastros]

In cases like this, it may be best to use the OPENROWSET function in SQL. It's general form looks like this....

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Folder\ExcelFile.xls', [SheetName$])

You can use this to import the excel data in to a temp table, or simply use it as is. Either way, it should let you get the data in to SQL.

To update data in one table based on a join, you should use the form:

Update TableToUpdate
Set    TableToUpdate.Column1 = TableUpdatingFrom.Column1,
       TableToUpdate.Column2 = TableUpdatingFrom.Column2
From   TableToUpdate
       Inner Join TableUpdatingFrom
         On TableToUpdate.KeyColumn = TableUpdatingFrom.KeyColumn

What I would do is build this in stages. First, create a temp table to store the data from Excel, then use the temp table to update your real table. This allows you to debug as you go.

Create Table #Temp(AccountId Int, ExtraInfo1 VarChar(1000), ExtraInfo2 VarChar(1000))

Insert Into #Temp(AccountId, ExtraInfo1, ExtraInfo2)
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Folder\ExcelFile.xls', [SheetName$])

Update Account
Set    Account.ExtraInfo1  = #Temp.Column1,
       Account.ExtraInfo30 = #Temp.Column2
From   Account
       Inner Join #Temp
         On Account.AccountId= #Temp.AccountId

There is a potential gotcha when working with external files in SQL Server. When you use things like "C:\Folder\File.xls", it's important to realize that the C drive is the server's C drive. You can use UNC paths like "\\Anycomputer\ShareName\Folder", but there's a potential problem here too. SQL Server runs as a service. By default, the service operates under the "local system" account, which usually does NOT have permissions to other computers. You can change the log in account used to start the service, but you need to be careful here. If you need to do this, best practice is to create a new windows account (active directory) just for this purpose. Make sure you give it a really string password, and make sure the password does not expire.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[chrisppppp]

gmmastros what you've suggested i've tested on about 10 rows and it seems to have worked fine, so thanks for your help and i'll give it a go on the rest of them. Cheers