downloading data to excel

Hi,

I want to download data to excel in which each filename varies depending on the day i download them.

Ex.

ExportFile_09272007.xls

Can this be possible?

cherriesh

You need a variable to build the name of the Excel file.

In a script use this variable to create the excel file.

Then create your excel connection to an existing file and in the expressions of this connection assign the value to the ExcelFilePath & ServerName.

I hope it helps!

|||

hi,

what i did, in the expressions property (connectionstring) of the excel connection manager, i put this path:

"C:\\Cube Reports\\Excel\\" + @.[System:ackageName] + "Logs" +
(DT_STR,4,1252)DATEPART( "yyyy" , @.[System:tartTime] ) +
RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @.[System:tartTime] ), 2) +
RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @.[System:tartTime] ), 2) +
RIGHT("0" + (DT_STR,4,1252)DATEPART( "hh" , @.[System:tartTime] ), 2) +
RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" , @.[System:tartTime] ), 2) +
RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" , @.[System:tartTime] ), 2) +
".xls"

when i tried running the report, it gives me this error:

Nonfatal errors occurred while saving the package:

Error at ExportToExcel [connection manager "excel connection manager"]: the connection string formal is not valid. it must consist of one or more components of the form x=y, separated by semicolons. this error occurs when a connection string with zero components is set on database connection manager.

cherriesh

|||You have to set the ExcelFilePath not the Connection string property!|||

hi,

so here's what i've done, in the expression i set the excelfile path to "C:\\Cube Reports\\Excel"

I set the server name to "localhost"

what's the variable you mentioned? I don't have any script object. I only have oledb source, then excel destination.

can you detail the solution... thanks a lot!!!

cherriesh

|||

The “excel destination” assumes that the Excel file exists. So you have to create your file, you can accomplish this with a “Script Task”. While creating the Excel file you have to provide the file name, this is the same that you need for the ExcelFilePath, so you can save it in a variable.

If you do it like that, you may have another code for getting the file name how ever I think your expression should return

“C:\Cube Reports\Excel\TestLogs20070927145711.xls”. I got that.

sql

downloading data from a table

I need to download all data in a table into a text file or an excel spreadsheet. What would be the easiest way to do this?
Hi,
Please see the reply to the same post in microsoft.public.sqlserver.server
group
Thanks
Hari
"Joel" <anonymous@.discussions.microsoft.com> wrote in message
news:4E7B6116-7F34-47B0-B8C7-055E2DCF1560@.microsoft.com...
> I need to download all data in a table into a text file or an excel
spreadsheet. What would be the easiest way to do this?
>

Download the SQL servere date to excel file using ASP.net

Hi EveryOne

I would like to download the SQL server databse to excel file using ASP.Net

I need to download the one table to excel file. If anyone knows please help me.

With Regards

Nibu Abraham

hi

i found this link

http://www.theserverside.net/discussions/thread.tss?thread_id=29385

give a try

|||

Hi Nibu

Create a stored procedure to display the required columns from the give table. Grant execute permission to this stored procedure to the role that your ASP.NET account is assigned to. In your datalayer, run the stored procedure to output a dataset. Add a gridview control (or datagrid if still at ASP.NET 1.1) to an ASPX form.

Set the datasource for the control to the dataset produced by the read function in your data layer, call databind and the data will be displayed.

There is a simple modifier to make the displayed page OLE load Excel - I will find out the how of this at the weekend.

HTH

|||

You need to add the following to your Page_Load

Response.ContentType = "application/vnd.ms-excel";

Response.Charset = "";

You will need to turn off the sort option on the grid and any other hyperlinks on the form.

HTH

sql

download excel file

