Change CSV export to TAB delimited

Hi,
I want to change the CSV export to TAB delimited instead of comma delimited
in order Excel to open it properly without defining the delimiter each time I
open the export. I define the following in rsreportserver.config:
<Extension Name="CSV-TAB"
Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<OverrideNames>
<Name Language="en-US">CSV - TAB Delimited</Name>
</OverrideNames>
<Configuration>
<DeviceInfo>
<FieldDelimiter> </FieldDelimiter>
</DeviceInfo>
</Configuration>
</Extension>
but I still get comma delimited file.
Thanks,
Ronen Fidel
UnisfairI have never tried this, but perhaps you need to use \t ?
=-Chris
"FidelR" <fidelr@.newsgroups.nospam> wrote in message
news:7CC6BBEB-95AA-4205-8426-646A07974B51@.microsoft.com...
> Hi,
> I want to change the CSV export to TAB delimited instead of comma
> delimited
> in order Excel to open it properly without defining the delimiter each
> time I
> open the export. I define the following in rsreportserver.config:
> <Extension Name="CSV-TAB"
> Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
> <OverrideNames>
> <Name Language="en-US">CSV - TAB Delimited</Name>
> </OverrideNames>
> <Configuration>
> <DeviceInfo>
> <FieldDelimiter> </FieldDelimiter>
> </DeviceInfo>
> </Configuration>
> </Extension>
> but I still get comma delimited file.
> Thanks,
> Ronen Fidel
> Unisfair|||I tried \t and %09 but I still get comma delimited file. There are notes in
some web forums that claim that this is a known bug. Can you or anyone else
confirm that?
Thanks,
Ronen Fidel
Unisfair
"Chris Conner" wrote:
> I have never tried this, but perhaps you need to use \t ?
> =-Chris
> "FidelR" <fidelr@.newsgroups.nospam> wrote in message
> news:7CC6BBEB-95AA-4205-8426-646A07974B51@.microsoft.com...
> > Hi,
> >
> > I want to change the CSV export to TAB delimited instead of comma
> > delimited
> > in order Excel to open it properly without defining the delimiter each
> > time I
> > open the export. I define the following in rsreportserver.config:
> > <Extension Name="CSV-TAB"
> > Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
> > <OverrideNames>
> > <Name Language="en-US">CSV - TAB Delimited</Name>
> > </OverrideNames>
> > <Configuration>
> > <DeviceInfo>
> > <FieldDelimiter> </FieldDelimiter>
> > </DeviceInfo>
> > </Configuration>
> > </Extension>
> > but I still get comma delimited file.
> >
> > Thanks,
> > Ronen Fidel
> > Unisfair
>
>

Certain rows to excel files

Hello,

I have a table T1 with ID and Desc fields. I want to export this table to excel files based on the ID field. So my stored procedure select all the same T1.ID and export it to ID.xls files. How can I do this?

You can easily achieve this in DTS Import/Export Wizard. Suppose you're using SQL2000, open Enterprise Manager->go to the table from which you want to export data->right click choose All Tasks-> Export Data-> in the Specify Table Copy or Query step, choose Use a Query...->enter the SELECT command (e.g. SELECT * FROM Orders WHERE EmployeeID=6), or use Query Builder->complete the wizard.

|||

Thanks for the reply, How should I run the same queries for all the IDs separately in DTS and save the results to excel files?

certain rows to Excel files

Hello,

I have a table T1 with ID and Desc fields. I want to export this table to excel files based on the ID field. So my stored procedure select all the same T1.ID and export it to ID.xls files. How can I do this?

Hi,

While Using DTS Import/Export Wizard,
After Choosing Data Source and DataDestination,
Wizard asks to Specify Table Copy or Query,
At the Time Select the Radio Button [Use a Query to Specify the Data To Transfer].

It will Take you To Type Sql Statement where Query Statement Panel is there ,you can Type Query with your Condition.

|||

Thanks for the reply.

