By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,213 Members | 2,172 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,213 IT Pros & Developers. It's quick & easy.

Import cell data from XLS into SQL table

P: n/a
I'm trying to use DTS to import data from an XLS into a SQL table.

It works fine in that it INSERT's the data. However, I need it to
UPDATE the table, based upon a ProjectID. Can this be done?

Can a DTS package be fired from a SP using parameters?
Eg UPDATE tProjects SET MyField1=XLS.Sheet1.CellA1,
MyField2=XLS.Sheet2.CellA1 WHERE ProjectID = @ProjectID.

Also, it must handle dynamic XLS file names, eg 981-Budget.xls,
513-Budget.xls, xyz-Budget.xls

Is this the best way to go? Other suggestions most welcome?

Thanks everyone in advance!

Oct 6 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

I would do the querying once and put the data in a temporary table:

<SQLCode>
DECLARE @Folder varchar(200)
DECLARE @Filename varchar(200)
DECLARE @Workbook varchar(200)

DECLARE @Sqlstring varchar(4000)

SET @FileName = 'SomeSheet.xls'
SET @Folder = 'C:\SomeFolder\'
Set @Workbook = 'SomeWorkbook'

SET @SQLString = 'SELECT * FROM OpenDataSource(
''Microsoft.Jet.OLEDB.4.0'',''Data Source="' + @Folder + @FileName +
'";User ID=Admin;Password=;Extended properties=Excel 5.0'')...' +
@Workbook

Create Table #SomeTable
(
<YourTableDefinitionhere>
)
INSERt INTO #SomeTable
EXEC(@SQLString)
</Do anything with the data>

</SQLCode>
HTH, Jens Suessmeyer.

Oct 6 '05 #2

P: n/a
Sorry, you also has another question:

"Can a DTS package be fired from a SP using parameters? "

Yes, you have to run the DTSRUn in a cmdshell with XP_cmdshell and hand
over the paramters to global paramerters defined in the DTS package.
(consider the /A switch and look in the BOL for more syntax
information)

HTH, Jens Suessmeyer.

Oct 6 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.