I've uploaded a excel file and i want to force the user to download it
and not display it inline. where can i change the MIME settings of the
report manager?
ThanksHello Owen,
I would like to know this issue a little bit clear.
Do you mean when you click the excel file, you could just open it in the IE?
You could force the IE show the Open & Save dialogbox to enable client user
to download the Excel file.
1. Open a windows explorer.
2. In the Tools -> Folder Options -> File Types -> Find the XLS extension.
3. Click the Advanced button. On the Edit File Type dialogbox, please check
the Confirm open after download and uncheck the Browse in same window.
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi
Thanks for the help, but I want to make the report manager only allow
the download of Excel files and not opening of them inline in IE.
Wei Lu [MSFT] wrote:
> Hello Owen,
> I would like to know this issue a little bit clear.
> Do you mean when you click the excel file, you could just open it in the IE?
> You could force the IE show the Open & Save dialogbox to enable client user
> to download the Excel file.
> 1. Open a windows explorer.
> 2. In the Tools -> Folder Options -> File Types -> Find the XLS extension.
> 3. Click the Advanced button. On the Edit File Type dialogbox, please check
> the Confirm open after download and uncheck the Browse in same window.
> Hope this will be helpful.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Owen,
Unfortunately, you could not do this directly in Reporting Manager.
All the request was handled by the reporting services and we could not
modify it directly.
I would like to know why you want to upload a Excel file to the report
server. Since the report server is used to generate the report, we do not
suggest to upload other file expect the report RDL file to the report
server.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi
I want to distribute a Excelsheet with Olap so power users can customize
their reports.
regardes
Wei Lu [MSFT] wrote:
> Hello Owen,
> Unfortunately, you could not do this directly in Reporting Manager.
> All the request was handled by the reporting services and we could not
> modify it directly.
> I would like to know why you want to upload a Excel file to the report
> server. Since the report server is used to generate the report, we do not
> suggest to upload other file expect the report RDL file to the report
> server.
>
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Owen,
Would you please let me know some details of how you make the power user
customize the report?
Do you mean you use the Excel as the datasource?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello
No Excel has a OLAP-Connection and can be used without reporting
services. I want to change the mime-type so the browser thinks it's a
octead-stream that only leaves the option to save the file and not open
it inline.
Wei Lu [MSFT] wrote:
> Hello Owen,
> Would you please let me know some details of how you make the power user
> customize the report?
> Do you mean you use the Excel as the datasource?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Owen,
Unfortunately, you could not change the mime-type in the report manager
directly.
You need to force the client to change their client setting so that they
will download the excel file only instead of open it in the browser.
When the client get the Open & Save dialogbox, uncheck the Always ask
before opening this type of file and then click the Save to Save it to the
local.
Then, next time when you open the excel file in the Report manager, you
will get the file open directly in local.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello
Its not me with the problem of saving instead of opening the xls but the
end-user. i want to eliminante a potential mistake of the end-user.
Thats why i wont to adjust the mime-type of the xls. I dont care if i
have to set it in a db-table or config file. I just need to know where
the setting is.
Thanks
Wei Lu [MSFT] wrote:
> Hello Owen,
> Unfortunately, you could not change the mime-type in the report manager
> directly.
> You need to force the client to change their client setting so that they
> will download the excel file only instead of open it in the browser.
> When the client get the Open & Save dialogbox, uncheck the Always ask
> before opening this type of file and then click the Save to Save it to the
> local.
> Then, next time when you open the excel file in the Report manager, you
> will get the file open directly in local.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Owen,
After consulting the IIS engineer, you could add a MIME type in the IIS
virtual directory.
1. Run "inetmgr" in the start menu.
2. Expand the Web Sites, find the ReportServer virtual directory.
Right-click it and click the Properties.
3. Click the HTTP Headers Tab, then click the MIME Types button and click
New Button.
4. In the Dialogbox, type the .XLS in the entension and octead-stream in
the MIME type section. Click OK.
5. Click OK and then restart the IIS.
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi
Thanks for trying but if it was so easy i would of succeded myself. The
problem is that i cant change the default mime-types. i added the
mime-type you suggested but it didn't ovverride the existing. Have you
got any other suggestion. Sorry for all the work i'm creating with this
ping-pong request.
Thanks,
Wei Lu [MSFT] wrote:
> Hello Owen,
> After consulting the IIS engineer, you could add a MIME type in the IIS
> virtual directory.
> 1. Run "inetmgr" in the start menu.
> 2. Expand the Web Sites, find the ReportServer virtual directory.
> Right-click it and click the Properties.
> 3. Click the HTTP Headers Tab, then click the MIME Types button and click
> New Button.
> 4. In the Dialogbox, type the .XLS in the entension and octead-stream in
> the MIME type section. Click OK.
> 5. Click OK and then restart the IIS.
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Owen,
Would you please test this on other site?
Also, you may try to use the application/octstream for the MIME type.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

download data into text file