This should be done once every day , so I am trying to automate it. How should I develop my query and dts based on this criteria? Any example will be greatly appreciated.

|||

Select Jobs From SQL Server Agent Under Management of the Selected Server(In EnterpriseManager)

Select New Job and Name it
Select Steps Tab in New Job Properties
Click New step and Name Step and Write Query in Command Panel
Click New Schedule and Name Schedule and Select Schedule Type

Now you Achieve the Required Thing

|||

I still do not see how the query that I am writing will export data to a different excel file based on the ID. All the rows that have the same ID should go to a single excel file, so I am expecting more than one excel file based on the distinct values of ID to be created. How can I do this in DTS?

Certain rows to excel files

Hello,
I have a table T1 with ID and Desc fields. I want to export this table to
excel files based on the ID field. So my stored procedure select all the sam
e
T1.ID and export it to ID.xls files. How can I do this?Jim,
is this a one-off? If so I'd use the import/export wizard. If it is a
regular process, I'd look at using a scheduled DTS package (you can use the
import/export one as a template for this). There are other options namely
BCP and OSQL which run from the command-line which might also be relevant
for you.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hi Paul,
This should be done once every day , so I am trying to automate it. How
should I develop my query and dts based on this criteria? Any example will b
e
greatly appreciated.
"Paul Ibison" wrote:

> Jim,
> is this a one-off? If so I'd use the import/export wizard. If it is a
> regular process, I'd look at using a scheduled DTS package (you can use th
e
> import/export one as a template for this). There are other options namely
> BCP and OSQL which run from the command-line which might also be relevant
> for you.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||Jim,
is the ID list hardcoded or do new IDs appear? If it is hardcoded, then
you'll have a single 'Transform data' task for each ID and the destination
will be also hardcoded in this case.
If this is not the case, then I'd have a lookup table which contains the
ID/path as a pair of columns. You'll need to then iterate through that
table. Each iteration could call the package, sending in the 2 details as
global variables. These global variables will be used to modify the package
to run with the correct ID values. This is done through the Dynamic
Properties task, which would be mapped to the excel file path and the
parameter used for the stored proc.
I realize this sounds complicated, but it's actually quite easy to set up.
The main issue is whether the ID's are known in advance or not. If they are,
then it is quite simple.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||This table has IDs and Desc already in it. IDs might be duplicated and I am
trying to export the rows with the same IDs to a single excel file, so
expecting many excel files based on the distinct values of IDs. DTS seems
quite complicated to me since I have not done anything with that yet.
"Paul Ibison" wrote:

> Jim,
> is the ID list hardcoded or do new IDs appear? If it is hardcoded, then
> you'll have a single 'Transform data' task for each ID and the destination
> will be also hardcoded in this case.
> If this is not the case, then I'd have a lookup table which contains the
> ID/path as a pair of columns. You'll need to then iterate through that
> table. Each iteration could call the package, sending in the 2 details as
> global variables. These global variables will be used to modify the packag
e
> to run with the correct ID values. This is done through the Dynamic
> Properties task, which would be mapped to the excel file path and the
> parameter used for the stored proc.
> I realize this sounds complicated, but it's actually quite easy to set up.
> The main issue is whether the ID's are known in advance or not. If they ar
e,
> then it is quite simple.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
>|||JIM.H. wrote:
> This table has IDs and Desc already in it. IDs might be duplicated and I a
m
> trying to export the rows with the same IDs to a single excel file, so
> expecting many excel files based on the distinct values of IDs. DTS seems
> quite complicated to me since I have not done anything with that yet.
>
Jim, here is a link to a message thread that describes how to use a
template XLS file, some VBScript, and OPENROWSET to create a new Excel
document and export data to it, without using DTS. Might prove useful
to you:
http://www.sqlteam.com/forums/topic...926&whichpage=6
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Jim,
if you know all the IDs in advance, then you could hardcode these into the
DTS package as data flow tasks and this would be surprisingly easy (use the
export wizard to create the main template to see how it works), or if you
are more familiar with BCP, you could use a separate bcp commandline for
each excel file.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Great. Thanks Tracy.
"Tracy McKibben" wrote:

