Showing posts with label excel. Show all posts
Showing posts with label excel. Show all posts

Wednesday, March 7, 2012

Managing Multiple Excel incoming files?

Managing Multiple Excel incoming files?
I have this situation. I have a client which have several locations they
work primary with excel forms and they are thinking in doing reports with
them. What they want is to import those Excel files into SQL Server for
later using crystal reports or maybe asp.net for reporting. Those files
will come every week from diferent locations then go to SQL Server and then
they should be stored in a folder in the server for backup. So this means
that i have to deal with the importing but also with the file naming. This
is because they want the reports to be date based. So i have to ask them to
include the date as part of the file name or changing the file name after
procesing the file if I want to store them all in the same folder after
procesing.
So the real question is how do I deal with multiple file in regular basis
bye changing the name of the file or enforcing they send me the file with
the date already included. If the recomendation is the second one how can i
tell the system to change the files after the procesing?
Is there any easy way or tool to achive this? is there any way i can
configure DTS to do that?
EliezerThe easiest way would probably be to use a script task in DTS and us the
File System Object to manipulate the files. It can be done from TSQL but its
pretty kludgy. Using FSO from DTS will allow you a lot more flexibility and
control.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Eliezer Figueroa" <efigueroa@.propoint.com.do> wrote in message
news:Xns9479AA48BF55Aefigueroapropointco
m@.216.196.97.132...
quote:

> Managing Multiple Excel incoming files?
> I have this situation. I have a client which have several locations they
> work primary with excel forms and they are thinking in doing reports with
> them. What they want is to import those Excel files into SQL Server for
> later using crystal reports or maybe asp.net for reporting. Those files
> will come every week from diferent locations then go to SQL Server and

then
quote:

> they should be stored in a folder in the server for backup. So this means
> that i have to deal with the importing but also with the file naming. This
> is because they want the reports to be date based. So i have to ask them

to
quote:

> include the date as part of the file name or changing the file name after
> procesing the file if I want to store them all in the same folder after
> procesing.
> So the real question is how do I deal with multiple file in regular basis
> bye changing the name of the file or enforcing they send me the file with
> the date already included. If the recomendation is the second one how can

i
quote:

> tell the system to change the files after the procesing?
> Is there any easy way or tool to achive this? is there any way i can
> configure DTS to do that?
>
> Eliezer

Managing Multiple Excel incoming files?

Managing Multiple Excel incoming files?
I have this situation. I have a client which have several locations they
work primary with excel forms and they are thinking in doing reports with
them. What they want is to import those Excel files into SQL Server for
later using crystal reports or maybe asp.net for reporting. Those files
will come every week from diferent locations then go to SQL Server and then
they should be stored in a folder in the server for backup. So this means
that i have to deal with the importing but also with the file naming. This
is because they want the reports to be date based. So i have to ask them to
include the date as part of the file name or changing the file name after
procesing the file if I want to store them all in the same folder after
procesing.
So the real question is how do I deal with multiple file in regular basis
bye changing the name of the file or enforcing they send me the file with
the date already included. If the recomendation is the second one how can i
tell the system to change the files after the procesing?
Is there any easy way or tool to achive this? is there any way i can
configure DTS to do that?
EliezerThe easiest way would probably be to use a script task in DTS and us the
File System Object to manipulate the files. It can be done from TSQL but its
pretty kludgy. Using FSO from DTS will allow you a lot more flexibility and
control.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Eliezer Figueroa" <efigueroa@.propoint.com.do> wrote in message
news:Xns9479AA48BF55Aefigueroapropointcom@.216.196.97.132...
> Managing Multiple Excel incoming files?
> I have this situation. I have a client which have several locations they
> work primary with excel forms and they are thinking in doing reports with
> them. What they want is to import those Excel files into SQL Server for
> later using crystal reports or maybe asp.net for reporting. Those files
> will come every week from diferent locations then go to SQL Server and
then
> they should be stored in a folder in the server for backup. So this means
> that i have to deal with the importing but also with the file naming. This
> is because they want the reports to be date based. So i have to ask them
to
> include the date as part of the file name or changing the file name after
> procesing the file if I want to store them all in the same folder after
> procesing.
> So the real question is how do I deal with multiple file in regular basis
> bye changing the name of the file or enforcing they send me the file with
> the date already included. If the recomendation is the second one how can
i
> tell the system to change the files after the procesing?
> Is there any easy way or tool to achive this? is there any way i can
> configure DTS to do that?
>
> Eliezer

Managing Excel execution through DTS

We have a convoluted DTS package. The package is stored and scheduled to run on database server1. Within the package we:
Make a connection to database server2
Execute a Process Task to execute EXCEL.EXE that is installed on server3
Pass parameters through the Process Task direct EXCEL.EXE to open a .xls file on server4
When the Excel file opens, an auto_exec macro in the Excel file being opened imports a text file local to server4 AND directs Excel to save it with another name on server4.

The questions are:
1. Excel is not installed on server1 so how do we direct Excel to execute on server3 rather than server1 where the DTS package is being executed?

2. And how do we control the security context that executes Excel through this use of automation?

3. Other than potential CPU competition, are there any significant issues with having Excel installed on a dedicated database server?Yikes! That is convoluted. Is there any particular reason you are doing things this way?|||Dude -

Are you into masochism? Enjoy sleepless nights?

Seriously - just pull the excel spreadsheet from the server you are reading from, open it on the machine with the dts package, do the manipulation & then copy the final result to wherever its going....

Dont make it any more complicated than it has to be - K.I.S.S.

Simple = Robust.

Cheers,|||Why do you have 4 servers in this scenario when you just want to modify some data on another server than where the DTS-package is situated?

What kind of data does this file contain, and why does it have to be updated via Excel?