473,324 Members | 2,178 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Import cell data from XLS into SQL table

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
2 9639

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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Richard Holliingsworth | last post by:
Hello: Thanks for your quick response. I'm trying to import a new Excel file into an A2K table and it's truncating the data. One of the Excel columns is a text field that can be up to 2000...
4
by: Steve Jorgensen | last post by:
I'm restarting this thread with a different focus. The project I'm working on now id coming along and will be made to work, and it's too late to start over with a new strategy. Still, I'm not...
5
by: Colleyville Alan | last post by:
I need to import a spreadsheet into an Access table. The spreadsheet has performance for mutual funds for various periods. The problem is that if there is no info for a particular period, the...
1
by: Thanks | last post by:
I have a routine that is called on Page_Init. It retrieves folder records from a database which I display as Link Buttons in a table cell. I set the table cell's bgcolor to a default color (say...
10
by: Niklas | last post by:
Hi Before I start coding I need to be sure that nobody else has not done it yet and that I can use it. I need an import utility which import data from Excel to a database or some object in...
8
by: J-P-W | last post by:
Hi, anyone got any thoughts on this problem? I have sales reps. that remotely send their data to an ftp server. The office downloads all files, the code creates an empty file, then downloads the...
1
by: The_Fakir_42 | last post by:
Hi, I have a very tricky problem... I import an XLS file from my asp web application; this xls file contains lots of single-cell named areas. Usually, I know how to get them, this way : '...
6
by: Romulo NF | last post by:
Greetings again to everyone, Im back to show this grid componenet i´ve developed. With this grid you can show the data like a normal table, remove the rows that you need, add rows, import data,...
7
by: TG | last post by:
hi! I am trying to create a sql server table from an excel sheet. Here is the code I have: 'This procedure the xlsx file and dumps it to a table in SQL Server
2
by: TG | last post by:
Hi! I am trying to export only the visible columns from a datagridview in my windows form in VB 2008. Should't it be no comma after the first row where the headers are? Also should...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.