> JIM.H. wrote:
> Jim, here is a link to a message thread that describes how to use a
> template XLS file, some VBScript, and OPENROWSET to create a new Excel
> document and export data to it, without using DTS. Might prove useful
> to you:
> http://www.sqlteam.com/forums/topic...926&whichpage=6
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Certain rows to excel files

Hello,
I have a table T1 with ID and Desc fields. I want to export this table to
excel files based on the ID field. So my stored procedure select all the same
T1.ID and export it to ID.xls files. How can I do this?Jim,
is this a one-off? If so I'd use the import/export wizard. If it is a
regular process, I'd look at using a scheduled DTS package (you can use the
import/export one as a template for this). There are other options namely
BCP and OSQL which run from the command-line which might also be relevant
for you.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hi Paul,
This should be done once every day , so I am trying to automate it. How
should I develop my query and dts based on this criteria? Any example will be
greatly appreciated.
"Paul Ibison" wrote:
> Jim,
> is this a one-off? If so I'd use the import/export wizard. If it is a
> regular process, I'd look at using a scheduled DTS package (you can use the
> import/export one as a template for this). There are other options namely
> BCP and OSQL which run from the command-line which might also be relevant
> for you.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||Jim,
is the ID list hardcoded or do new IDs appear? If it is hardcoded, then
you'll have a single 'Transform data' task for each ID and the destination
will be also hardcoded in this case.
If this is not the case, then I'd have a lookup table which contains the
ID/path as a pair of columns. You'll need to then iterate through that
table. Each iteration could call the package, sending in the 2 details as
global variables. These global variables will be used to modify the package
to run with the correct ID values. This is done through the Dynamic
Properties task, which would be mapped to the excel file path and the
parameter used for the stored proc.
I realize this sounds complicated, but it's actually quite easy to set up.
The main issue is whether the ID's are known in advance or not. If they are,
then it is quite simple.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||This table has IDs and Desc already in it. IDs might be duplicated and I am
trying to export the rows with the same IDs to a single excel file, so
expecting many excel files based on the distinct values of IDs. DTS seems
quite complicated to me since I have not done anything with that yet.
"Paul Ibison" wrote:
> Jim,
> is the ID list hardcoded or do new IDs appear? If it is hardcoded, then
> you'll have a single 'Transform data' task for each ID and the destination
> will be also hardcoded in this case.
> If this is not the case, then I'd have a lookup table which contains the
> ID/path as a pair of columns. You'll need to then iterate through that
> table. Each iteration could call the package, sending in the 2 details as
> global variables. These global variables will be used to modify the package
> to run with the correct ID values. This is done through the Dynamic
> Properties task, which would be mapped to the excel file path and the
> parameter used for the stored proc.
> I realize this sounds complicated, but it's actually quite easy to set up.
> The main issue is whether the ID's are known in advance or not. If they are,
> then it is quite simple.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
>|||JIM.H. wrote:
> This table has IDs and Desc already in it. IDs might be duplicated and I am
> trying to export the rows with the same IDs to a single excel file, so
> expecting many excel files based on the distinct values of IDs. DTS seems
> quite complicated to me since I have not done anything with that yet.
>
Jim, here is a link to a message thread that describes how to use a
template XLS file, some VBScript, and OPENROWSET to create a new Excel
document and export data to it, without using DTS. Might prove useful
to you:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926&whichpage=6
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Jim,
if you know all the IDs in advance, then you could hardcode these into the
DTS package as data flow tasks and this would be surprisingly easy (use the
export wizard to create the main template to see how it works), or if you
are more familiar with BCP, you could use a separate bcp commandline for
each excel file.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Great. Thanks Tracy.
"Tracy McKibben" wrote:
> JIM.H. wrote:
> > This table has IDs and Desc already in it. IDs might be duplicated and I am
> > trying to export the rows with the same IDs to a single excel file, so
> > expecting many excel files based on the distinct values of IDs. DTS seems
> > quite complicated to me since I have not done anything with that yet.
> >
> Jim, here is a link to a message thread that describes how to use a
> template XLS file, some VBScript, and OPENROWSET to create a new Excel
> document and export data to it, without using DTS. Might prove useful
> to you:
> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926&whichpage=6
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Certain numeric fields not read from the Excel file when using a Excel file source.