I need to download all data in a table into a text file or an excel spreadsheet. What would be the easiest way to do this?
DTS would be the easiest way.
In Enterprise Manager, the shortcut is to right click on the table and choose export data.
|||Hi,
There are 2 options:-
1. DTS (A Graphical interface , you can just by clicking down load the
contents of a table to Text file.
2. BCP OUT (This is command line utility ) ... For more help execute BCP/?
from command prompt.
a. Using query to BCP OUT specific columns
BCP "Select name from dbname..sysusers" QUERYOUT
c:\sysusers.txt -Usa -Ppassword -SServer_name -c
b. BCP OUT entire table
BCP master..sysobjects OUT c:\sysusers.txt -Usa -Ppassword -SServer_name -c
Thanks
Hari
MCDBA
"Ken Dutton" <anonymous@.discussions.microsoft.com> wrote in message
news:00E0DDBA-97BD-4DB0-B582-8D6863E7C6F2@.microsoft.com...
> DTS would be the easiest way.
> In Enterprise Manager, the shortcut is to right click on the table and
choose export data.
|||Hi,
I understand that you want to export the Data in a table to an excel work
sheet or a Text file.
You can achieve this by Import Export Wizard.
In the Enterprise Manager, select the Table from which you want to export
the data .
Right click on that table.
Select All Tasks
Select Export Data
Click on Next - On the Welcome Dialog of DTS Import/Export Wizard
You will get a Choose a Source Dialog. It would by default show the current
database. If that's the database from where you want to export the data,
click on Next
You will get a Choose a Destination Dialog
1. To save the data to Excel (2000 or above) worksheet
a. Select Excel 97 -2 000 in the Destination (from the pull down menu)
b. In the File name option, enter the filename for the output file.
Click on Next
You will see a Specify Table Copy or Query Dialog
Select the "Copy Table(s) and view(s) from the source database
Then Click on NExt
You will get a Select Source Tables and Views Dialog
Select the table from which you want to export the data and then click on
Next.
You will get a Save, Schedule and replicate package dialog.
Select "Run Immediately" option
Click on Next
You will get a Completing the DTS Import/Export Wizard dialog.
Click on Finish
This will successfully create an excel file and populate it with the
records from the selected table.
2. To save the data to Text File
a. Select Text File in the destination (from the pull down menu)
b. in the File Name option ,enter the filename for the output file.
Click on Next
You will see a Specify Table Copy or Query Dialog
Select the "Copy Table(s) and view(s) from the source database
Then Click on NExt
You will get a Select Destination File Format dialog
Select the appropriate format (or leave it to default).
You may want to select the option (First Row has Column Names), to include
the column names in the output
Then Click on NExt
You will get a Save, Schedule and replicate package dialog.
Select "Run Immediately" option
Click on Next
You will get a Completing the DTS Import/Export Wizard dialog.
Click on Finish
This will successfully create an excel file and populate it with the
records from the selected table.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||How about going into EXCEL and DATA>IMPORT EXTERNAL DATA>IMPORT DATA>NEW SQL SERVER CONNECTION.
If the goal is to get SQL data into EXCEL, why use BCP or EM?
|||Yes, that worked very well
Thanks
Joel

download data into text file

I need to download all data in a table into a text file or an excel spreadsh
eet. What would be the easiest way to do this?DTS would be the easiest way.
In Enterprise Manager, the shortcut is to right click on the table and choos
e export data.|||Hi,
There are 2 options:-
1. DTS (A Graphical interface , you can just by clicking down load the
contents of a table to Text file.
2. BCP OUT (This is command line utility ) ... For more help execute BCP/?
from command prompt.
a. Using query to BCP OUT specific columns
BCP "Select name from dbname..sysusers" QUERYOUT
c:\sysusers.txt -Usa -Ppassword -SServer_name -c
b. BCP OUT entire table
BCP master..sysobjects OUT c:\sysusers.txt -Usa -Ppassword -SServer_name -c
Thanks
Hari
MCDBA
"Ken Dutton" <anonymous@.discussions.microsoft.com> wrote in message
news:00E0DDBA-97BD-4DB0-B582-8D6863E7C6F2@.microsoft.com...
> DTS would be the easiest way.
> In Enterprise Manager, the shortcut is to right click on the table and
choose export data.|||Hi,
I understand that you want to export the Data in a table to an excel work
sheet or a Text file.
You can achieve this by Import Export Wizard.
In the Enterprise Manager, select the Table from which you want to export
the data .
Right click on that table.
Select All Tasks
Select Export Data
Click on Next - On the Welcome Dialog of DTS Import/Export Wizard
You will get a Choose a Source Dialog. It would by default show the current
database. If that's the database from where you want to export the data,
click on Next
You will get a Choose a Destination Dialog
1. To save the data to Excel (2000 or above) worksheet
a. Select Excel 97 -2 000 in the Destination (from the pull down menu)
b. In the File name option, enter the filename for the output file.
Click on Next
You will see a Specify Table Copy or Query Dialog
Select the "Copy Table(s) and view(s) from the source database
Then Click on NExt
You will get a Select Source Tables and Views Dialog
Select the table from which you want to export the data and then click on
Next.
You will get a Save, Schedule and replicate package dialog.
Select "Run Immediately" option
Click on Next
You will get a Completing the DTS Import/Export Wizard dialog.
Click on Finish
This will successfully create an excel file and populate it with the
records from the selected table.
2. To save the data to Text File
a. Select Text File in the destination (from the pull down menu)
b. in the File Name option ,enter the filename for the output file.
Click on Next
You will see a Specify Table Copy or Query Dialog
Select the "Copy Table(s) and view(s) from the source database
Then Click on NExt
You will get a Select Destination File Format dialog
Select the appropriate format (or leave it to default).
You may want to select the option (First Row has Column Names), to include
the column names in the output
Then Click on NExt
You will get a Save, Schedule and replicate package dialog.
Select "Run Immediately" option
Click on Next
You will get a Completing the DTS Import/Export Wizard dialog.
Click on Finish
This will successfully create an excel file and populate it with the
records from the selected table.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.|||How about going into EXCEL and DATA>IMPORT EXTERNAL DATA>IMPORT DATA>NEW SQL
SERVER CONNECTION.
If the goal is to get SQL data into EXCEL, why use BCP or EM?|||Yes, that worked very well
Thanks
Joel

download data into text file

I need to download all data in a table into a text file or an excel spreadsheet. What would be the easiest way to do this?DTS would be the easiest way
In Enterprise Manager, the shortcut is to right click on the table and choose export data.|||Hi,
There are 2 options:-
1. DTS (A Graphical interface , you can just by clicking down load the
contents of a table to Text file.
2. BCP OUT (This is command line utility ) ... For more help execute BCP/?
from command prompt.
a. Using query to BCP OUT specific columns
BCP "Select name from dbname..sysusers" QUERYOUT
c:\sysusers.txt -Usa -Ppassword -SServer_name -c
b. BCP OUT entire table
BCP master..sysobjects OUT c:\sysusers.txt -Usa -Ppassword -SServer_name -c
Thanks
Hari
MCDBA
"Ken Dutton" <anonymous@.discussions.microsoft.com> wrote in message
news:00E0DDBA-97BD-4DB0-B582-8D6863E7C6F2@.microsoft.com...
> DTS would be the easiest way.
> In Enterprise Manager, the shortcut is to right click on the table and
choose export data.|||Hi,
I understand that you want to export the Data in a table to an excel work
sheet or a Text file.
You can achieve this by Import Export Wizard.
In the Enterprise Manager, select the Table from which you want to export
the data .
Right click on that table.
Select All Tasks
Select Export Data
Click on Next - On the Welcome Dialog of DTS Import/Export Wizard
You will get a Choose a Source Dialog. It would by default show the current
database. If that's the database from where you want to export the data,
click on Next
You will get a Choose a Destination Dialog
1. To save the data to Excel (2000 or above) worksheet
a. Select Excel 97 -2 000 in the Destination (from the pull down menu)
b. In the File name option, enter the filename for the output file.
Click on Next
You will see a Specify Table Copy or Query Dialog
Select the "Copy Table(s) and view(s) from the source database
Then Click on NExt
You will get a Select Source Tables and Views Dialog
Select the table from which you want to export the data and then click on
Next.
You will get a Save, Schedule and replicate package dialog.
Select "Run Immediately" option
Click on Next
You will get a Completing the DTS Import/Export Wizard dialog.
Click on Finish
This will successfully create an excel file and populate it with the
records from the selected table.
2. To save the data to Text File
a. Select Text File in the destination (from the pull down menu)
b. in the File Name option ,enter the filename for the output file.
Click on Next
You will see a Specify Table Copy or Query Dialog
Select the "Copy Table(s) and view(s) from the source database
Then Click on NExt
You will get a Select Destination File Format dialog
Select the appropriate format (or leave it to default).
You may want to select the option (First Row has Column Names), to include
the column names in the output
Then Click on NExt
You will get a Save, Schedule and replicate package dialog.
Select "Run Immediately" option
Click on Next
You will get a Completing the DTS Import/Export Wizard dialog.
Click on Finish
This will successfully create an excel file and populate it with the
records from the selected table.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

Down to the wire.....

I need to expand off this query. Instead of data being imported from
cr_stating into cr. I need to import data from excel into cr_staging. Can
someone give me a good example please???
UPDATE cr
SET cr.location = crs.location
FROM cr
JOIN cr_staging crs ON cr.first_name = crs.first_name
AND cr.last_name = crs.last_name
AND cr.ssn = crs.ssn
INSERT cr (first_name,
last_name,
ssn,
location)
SELECT crs.first_name,
crs.last_name,
crs.ssn,
crs.location
FROM cr_staging crs
WHERE NOT EXISTS (SELECT *
FROM cr
WHERE cr.first_name = crs.first_name
AND cr.last_name = crs.last_name
AND cr.ssn = crs.ssn)
Thanks...
Eric,
This article should help you: http://support.microsoft.com/?id=321686
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:6C855B64-751D-4137-8B29-1932BE87D673@.microsoft.com...
> I need to expand off this query. Instead of data being imported from
> cr_stating into cr. I need to import data from excel into cr_staging. Can
> someone give me a good example please???
> UPDATE cr
> SET cr.location = crs.location
> FROM cr
> JOIN cr_staging crs ON cr.first_name = crs.first_name
> AND cr.last_name = crs.last_name
> AND cr.ssn = crs.ssn
> INSERT cr (first_name,
> last_name,
> ssn,
> location)
> SELECT crs.first_name,
> crs.last_name,
> crs.ssn,
> crs.location
> FROM cr_staging crs
> WHERE NOT EXISTS (SELECT *
> FROM cr
> WHERE cr.first_name = crs.first_name
> AND cr.last_name = crs.last_name
> AND cr.ssn = crs.ssn)
> Thanks...
>
|||Eric
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:6C855B64-751D-4137-8B29-1932BE87D673@.microsoft.com...
> I need to expand off this query. Instead of data being imported from
> cr_stating into cr. I need to import data from excel into cr_staging. Can
> someone give me a good example please???
> UPDATE cr
> SET cr.location = crs.location
> FROM cr
> JOIN cr_staging crs ON cr.first_name = crs.first_name
> AND cr.last_name = crs.last_name
> AND cr.ssn = crs.ssn
> INSERT cr (first_name,
> last_name,
> ssn,
> location)
> SELECT crs.first_name,
> crs.last_name,
> crs.ssn,
> crs.location
> FROM cr_staging crs
> WHERE NOT EXISTS (SELECT *
> FROM cr
> WHERE cr.first_name = crs.first_name
> AND cr.last_name = crs.last_name
> AND cr.ssn = crs.ssn)
> Thanks...
>
|||URI,
I think you gave me that before but I was getting an error when I used that
query. Will that update and insert records per my requirements?
Thanls
"Uri Dimant" wrote:

> Eric
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="c:\MyExcel.xls";
> User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
>
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:6C855B64-751D-4137-8B29-1932BE87D673@.microsoft.com...
>
>
|||"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:4245938B-E84F-4397-AAD8-1A415CC02BA6@.microsoft.com...
> I think you gave me that before but I was getting an error when I used
that
> query. Will that update and insert records per my requirements?
Eric,
You need to read the article I posted the link to. Read the examples on
how to create a linked server pointing to your spreadsheet, then replace
your 'cr_staging' table in the query with the linked server.
|||Eric
It does a simple SELECT statement from EXCEL file
INSERT INTO Table SELECT statement and the you can update it.
If I missunderstand something let me know.
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:4245938B-E84F-4397-AAD8-1A415CC02BA6@.microsoft.com...
> URI,
> I think you gave me that before but I was getting an error when I used
that[vbcol=seagreen]
> query. Will that update and insert records per my requirements?
> Thanls
> "Uri Dimant" wrote:
Can[vbcol=seagreen]

Down to the wire.....

I need to expand off this query. Instead of data being imported from
cr_stating into cr. I need to import data from excel into cr_staging. Can
someone give me a good example please''?
UPDATE cr
SET cr.location = crs.location
FROM cr
JOIN cr_staging crs ON cr.first_name = crs.first_name
AND cr.last_name = crs.last_name
AND cr.ssn = crs.ssn
INSERT cr (first_name,
last_name,
ssn,
location)
SELECT crs.first_name,
crs.last_name,
crs.ssn,
crs.location
FROM cr_staging crs
WHERE NOT EXISTS (SELECT *
FROM cr
WHERE cr.first_name = crs.first_name
AND cr.last_name = crs.last_name
AND cr.ssn = crs.ssn)
Thanks...Eric,
This article should help you: http://support.microsoft.com/?id=321686
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:6C855B64-751D-4137-8B29-1932BE87D673@.microsoft.com...
> I need to expand off this query. Instead of data being imported from
> cr_stating into cr. I need to import data from excel into cr_staging. Can
> someone give me a good example please''?
> UPDATE cr
> SET cr.location = crs.location
> FROM cr
> JOIN cr_staging crs ON cr.first_name = crs.first_name
> AND cr.last_name = crs.last_name
> AND cr.ssn = crs.ssn
> INSERT cr (first_name,
> last_name,
> ssn,
> location)
> SELECT crs.first_name,
> crs.last_name,
> crs.ssn,
> crs.location
> FROM cr_staging crs
> WHERE NOT EXISTS (SELECT *
> FROM cr
> WHERE cr.first_name = crs.first_name
> AND cr.last_name = crs.last_name
> AND cr.ssn = crs.ssn)
> Thanks...
>|||Eric
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:6C855B64-751D-4137-8B29-1932BE87D673@.microsoft.com...
> I need to expand off this query. Instead of data being imported from
> cr_stating into cr. I need to import data from excel into cr_staging. Can
> someone give me a good example please''?
> UPDATE cr
> SET cr.location = crs.location
> FROM cr
> JOIN cr_staging crs ON cr.first_name = crs.first_name
> AND cr.last_name = crs.last_name
> AND cr.ssn = crs.ssn
> INSERT cr (first_name,
> last_name,
> ssn,
> location)
> SELECT crs.first_name,
> crs.last_name,
> crs.ssn,
> crs.location
> FROM cr_staging crs
> WHERE NOT EXISTS (SELECT *
> FROM cr
> WHERE cr.first_name = crs.first_name
> AND cr.last_name = crs.last_name
> AND cr.ssn = crs.ssn)
> Thanks...
>|||URI,
I think you gave me that before but I was getting an error when I used that
query. Will that update and insert records per my requirements?
Thanls
"Uri Dimant" wrote:

> Eric
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="c:\MyExcel.xls";
> User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
>
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:6C855B64-751D-4137-8B29-1932BE87D673@.microsoft.com...
>
>|||"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:4245938B-E84F-4397-AAD8-1A415CC02BA6@.microsoft.com...
> I think you gave me that before but I was getting an error when I used
that
> query. Will that update and insert records per my requirements?
Eric,
You need to read the article I posted the link to. Read the examples on
how to create a linked server pointing to your spreadsheet, then replace
your 'cr_staging' table in the query with the linked server.|||Eric
It does a simple SELECT statement from EXCEL file
INSERT INTO Table SELECT statement and the you can update it.
If I missunderstand something let me know.
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:4245938B-E84F-4397-AAD8-1A415CC02BA6@.microsoft.com...
> URI,
> I think you gave me that before but I was getting an error when I used
that[vbcol=seagreen]
> query. Will that update and insert records per my requirements?
> Thanls
> "Uri Dimant" wrote:
>
Can[vbcol=seagreen]sql

Down to the wire.....

I need to expand off this query. Instead of data being imported from
cr_stating into cr. I need to import data from excel into cr_staging. Can
someone give me a good example please''?
UPDATE cr
SET cr.location = crs.location
FROM cr
JOIN cr_staging crs ON cr.first_name = crs.first_name
AND cr.last_name = crs.last_name
AND cr.ssn = crs.ssn
INSERT cr (first_name,
last_name,
ssn,
location)
SELECT crs.first_name,
crs.last_name,
crs.ssn,
crs.location
FROM cr_staging crs
WHERE NOT EXISTS (SELECT *
FROM cr
WHERE cr.first_name = crs.first_name
AND cr.last_name = crs.last_name
AND cr.ssn = crs.ssn)
Thanks...Eric,
This article should help you: http://support.microsoft.com/?id=321686
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:6C855B64-751D-4137-8B29-1932BE87D673@.microsoft.com...
> I need to expand off this query. Instead of data being imported from
> cr_stating into cr. I need to import data from excel into cr_staging. Can
> someone give me a good example please''?
> UPDATE cr
> SET cr.location = crs.location
> FROM cr
> JOIN cr_staging crs ON cr.first_name = crs.first_name
> AND cr.last_name = crs.last_name
> AND cr.ssn = crs.ssn
> INSERT cr (first_name,
> last_name,
> ssn,
> location)
> SELECT crs.first_name,
> crs.last_name,
> crs.ssn,
> crs.location
> FROM cr_staging crs
> WHERE NOT EXISTS (SELECT *
> FROM cr
> WHERE cr.first_name = crs.first_name
> AND cr.last_name = crs.last_name
> AND cr.ssn = crs.ssn)
> Thanks...
>|||Eric
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:6C855B64-751D-4137-8B29-1932BE87D673@.microsoft.com...
> I need to expand off this query. Instead of data being imported from
> cr_stating into cr. I need to import data from excel into cr_staging. Can
> someone give me a good example please''?
> UPDATE cr
> SET cr.location = crs.location
> FROM cr
> JOIN cr_staging crs ON cr.first_name = crs.first_name
> AND cr.last_name = crs.last_name
> AND cr.ssn = crs.ssn
> INSERT cr (first_name,
> last_name,
> ssn,
> location)
> SELECT crs.first_name,
> crs.last_name,
> crs.ssn,
> crs.location
> FROM cr_staging crs
> WHERE NOT EXISTS (SELECT *
> FROM cr
> WHERE cr.first_name = crs.first_name
> AND cr.last_name = crs.last_name
> AND cr.ssn = crs.ssn)
> Thanks...
>|||URI,
I think you gave me that before but I was getting an error when I used that
query. Will that update and insert records per my requirements?
Thanls
"Uri Dimant" wrote:
> Eric
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="c:\MyExcel.xls";
> User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
>
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:6C855B64-751D-4137-8B29-1932BE87D673@.microsoft.com...
> > I need to expand off this query. Instead of data being imported from
> > cr_stating into cr. I need to import data from excel into cr_staging. Can
> > someone give me a good example please''?
> >
> > UPDATE cr
> > SET cr.location = crs.location
> > FROM cr
> > JOIN cr_staging crs ON cr.first_name = crs.first_name
> > AND cr.last_name = crs.last_name
> > AND cr.ssn = crs.ssn
> >
> > INSERT cr (first_name,
> > last_name,
> > ssn,
> > location)
> > SELECT crs.first_name,
> > crs.last_name,
> > crs.ssn,
> > crs.location
> > FROM cr_staging crs
> > WHERE NOT EXISTS (SELECT *
> > FROM cr
> > WHERE cr.first_name = crs.first_name
> > AND cr.last_name = crs.last_name
> > AND cr.ssn = crs.ssn)
> >
> > Thanks...
> >
>
>|||Eric
It does a simple SELECT statement from EXCEL file
INSERT INTO Table SELECT statement and the you can update it.
If I missunderstand something let me know.
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:4245938B-E84F-4397-AAD8-1A415CC02BA6@.microsoft.com...
> URI,
> I think you gave me that before but I was getting an error when I used
that
> query. Will that update and insert records per my requirements?
> Thanls
> "Uri Dimant" wrote:
> > Eric
> > SELECT *
> > FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> > 'Data Source="c:\MyExcel.xls";
> > User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
> >
> >
> >
> > "Eric" <Eric@.discussions.microsoft.com> wrote in message
> > news:6C855B64-751D-4137-8B29-1932BE87D673@.microsoft.com...
> > > I need to expand off this query. Instead of data being imported from
> > > cr_stating into cr. I need to import data from excel into cr_staging.
Can
> > > someone give me a good example please''?
> > >
> > > UPDATE cr
> > > SET cr.location = crs.location
> > > FROM cr
> > > JOIN cr_staging crs ON cr.first_name = crs.first_name
> > > AND cr.last_name = crs.last_name
> > > AND cr.ssn = crs.ssn
> > >
> > > INSERT cr (first_name,
> > > last_name,
> > > ssn,
> > > location)
> > > SELECT crs.first_name,
> > > crs.last_name,
> > > crs.ssn,
> > > crs.location
> > > FROM cr_staging crs
> > > WHERE NOT EXISTS (SELECT *
> > > FROM cr
> > > WHERE cr.first_name = crs.first_name
> > > AND cr.last_name = crs.last_name
> > > AND cr.ssn = crs.ssn)
> > >
> > > Thanks...
> > >
> >
> >
> >|||"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:4245938B-E84F-4397-AAD8-1A415CC02BA6@.microsoft.com...
> I think you gave me that before but I was getting an error when I used
that
> query. Will that update and insert records per my requirements?
Eric,
You need to read the article I posted the link to. Read the examples on
how to create a linked server pointing to your spreadsheet, then replace
your 'cr_staging' table in the query with the linked server.

Doubt in " HOW TO: Query and Display Excel Data by Using ASP.NET, ADO.NET, and Visual C# .

Hi,

In my application, I need to copy data from an Excel file into a SQL
table. The article related to this can be found at

http://support.microsoft.com/defaul...Ben-us%3B306572

Using this,I am first extracting data from given excel file into a
temporary DataTable. After making some operations on that DataTable
(like splitting one column into two), I am saving the data into actual
table in SQL Server.

My doubt is that, in the above given link, there are few steps needed
to do on the excel file... eg.
--> Highlight the rows and columns where the data resides.
--> On the Insert menu, point to Name, and then click Define.
--> In the Names in workbook text box, type myRange1, and then click
OK.

I don't want my client to do thsese operations everytime he changes
contents in that excel file, as this excel file changes almost daily.
The extracting of data from excel file is done after clicking UPDATE
button on webpage. The person saving data into Excel file is differnet
from the one who updates it into SQL.

Is there any other way where we can directly copy data from excel file
into DataTable without doing the above three steps?

I am using Visual Studio.NET 2003,SQL Server 2000 and C# as developing
language for this web-based project.

Any advice would be greatly appreciated. Thanks in advance.

Regards,
RK."RK" <rakish123@.yahoo.com> wrote in message
news:22626a3.0404020930.7d9bdd7e@.posting.google.co m...
> Hi,
> In my application, I need to copy data from an Excel file into a SQL
> table. The article related to this can be found at
> http://support.microsoft.com/defaul...Ben-us%3B306572
> Using this,I am first extracting data from given excel file into a
> temporary DataTable. After making some operations on that DataTable
> (like splitting one column into two), I am saving the data into actual
> table in SQL Server.
> My doubt is that, in the above given link, there are few steps needed
> to do on the excel file... eg.
> --> Highlight the rows and columns where the data resides.
> --> On the Insert menu, point to Name, and then click Define.
> --> In the Names in workbook text box, type myRange1, and then click
> OK.
> I don't want my client to do thsese operations everytime he changes
> contents in that excel file, as this excel file changes almost daily.
> The extracting of data from excel file is done after clicking UPDATE
> button on webpage. The person saving data into Excel file is differnet
> from the one who updates it into SQL.
> Is there any other way where we can directly copy data from excel file
> into DataTable without doing the above three steps?
> I am using Visual Studio.NET 2003,SQL Server 2000 and C# as developing
> language for this web-based project.
> Any advice would be greatly appreciated. Thanks in advance.
> Regards,
> RK.

The article doesn't mention SQL Server anywhere, and it sounds like your
problem is how to automate something in Excel, so I guess you'll get a
better answer in an Excel or C# newsgroup.

Simon|||RK (rakish123@.yahoo.com) writes:
> Using this,I am first extracting data from given excel file into a
> temporary DataTable. After making some operations on that DataTable
> (like splitting one column into two), I am saving the data into actual
> table in SQL Server.
> My doubt is that, in the above given link, there are few steps needed
> to do on the excel file... eg.
> --> Highlight the rows and columns where the data resides.
> --> On the Insert menu, point to Name, and then click Define.
> --> In the Names in workbook text box, type myRange1, and then click
> OK.
> I don't want my client to do thsese operations everytime he changes
> contents in that excel file, as this excel file changes almost daily.
> The extracting of data from excel file is done after clicking UPDATE
> button on webpage. The person saving data into Excel file is differnet
> from the one who updates it into SQL.
> Is there any other way where we can directly copy data from excel file
> into DataTable without doing the above three steps?

Of course there is.

I have not tried to get data from Excel with SELECT statements, but
would be surprised if "SELECT * FROM Sheet1" would work. I've mainly
use the OLE interface to get data from Excel in Perl. While I understand
that you don't want users to have to highlight things, I fail to see
but that you need to have some pre-defined structure that the Excel
file must have, or else you will have no idea of what you are importing.

But while you said SQL Server twice in your posting (look again Simon! :-),
your question seems more to be related to Excel, so you should find a
group about Excel.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You might look at the capabilities of SQL Server DTS Packages. They can
read from Excel spreadsheets. However, your datasheet would have to be
massaged enough that the data was a fairly straightforward import.

I suppose if I were you, I'd use a combination of VBA code to hack up the
spreadsheet, save it to a convenient spreadsheet, then use SQL-DMO to
trigger a DTS package for the import.

Otherwise, you could just do a VBA module that would pass in the interesting
data directly over an ADO, OLEDB or ODBC connection. We've done something
like that, it wasn't terribly difficult.

"RK" <rakish123@.yahoo.com> wrote in message
news:22626a3.0404020930.7d9bdd7e@.posting.google.co m...
> Hi,
> In my application, I need to copy data from an Excel file into a SQL
> table. The article related to this can be found at
> http://support.microsoft.com/defaul...Ben-us%3B306572
> Using this,I am first extracting data from given excel file into a
> temporary DataTable. After making some operations on that DataTable
> (like splitting one column into two), I am saving the data into actual
> table in SQL Server.
> My doubt is that, in the above given link, there are few steps needed
> to do on the excel file... eg.
> --> Highlight the rows and columns where the data resides.
> --> On the Insert menu, point to Name, and then click Define.
> --> In the Names in workbook text box, type myRange1, and then click
> OK.
> I don't want my client to do thsese operations everytime he changes
> contents in that excel file, as this excel file changes almost daily.
> The extracting of data from excel file is done after clicking UPDATE
> button on webpage. The person saving data into Excel file is differnet
> from the one who updates it into SQL.
> Is there any other way where we can directly copy data from excel file
> into DataTable without doing the above three steps?
> I am using Visual Studio.NET 2003,SQL Server 2000 and C# as developing
> language for this web-based project.
> Any advice would be greatly appreciated. Thanks in advance.
> Regards,
> RK.|||Hi,

There are a few alternatives. I recommend you to use SQL*XL an addin I
wrote for Excel to do these things. There are some other alternatives
which I will mention.

1. SQL*XL - addin for Excel to access your databases.
SQL*XL is an addin for Excel that allows you to access your database
directly from Excel. You can query data into Excel or you can pump
data from Excel into the database. SQL*XL focusses on making it easy
for the end user but it can also be used to make scripts as it is
fully compatible with Excel macro recording and VBA.

Have a look at SQL*XL at: www.oraxcel.com

2. Define your Excel workbook as a data source in ODBC
If you do this you can issue select statements against it. You can use
it in an openquery statement in SQL server or even make it a linked
server I suppose. Named ranges will appear as tables. Reserve row 1
for the column headers.
Problems with this technique: it is readonly, you may have problems
with accessing the workbook (in use)
The nice thing is that you can use it directly in SQL or DTS

3. You can write a little macro in Excel that dumps the file out in a
nominated format (CSV e.g) and a nominated directory. You could have
DTS to pickup the file and import it in your database table.

Hope this helps,

Gerrit-Jan Linker
Linker IT Consulting Limited
www.oraxcel.com
Author of SQL*XL

Doubt

Hi there

I have a question here. I need to import the excel template into sql server data base tables. I did worked on the regular importing of excel spread sheets to import into the appropriate data base columns. I am not getting idea with the eacel template. My main goal is to import the excel template to sql tables and then reproduce the same template for the user in the browser. If any body can help i would really appreciate....Welcome to the forums..looks like you signed up just a few mins back.

can you xplain your q in a more detailed way.
>>I am not getting idea with the eacel template.
Did you mean you are not able to use the DTS Wizard to import the excel file into a sql table ?

>> My main goal is to import the excel template to sql tables and then reproduce the same template for the user in the browser.

I didnt understand this part either.|||Yes I can't use the regular DTS ti import into the data Base tables. Because the the excel templates might be in any order and they have data in any way. I have a sample application with title, genere, format size , frequency, attributes and insertion costs as columns in excel template. The data can come or can't come in exact order as in regular excel spread sheets. I have to read the data cell by cell and import into the data base. For that I can't use the Microsoft Jet provider . I can use the Jet for the regular spread sheets, but not for the templates. The template where I import the data into the data base, I need to make a look up for each and every tables.(ex: Market--country--> string look up int fk to the country table, Devision: string look up fk to a devision table) and at the end I need to display the information to the end user according to his needs. So please tell me any ideas how I can make a look up and how I can read the data from excel template and finally import into sql tables.|||you can prbly write a stored proc that will query from a table . you can import your excel sheet into an sql table. use that as a container..then write a select stmt to query against that table. assuming the column names will be the same no matter what order they are in, this approach might work. IF the column names are also different then it will require a human to read through to identify the columns.|||Do you have any idea about the look ups. How to design the data base for that excel template. Here is I got some sentences...

User uploads an excel sheet template that has been filled out.

Header:

Market -- country string lookup int fk to a country table
Business Group -- division string lookup fk to division table
ALLOCATED BUDGET: float
GRPs (IF APPLICABLE): float
% REACH (IF APPLICABLE): float
OTS (IF APPLICABLE): float
start date smalldatetime
end date smalldatetime
flight type char[1]

row data:

Title string lookup int fk to a media location table
Frequency -- you can ignore insert as it is driven by title
Client cost float
Insertion cost float
Genre string lookup fk to Genre table
Start date smalldatetime
end date smalldatetime
Booking Date smalldatetime
Material Date smalldatetime
Onsale Date smalldatetime
Attrb 1 -- 10 varchars any number of attributes from 0 through 10 posibblities based on flight type (ie circulation format/size) which are
fk to title table

Iam not understanding the what the header is and what the row data is...How sould I make the look ups...Thanks for you answers.|||(1) Market -- country string lookup int fk to a country table
can you xplain what this means.

(2) are you trying to this thing programmatically or manually.|||Iam also understanding that much. If you can explain me what's the look up and how to make a look up in the data base?. What kind of queries you write to get the thing I want that would be great.....

Market -- country string lookup int fk to a country table

I feel if suppose the country is India and country ID is 15, whenever I should enter 15 I need to get India. i think this is the look up...If you knowsome thing about how to make look ups please explain me....