Help with pulling data into SQL  
Author Message
Odiel





PostPosted: Tue Nov 16 17:21:51 CST 2004 Top

SQL Server Developer >> Help with pulling data into SQL

Each sales rep in the company keeps their sales totals in a separate
workbook. I have a summary woorkbook that pulls the data from each of
sales workbooks so the manager can look over the data.

I want to be able to pull the information from this summary sheet into
an SQL database. Using SQL I can pull the info with out a problem,
but the data is not updated. I have to first open the Summary
workbook, update the links, then close the workbook and save it.

What I want to do is be able to automate this procedure so I dont have
to open the workbook up before I pull the info everytime.

Thanks all,

Steve Monczka

SQL Server79  
 
 
Anith





PostPosted: Tue Nov 16 17:21:51 CST 2004 Top

SQL Server Developer >> Help with pulling data into SQL How are you trying to extract the data to SQL? Are you using DTS or any
comparable utilities?

With DTS you can create a local package which can read the source file like
an Excel spreadsheet & import the data into an SQL table.

--
Anith


 
 
smonczka





PostPosted: Fri Nov 19 13:04:51 CST 2004 Top

SQL Server Developer >> Help with pulling data into SQL Anith, I am using DTS to do the transfermation but the problem is that
I have to manualy open the Excel file, update the links in the file
and then save it for the DTS to pull in the updated data. Otherwize
it just brings in the data from last time the Excel file was opened
and saved.

I dont want to have to manualy open up the Excel file and save the
data every time I do the transformation. Any ideas?

Steve




> How are you trying to extract the data to SQL? Are you using DTS or any
> comparable utilities?
>
> With DTS you can create a local package which can read the source file like
> an Excel spreadsheet & import the data into an SQL table.
 
 
Anith





PostPosted: Fri Nov 19 13:39:27 CST 2004 Top

SQL Server Developer >> Help with pulling data into SQL Um.. I don't think you can do this from within the DTS interface since the
changes are external. But I could be wrong here, so let me suggest you check
the following site to see if you can find some info: www.sqldts.com

Also, consider posting this in public.sqlserver.dts newsgroup. There are
some knowledgeable regulars who may be able to assist you.

--
Anith


 
 
James





PostPosted: Fri Nov 19 14:31:55 CST 2004 Top

SQL Server Developer >> Help with pulling data into SQL


> I dont want to have to manualy open up the Excel file and save the
> data every time I do the transformation. Any ideas?
>
> Steve

Maybe connect to the source excel documents instead of the compiled excel
document.

Regards,
Jim


 
 
smonczka





PostPosted: Sat Nov 20 11:13:36 CST 2004 Top

SQL Server Developer >> Help with pulling data into SQL James thanks, but there are 143 of them. : )






>
> > I dont want to have to manualy open up the Excel file and save the
> > data every time I do the transformation. Any ideas?
> >
> > Steve
>
> Maybe connect to the source excel documents instead of the compiled excel
> document.
>
> Regards,
> Jim
 
 
James





PostPosted: Mon Nov 22 11:03:05 CST 2004 Top

SQL Server Developer >> Help with pulling data into SQL Ok. Maybe an ActiveX task at the beginning of the DTS Package that does
something like
'---BEGIN Untested
'Create the Excel object
Set objExcel = CreateObject("Excel.Application")

'Open the worksheet the 3 says to update external(1) and remote(2)
references
objExcel.Workbooks.Open(MYFILE,3)

'Save the update workbook.
objExcel.ActiveWorkbook.Save
'---END

Regards,
Jim



> James thanks, but there are 143 of them. : )
>
>
>




> >
> > > I dont want to have to manualy open up the Excel file and save the
> > > data every time I do the transformation. Any ideas?
> > >
> > > Steve
> >
> > Maybe connect to the source excel documents instead of the compiled
excel
> > document.
> >
> > Regards,
> > Jim