I have the Excel Connection Manager and Source to read the contents from an Excel file. For some reason couple of numeric fields from the Excel worksheet are brought over as nulls even though they have a value of 300 and 150. I am not sure why this is happening. I looked into the format of the fields and they are set to General in Excel, I tried setting them to numeric and that did not help.

All the other content from the excel file is coming thru except for the 2 numeric fields.

I tried to bring the contents from the excel source to a text file in csv format and for some reason the 2 numeric fields came out as blank.

Any inputs on getting this addressed will be much appreciated.

Thanks,

Manisha

Try doing a data conversion of this column in the transformation phase using the Derived Column Transformation or the Data Conversion Transformation. Hope that works for you.|||

It has to do with a registry setting. I got a response from some other forum and I tried it and it worked.

|||I am having a similar issue. I am importing an Excel sheet from a customer of ours into a SQL Server 2005 table. The first column is text and imports OK. The 2nd column has numbers, but the first two rows import as nulls. If I open the sheet and retype the numbers exactly as they appear, then the sheet imports OK. Each time they send us this sheet I have to retype the numbers in the first two rows. If I import the sheet into Access (using the Access Import wizard), Access does not have a problem and the first two numbers show up OK in Access (without my having to retype them first). How were you able to resolve this issue? Thanks.|||

Al C. wrote:

I am having a similar issue. I am importing an Excel sheet from a customer of ours into a SQL Server 2005 table. The first column is text and imports OK. The 2nd column has numbers, but the first two rows import as nulls. If I open the sheet and retype the numbers exactly as they appear, then the sheet imports OK. Each time they send us this sheet I have to retype the numbers in the first two rows. If I import the sheet into Access (using the Access Import wizard), Access does not have a problem and the first two numbers show up OK in Access (without my having to retype them first). How were you able to resolve this issue? Thanks.

Can you set up the Excel connector to import all fields as text fields, and then later convert them to numerics using a derived column transformation?|||

Phil,

TFYR. I verified in the Excel Source (Advanced Editor) that it is using DT_WSTR in both the External columns and Output columns. When I click the Preview button it shows NULL in the first two rows. If I add a DataViewer it also shows nulls. Evidently the first two rows are considered text even though the values are numeric. I can see the difference when I use the formula auditing tool and the first two rows align to the left (indicating text) and the others align to the right (numeric). So what I have gathered is that Excel samples the first nn rows to determine the datatype and since most values are numeric it deems the datatype of the column to be numeric and so it replaces the first two values with nulls. If I modify the Excel connection string and add 'IMEX=1' (import mode=1) to the extended properties, then it works OK.

Certain numeric fields not read from the Excel file when using a Excel file source.

I have the Excel Connection Manager and Source to read the contents from an Excel file. For some reason couple of numeric fields from the Excel worksheet are brought over as nulls even though they have a value of 300 and 150. I am not sure why this is happening. I looked into the format of the fields and they are set to General in Excel, I tried setting them to numeric and that did not help.

All the other content from the excel file is coming thru except for the 2 numeric fields.

I tried to bring the contents from the excel source to a text file in csv format and for some reason the 2 numeric fields came out as blank.

Any inputs on getting this addressed will be much appreciated.

Thanks,

Manisha

Try doing a data conversion of this column in the transformation phase using the Derived Column Transformation or the Data Conversion Transformation. Hope that works for you.|||

It has to do with a registry setting. I got a response from some other forum and I tried it and it worked.

|||I am having a similar issue. I am importing an Excel sheet from a customer of ours into a SQL Server 2005 table. The first column is text and imports OK. The 2nd column has numbers, but the first two rows import as nulls. If I open the sheet and retype the numbers exactly as they appear, then the sheet imports OK. Each time they send us this sheet I have to retype the numbers in the first two rows. If I import the sheet into Access (using the Access Import wizard), Access does not have a problem and the first two numbers show up OK in Access (without my having to retype them first). How were you able to resolve this issue? Thanks.|||

Al C. wrote:

I am having a similar issue. I am importing an Excel sheet from a customer of ours into a SQL Server 2005 table. The first column is text and imports OK. The 2nd column has numbers, but the first two rows import as nulls. If I open the sheet and retype the numbers exactly as they appear, then the sheet imports OK. Each time they send us this sheet I have to retype the numbers in the first two rows. If I import the sheet into Access (using the Access Import wizard), Access does not have a problem and the first two numbers show up OK in Access (without my having to retype them first). How were you able to resolve this issue? Thanks.

Can you set up the Excel connector to import all fields as text fields, and then later convert them to numerics using a derived column transformation?|||

Phil,

TFYR. I verified in the Excel Source (Advanced Editor) that it is using DT_WSTR in both the External columns and Output columns. When I click the Preview button it shows NULL in the first two rows. If I add a DataViewer it also shows nulls. Evidently the first two rows are considered text even though the values are numeric. I can see the difference when I use the formula auditing tool and the first two rows align to the left (indicating text) and the others align to the right (numeric). So what I have gathered is that Excel samples the first nn rows to determine the datatype and since most values are numeric it deems the datatype of the column to be numeric and so it replaces the first two values with nulls. If I modify the Excel connection string and add 'IMEX=1' (import mode=1) to the extended properties, then it works OK.

Cellset.open hangs, query works fine in Management Studio?

Hi All,

We are experiencing a very strange Cellset behaviour.

Trying to open a cellset in VBA (same in VB6) in Excel 2003, using ADOMD.Catalog, accessing a AS2005SP2, the system 'sometimes' hangs, depending on the 'dynamic' MDX query string. While the query works fine in the SQL Management Studio.

For example, this MDX works fine:

select descendants {[Measures].[NumberOfA]} on columns, [Time].[Month].[200502] on rows

from [DWH]
where {([Client].[Hierarchy].[Group].&[G000879])}

This one causes Excel (and VB6) to freeze, but nicely returns a result in SQL Management Studio!!!?

select descendants {[Measures].[NumberOfA]} on columns, [Time].[Month].[200501] on rows

from [DWH]
where {([Client].[Hierarchy].[Group].&[G000879])}

There is nothing wrong with the data, it's more connection related (I expect...)?

Does somebody have any advice?

PS: we have msxml6,asoledb9 installed

It is a firewall issue. See also: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1558402&SiteID=1

Cheers,

Tom

Cell reference in SQL Report

Hi,

Is it possible to use Cell reference in the calculations, in SQL reports.

Like Excel allows "=A1 * B1", where A1 and B1 are two cells with some numeric value. Is it possible to use Cell reference in SQL reports? Like "textbox1 * textbox 2"... or something like that, with textbox1 and textbox2 containing some numeric values.

Thanks

Perm

Yes. You can name a text box and then reference it using ReportItems!. If you have 2 text boxes named txtA and txtB and you want to add the two and put in a third box you can put ReportItems!txtA.value+ReportItems!txtB.value in the third textbox to get the total of the two cells.|||

Hi Kim,

Thank you very much for the quick reply!! It works.

Now, the next item I'm stuck on, is the running total of this calculated field. Here is the detail of my RDL:

In a table, I have "textbox34" with the formula =count(Fields!ADF_NO.Value) in it and "textbox30" with the formula =first(Fields!TOTSTKS2.Value, "ProdServSales") - reportitems!TOTSTKS.value in it. I have divided "textbox 34" with "textbox30", and placed the results in "textbox36". Now, I want to do a cummulative total (Running total) of "textbox36", using the formula =runningvalue(reportitems!textbox36.Value, sum, nothing) in "textbox14". Its giving me an error saying:

"[rsAggregateReportItemInBody] The Value expression for the textbox 'textbox14' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers."

Any suggestions? I appreciate you help on this.

Thanks

Perm

Cell Orientation

How do i format the Header cells to be oriented by 90 degrees like in Excel. I tried the WRITING MODE under INTERNATIONAL in properties, and choose "tb-rl". But it gives top to bottom. Is there any way to get it bottom to top?

Is there any example to write Expression in Writing Mode property to display in 30 degrees?

Thanks.

Girija, did you ever find a solution to this? I need to do the same thing, rotate headers by 90 degrees.

Thanks.

|||Unfortunately, this is not supported in the current version of Reporting Services. Arbitrary text rotation is something we would like to add in a future release.

Cell Orientation

How do i format the Header cells to be oriented by 90 degrees like in Excel. I tried the WRITING MODE under INTERNATIONAL in properties, and choose "tb-rl". But it gives top to bottom. Is there any way to get it bottom to top?

Is there any example to write Expression in Writing Mode property to display in 30 degrees?

Thanks.

Girija, did you ever find a solution to this? I need to do the same thing, rotate headers by 90 degrees.

Thanks.

|||Unfortunately, this is not supported in the current version of Reporting Services. Arbitrary text rotation is something we would like to add in a future release.

cell merge in excel....

after i export the crystal report to excel, but i found that it ca not fit within one cell, so i can not sorting the data during the excel... can anyone elp me...
thank you..what version of CR do you have?
the problem could be when you are exporting from the excell the option that the standard column width is 10 as default you can alter this property.

Cell Formatting

Hi,

On importing an XML file using VBA I/O function, I need to apply rules/format to specific Excel 2003 .Cells.

XML
<Column FieldName="ProductReference" FieldNumber="1">
<DataType>character</DataType>
<Format>x(20)</Format>
<Restrictions/>
<Mandatory>true</Mandatory>
<PermittedValues/>
</Column>

If the above XML-lines (strings) are read, I need to apply the following rules/formats to the identified .Cells(x,1):

1.
.Columns("A").NumberFormat = "General" or "Text" ??
2.
That when inputting a value, the user is restricted to 20 Characters
3.
That this is a Mandatory entry field

These values obviously change as per XML Output. Can anyone help, please?Is this related to Crystal Reports?

Cell comments in Excel Export

Is it possible to include a cell comment when exporting a report to Excel? My first guess was that the Textbox.ToolTip property might do the trick, but no luck.

Example: I am calculating a textbox based on the age of a particular value in days and conditionally formatting it. In the Excel export, I would like to have a cell comment (i.e., "insert comment" from the context menu in Excel) that shows how many days past the age limit the item is when it is formatted.

Thanks much for any help!

Through conventional export methodologies? No. But ofcourse through VBA/ADO anything is possible.

Adamus

catastrophic failure when refreshing a report from a cube

there is a cube that refreshes each night. in the morning there is an excel pivot table report that refreshes from the cube.it has worked fine for years, but now when i try to refresh it gives me the error "catastrophic failure". Recently i have had to archive the time dimensions of the cube, as the business only want the cube to contain 6 months of data, not 4 years. so i deleted all time dimension members older than 6 months, and deleted all fact data relating to the deleted time dimensions. this seems to be the cause of the issue, but i cant see why. can anyone shed light on this?

What type of query did the Excel pivot table execute? Did the data in the pivot table span more than 6 months of data? Did it have a member of the time dimension selected that is now missing? In general, if you will be purging data from a time dimension and cube, you'll likely want to implement some type of calculated member or named set that refers to the "current" or "most recent" set of time dimension members and have things like Excel pivot table reports refer to these things instead of referring directly to members of the dimension (since they might disappear)...

HTH,

Dave Fackler