Cursors vs. Queries: files

Showing posts with label files. Show all posts

Monday, March 19, 2012

Current Date help

Hello DBA's:

I have a linked server to DB2. The date field in my DB2 source files is of the format 1031222 (7 character). The 1 indicates 21th century.Year 2000 and beyond. 03 is year 2003,12 is the month, 22 is the day. Now this date is the current date. Tommorow would be 1031223.

Records are added to this file on a daily basis with the current date. Now I need to query this file on a daily basis using my linked server. An example query would be.

Select *
From DB2LinkedServer.Filename
where Date = CurrentDate.

Now How do I implement this current date. I am not sure how do I include the function getdate() and get to the CurrentDate format of my source data.

ThanksDECLARE @.bogusDate char(7)
SELECT @.bogusDate = CASE WHEN SUBSTRING(CONVERT(char(4),DATEPART(yyyy,GetDate()) ),1,1) = 2 THEN '1' ELSE '0' END
+ RIGHT(CONVERT(char(4),DATEPART(yyyy,GetDate())),2)
+ CONVERT(char(2),DATEPART(mm,GetDate()))
+ CONVERT(char(2),DATEPART(dd,GetDate()))
SELECT @.bogusDate|||Thanks a lot. That helped.
Originally posted by Brett Kaiser

DECLARE @.bogusDate char(7)
SELECT @.bogusDate = CASE WHEN SUBSTRING(CONVERT(char(4),DATEPART(yyyy,GetDate()) ),1,1) = 2 THEN '1' ELSE '0' END
+ RIGHT(CONVERT(char(4),DATEPART(yyyy,GetDate())),2)
+ CONVERT(char(2),DATEPART(mm,GetDate()))
+ CONVERT(char(2),DATEPART(dd,GetDate()))
SELECT @.bogusDate|||Brett,

You had sent the me this piece of code pertaining to the current date help topic.

Bretts Code:

DECLARE @.bogusDate char(7)
SELECT @.bogusDate = CASE WHEN SUBSTRING(CONVERT(char(4),DATEPART(yyyy,GetDate())
),1,1) = 2 THEN '1' ELSE '0' END
+ RIGHT(CONVERT(char(4),DATEPART(yyyy,GetDate())),2)

+ CONVERT(char(2),DATEPART(mm,GetDate()))
+ CONVERT(char(2),DATEPART(dd,GetDate()))
SELECT @.bogusDate

When you execute the above code for month < 10 or/and day < 10 you get - 1041 7 for Jan 7,2004. I want 1040107.

So I changed the above code to -

DECLARE @.bogusDate char(7)
SELECT @.bogusDate = CASE WHEN SUBSTRING(CONVERT(char(4),DATEPART(yyyy,GetDate())
),1,1) = 2 THEN '1' ELSE '0' END
+ RIGHT(CONVERT(char(4),DATEPART(yyyy,GetDate())),2)

+ CASE WHEN SUBSTRING(CONVERT(char(2),DATEPART(mm,GetDate())), 1,2) < 10
THEN '0'+CONVERT(char(2),DATEPART(mm,GetDate()))
ELSE CONVERT(char(2),DATEPART(mm,GetDate()))
END

+ CASE WHEN SUBSTRING(CONVERT(char(2),DATEPART(dd,GetDate())), 1,2) < '10'
THEN '0'+CONVERT(char(2),DATEPART(dd,GetDate()))
ELSE CONVERT(char(2),DATEPART(dd,GetDate()))
END
SELECT @.bogusDate

--

However the day part doesn't change. I have been able to modify the month. Can you help me fix the code.

Thanks for your help.

Vivek

Originally posted by Brett Kaiser

DECLARE @.bogusDate char(7)
SELECT @.bogusDate = CASE WHEN SUBSTRING(CONVERT(char(4),DATEPART(yyyy,GetDate()) ),1,1) = 2 THEN '1' ELSE '0' END
+ RIGHT(CONVERT(char(4),DATEPART(yyyy,GetDate())),2)
+ CONVERT(char(2),DATEPART(mm,GetDate()))
+ CONVERT(char(2),DATEPART(dd,GetDate()))
SELECT @.bogusDate|||My Bad:

DECLARE @.bogusDate char(7)
SELECT @.bogusDate = CASE WHEN SUBSTRING(CONVERT(char(4),DATEPART(yyyy,GetDate())
),1,1) = 2 THEN '1' ELSE '0' END
+ RIGHT(CONVERT(char(4),DATEPART(yyyy,GetDate())),2)
+ RIGHT(REPLICATE('0',2)+CONVERT(varchar(2),DATEPART (mm,GetDate())),2)
+ RIGHT(REPLICATE('0',2)+CONVERT(varchar(2),DATEPART (dd,GetDate())),2)
SELECT @.bogusDate

HAPPY NEW YEAR|||Thanks Brett. Happy New Year to you too.
Originally posted by Brett Kaiser
My Bad:

DECLARE @.bogusDate char(7)
SELECT @.bogusDate = CASE WHEN SUBSTRING(CONVERT(char(4),DATEPART(yyyy,GetDate())
),1,1) = 2 THEN '1' ELSE '0' END
+ RIGHT(CONVERT(char(4),DATEPART(yyyy,GetDate())),2)
+ RIGHT(REPLICATE('0',2)+CONVERT(varchar(2),DATEPART (mm,GetDate())),2)
+ RIGHT(REPLICATE('0',2)+CONVERT(varchar(2),DATEPART (dd,GetDate())),2)
SELECT @.bogusDate

HAPPY NEW YEAR

Friday, February 24, 2012

Cube deployment

Hi,

I am trying to silently deploy a cube within my install using the following command line:

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\Microsoft.AnalysisServices.Deployment.exe" "c:\MyCubesFolder\Cubes.asdatabase" /s

but i keep getting the error message:

Reading input files...

Done

The 'Name' property cannot contain any of the following characters: . , ; ' ` : / * | ? " & % $ ! + = ( ) [ ] { } < >

The file contains URLs in some name properties with the ':' character. I tried removing those URLs but that didnt help.

Also, I was able to deploy the cube successfully using the same file from the Microsoft.AnalysisServices.Deployment.exe IDE - so I know my file is ok.

But I want to wrap this into the install and deploy it silently. Any ideas?

Thanks

Are you doing all steps from the list below ?

To create a XMLA script from solution project you have to buld solution (generas .asdatabase file), then run deployment wizard with option specifying that you want to generate XMLA script. Step by step guide:

Run script to build solution:
devenv.exe YourSolution.sln /build development /out BuildOutputLog.log
Instead of /build you can specify /rebuild
Instead of development you can specify other soluction configuration, like: Release
Example: "c:\Program Files\Microsoft Visual Studio 8\Common7\ide\devenv.exe" "c:\documents and settings\vidas\my documents\visual studio 2005\projects\MySolution\MySolution.sln" /build development /out BuildOutputLog.log Optionally run deployment wizard in answer mode to generate deployment script configuration. This is interactive step and can be done just once. Command:
Microsoft.AnalysisServices.Deployment.exe MySolution.asdatabase /a
Here /a runs deployment wizard in answer mode.
Example: Microsoft.AnalysisServices.Deployment.exe "c:\documents and settings\Vidas\My Documents\Visual Studio 2005\Projects\MySolution\MySolution\bin\MySolution.asdatabase" /a Run deployment wizard command line script to generate XMLA file:
Microsoft.AnalysisServices.Deployment.exe MySolution.asDatabase /d /o:c:\MySolutionXMLAScript.xmla
Example: Microsoft.analysisServices.Deployment.exe "c:\Documents And Settings\Vidas\My Documents\Visual Studio 2005\Projects\MySolution\MySolution\bin\MySolution.asDatabase" /d /o:c:\MySolutionXMLAScript.xmla|||

I think most of those names will refer to internal SSAS objects like annotations, so that is not likely to be the issue, specially if you can deploy from the UI. The ouput from silently deploying Adventure Works on my laptop look like the following. Notice that the line where you are getting hte error is where it should be attempting to connect to the target server. This is probably where the illegal character is. This should be in your .deploymentOptions file and it would depend on which configuration you had build last.

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE>microsoft.analysisservices.deployment.exe "C:\Data\Projects\SSAS 2005 Samples\Enterprise\bin\Adventure Works DW.asdatabase" /s

Reading input files...
Done
Connecting to the localhost\sql05 server
Database, Adventure Works DW, found on server, localhost\sql05. Applying configuration settings and options...
Analyzing configuration settings...
Done
Analyzing optimization settings...
Done
Analyzing storage information...
Done
Analyzing security information...
Done
Generating processing sequence...
Deploying the 'Adventure Works DW' database to 'localhost\sql05'.
Done

Hope this helps

|||

Thanks for the replies

During install, I am updating the .deploymentOptions file with the ip address and instance where of the Analysis server where the cube is to be deployed. When I replaced the ip address with the system name, it seemed to work fine - looks like it cannot deal with the "." in the ip address - defect?

|||I think it might be. You should log this at http://connect.microsoft.com, it sounds like it might be an issue with the deployment wizard.

Cube deployment

Hi,

I am trying to silently deploy a cube within my install using the following command line:

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\Microsoft.AnalysisServices.Deployment.exe" "c:\MyCubesFolder\Cubes.asdatabase" /s

but i keep getting the error message:

Reading input files...

Done

The 'Name' property cannot contain any of the following characters: . , ; ' ` : / * | ? " & % $ ! + = ( ) [ ] { } < >

The file contains URLs in some name properties with the ':' character. I tried removing those URLs but that didnt help.

Also, I was able to deploy the cube successfully using the same file from the Microsoft.AnalysisServices.Deployment.exe IDE - so I know my file is ok.

But I want to wrap this into the install and deploy it silently. Any ideas?

Thanks

Are you doing all steps from the list below ?

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE>microsoft.analysisservices.deployment.exe "C:\Data\Projects\SSAS 2005 Samples\Enterprise\bin\Adventure Works DW.asdatabase" /s

Hope this helps

|||

Thanks for the replies

|||I think it might be. You should log this at http://connect.microsoft.com, it sounds like it might be an issue with the deployment wizard.

Friday, February 17, 2012

CSV import into MS SQL.

Hello,
I am new to the world of databases and would appreciate a
critique/brain storm of my idea to load CSV data files into a MS SQL
database.
My problem is that I have multiple types of CSV files, ones with first
rows equaling the column names and some with accompanying .ini files
with more specific attribute information.
So, my idea is to convert these csv files into xml and then apply an
xsl transform to a generic xml format (with applicable tag names for
the db) - which that I can then insert into a MS SQL database using a
c# data layer.
Depending on the data I have to undertake different actions (i.e. new
data create new records or mark records as amended).
Does this sound like a reasonable approach, this must be a very common
task in the world of databases?
Thank you so much for your time,
Dan.
You can import csv files with DTS or BCP
DTS is the easiest, BCP is the fastest (if you have really big files)
Lookup DTS and BCP in BOL
A great DTS site is http://www.sqldts.com/
http://sqlservercode.blogspot.com/
"Dan E" wrote:

> Hello,
> I am new to the world of databases and would appreciate a
> critique/brain storm of my idea to load CSV data files into a MS SQL
> database.
> My problem is that I have multiple types of CSV files, ones with first
> rows equaling the column names and some with accompanying .ini files
> with more specific attribute information.
> So, my idea is to convert these csv files into xml and then apply an
> xsl transform to a generic xml format (with applicable tag names for
> the db) - which that I can then insert into a MS SQL database using a
> c# data layer.
> Depending on the data I have to undertake different actions (i.e. new
> data create new records or mark records as amended).
> Does this sound like a reasonable approach, this must be a very common
> task in the world of databases?
> Thank you so much for your time,
> Dan.
>
|||Thank-You .... Can't understand why that was so hard for me...
"Dan E" wrote:

> Hello,
> I am new to the world of databases and would appreciate a
> critique/brain storm of my idea to load CSV data files into a MS SQL
> database.
> My problem is that I have multiple types of CSV files, ones with first
> rows equaling the column names and some with accompanying .ini files
> with more specific attribute information.
> So, my idea is to convert these csv files into xml and then apply an
> xsl transform to a generic xml format (with applicable tag names for
> the db) - which that I can then insert into a MS SQL database using a
> c# data layer.
> Depending on the data I have to undertake different actions (i.e. new
> data create new records or mark records as amended).
> Does this sound like a reasonable approach, this must be a very common
> task in the world of databases?
> Thank you so much for your time,
> Dan.
>

CSV import into MS SQL.

Hello,
I am new to the world of databases and would appreciate a
critique/brain storm of my idea to load CSV data files into a MS SQL
database.
My problem is that I have multiple types of CSV files, ones with first
rows equaling the column names and some with accompanying .ini files
with more specific attribute information.
So, my idea is to convert these csv files into xml and then apply an
xsl transform to a generic xml format (with applicable tag names for
the db) - which that I can then insert into a MS SQL database using a
c# data layer.
Depending on the data I have to undertake different actions (i.e. new
data create new records or mark records as amended).
Does this sound like a reasonable approach, this must be a very common
task in the world of databases?
Thank you so much for your time,
Dan.You can import csv files with DTS or BCP
DTS is the easiest, BCP is the fastest (if you have really big files)
Lookup DTS and BCP in BOL
A great DTS site is http://www.sqldts.com/
http://sqlservercode.blogspot.com/
"Dan E" wrote:

> Hello,
> I am new to the world of databases and would appreciate a
> critique/brain storm of my idea to load CSV data files into a MS SQL
> database.
> My problem is that I have multiple types of CSV files, ones with first
> rows equaling the column names and some with accompanying .ini files
> with more specific attribute information.
> So, my idea is to convert these csv files into xml and then apply an
> xsl transform to a generic xml format (with applicable tag names for
> the db) - which that I can then insert into a MS SQL database using a
> c# data layer.
> Depending on the data I have to undertake different actions (i.e. new
> data create new records or mark records as amended).
> Does this sound like a reasonable approach, this must be a very common
> task in the world of databases?
> Thank you so much for your time,
> Dan.
>|||Thank-You .... Can't understand why that was so hard for me...
"Dan E" wrote:

> Hello,
> I am new to the world of databases and would appreciate a
> critique/brain storm of my idea to load CSV data files into a MS SQL
> database.
> My problem is that I have multiple types of CSV files, ones with first
> rows equaling the column names and some with accompanying .ini files
> with more specific attribute information.
> So, my idea is to convert these csv files into xml and then apply an
> xsl transform to a generic xml format (with applicable tag names for
> the db) - which that I can then insert into a MS SQL database using a
> c# data layer.
> Depending on the data I have to undertake different actions (i.e. new
> data create new records or mark records as amended).
> Does this sound like a reasonable approach, this must be a very common
> task in the world of databases?
> Thank you so much for your time,
> Dan.
>

CSV import into MS SQL.

Hello,
I am new to the world of databases and would appreciate a
critique/brain storm of my idea to load CSV data files into a MS SQL
database.
My problem is that I have multiple types of CSV files, ones with first
rows equaling the column names and some with accompanying .ini files
with more specific attribute information.
So, my idea is to convert these csv files into xml and then apply an
xsl transform to a generic xml format (with applicable tag names for
the db) - which that I can then insert into a MS SQL database using a
c# data layer.
Depending on the data I have to undertake different actions (i.e. new
data create new records or mark records as amended).
Does this sound like a reasonable approach, this must be a very common
task in the world of databases?
Thank you so much for your time,
Dan.You can import csv files with DTS or BCP
DTS is the easiest, BCP is the fastest (if you have really big files)
Lookup DTS and BCP in BOL
A great DTS site is http://www.sqldts.com/
http://sqlservercode.blogspot.com/
"Dan E" wrote:
> Hello,
> I am new to the world of databases and would appreciate a
> critique/brain storm of my idea to load CSV data files into a MS SQL
> database.
> My problem is that I have multiple types of CSV files, ones with first
> rows equaling the column names and some with accompanying .ini files
> with more specific attribute information.
> So, my idea is to convert these csv files into xml and then apply an
> xsl transform to a generic xml format (with applicable tag names for
> the db) - which that I can then insert into a MS SQL database using a
> c# data layer.
> Depending on the data I have to undertake different actions (i.e. new
> data create new records or mark records as amended).
> Does this sound like a reasonable approach, this must be a very common
> task in the world of databases?
> Thank you so much for your time,
> Dan.
>|||Thank-You .... Can't understand why that was so hard for me...
"Dan E" wrote:
> Hello,
> I am new to the world of databases and would appreciate a
> critique/brain storm of my idea to load CSV data files into a MS SQL
> database.
> My problem is that I have multiple types of CSV files, ones with first
> rows equaling the column names and some with accompanying .ini files
> with more specific attribute information.
> So, my idea is to convert these csv files into xml and then apply an
> xsl transform to a generic xml format (with applicable tag names for
> the db) - which that I can then insert into a MS SQL database using a
> c# data layer.
> Depending on the data I have to undertake different actions (i.e. new
> data create new records or mark records as amended).
> Does this sound like a reasonable approach, this must be a very common
> task in the world of databases?
> Thank you so much for your time,
> Dan.
>

CSV files

Hi,

I guess it is a very simple question but I really can't think of an answer,

If I'm using csv files as a database what disadvantages may I have ?

I encrypt the data so it is secured and I found a csv reader that reads the files very fast.

Why would I use SQL instead ?

Thanks,

Alex.

Some disadvantages are:

1. You don´t have and advanced query language as SQL.

2. You will loose some of the advantages offered by a DataBase management system such as:

* Multiple requests handling.

* Query optimizations

* Security management

* Data Integrity management

Those are just some.

CSV files

How would I turn a CSV file into a working SQL Database?Do you mean to import data from CSV file to a table?|||I think he means that.
Use DTS. Just import your CSV file into SQL Database.

CSV Export delimited not fixed

Since RS exports csv files using a comma delimiter, Excel doesn't by
default, open the file correctly. Is there a way to export to csv using a
format which excel will handle correctly using its default settings?
Currently the user must open the file from within Excel and specify
"Delimited" not "Fixed Width" and go through the Text Import Wizard.
Thanks in advance,
Ryan OpferIs there a problem with Excel format?
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
news:OkccOY3HFHA.1108@.TK2MSFTNGP10.phx.gbl...
> Since RS exports csv files using a comma delimiter, Excel doesn't by
> default, open the file correctly. Is there a way to export to csv using a
> format which excel will handle correctly using its default settings?
> Currently the user must open the file from within Excel and specify
> "Delimited" not "Fixed Width" and go through the Text Import Wizard.
> Thanks in advance,
> Ryan Opfer
>|||Thanks Bruce for the quick reply.
The Excel format is fine provided you go through the Text Import Wizard of
Excel and specify that the file is a comma delimited csv file not Fixed
Width. I would like to figure out how to Export from RS using a format in
which Excel will natively open the file as a comma delimited file and
thereby bypass having to have the user go through the Text Import Wizard.
There was a post back in November (Jan Bodey) that said she fixed this by
specifying rc:Encoding=ASCII, but I couldn't get this to work, and if I
could have, I wouldn't expect the user to put this in the link anyway.
Does that make more sense?
Thanks,
Ryan
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:e5%2331j3HFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Is there a problem with Excel format?
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
> news:OkccOY3HFHA.1108@.TK2MSFTNGP10.phx.gbl...
>> Since RS exports csv files using a comma delimiter, Excel doesn't by
>> default, open the file correctly. Is there a way to export to csv using
>> a
>> format which excel will handle correctly using its default settings?
>> Currently the user must open the file from within Excel and specify
>> "Delimited" not "Fixed Width" and go through the Text Import Wizard.
>> Thanks in advance,
>> Ryan Opfer
>>
>|||I just noticed how I worded the subject title. By delimited not fixed, I
ment: default to "Delimited" not "Fixed Width".
Sorry for the confusion.
Ryan
"Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
news:OkccOY3HFHA.1108@.TK2MSFTNGP10.phx.gbl...
> Since RS exports csv files using a comma delimiter, Excel doesn't by
> default, open the file correctly. Is there a way to export to csv using a
> format which excel will handle correctly using its default settings?
> Currently the user must open the file from within Excel and specify
> "Delimited" not "Fixed Width" and go through the Text Import Wizard.
> Thanks in advance,
> Ryan Opfer
>|||You can render your report as an Excel file. No reason to use the CSV format
in the first place.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
news:uQS0Du3HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Thanks Bruce for the quick reply.
> The Excel format is fine provided you go through the Text Import Wizard of
> Excel and specify that the file is a comma delimited csv file not Fixed
> Width. I would like to figure out how to Export from RS using a format in
> which Excel will natively open the file as a comma delimited file and
> thereby bypass having to have the user go through the Text Import Wizard.
> There was a post back in November (Jan Bodey) that said she fixed this by
> specifying rc:Encoding=ASCII, but I couldn't get this to work, and if I
> could have, I wouldn't expect the user to put this in the link anyway.
> Does that make more sense?
> Thanks,
> Ryan
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:e5%2331j3HFHA.1476@.TK2MSFTNGP09.phx.gbl...
>> Is there a problem with Excel format?
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
>> news:OkccOY3HFHA.1108@.TK2MSFTNGP10.phx.gbl...
>> Since RS exports csv files using a comma delimiter, Excel doesn't by
>> default, open the file correctly. Is there a way to export to csv using
>> a
>> format which excel will handle correctly using its default settings?
>> Currently the user must open the file from within Excel and specify
>> "Delimited" not "Fixed Width" and go through the Text Import Wizard.
>> Thanks in advance,
>> Ryan Opfer
>>
>>
>|||An Excel format is what I tried first but I couldn't get around how Excel
sees the document (it's too smart). Excel sees the document map and puts it
on sheet 1 then on sheet 2 (which has the main table data) it shows drill
down. Ultimately this file will need to be imported into an MS SQL table so
it needs to be simple.
Excel would work fine if I can remove the document map (obviously I can do
this by just removing the document map from the report...and maybe that's
something the user will have to live with) and drill down capabilities (the
user can't live without this though), but by then I'd pretty much be looking
at a plain csv file anyway.
The key here is the user will be doing all this and just sending us back a
basic csv or Excel file for the sql import so thereby has to be a simple
process.
From your reply's, I take it that RS can't defign how it exports to csv or
for that matter Excel.
Thanks for the help,
Ryan
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23VOimb4HFHA.3076@.tk2msftngp13.phx.gbl...
> You can render your report as an Excel file. No reason to use the CSV
> format in the first place.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
> news:uQS0Du3HFHA.3332@.TK2MSFTNGP15.phx.gbl...
>> Thanks Bruce for the quick reply.
>> The Excel format is fine provided you go through the Text Import Wizard
>> of Excel and specify that the file is a comma delimited csv file not
>> Fixed Width. I would like to figure out how to Export from RS using a
>> format in which Excel will natively open the file as a comma delimited
>> file and thereby bypass having to have the user go through the Text
>> Import Wizard.
>> There was a post back in November (Jan Bodey) that said she fixed this by
>> specifying rc:Encoding=ASCII, but I couldn't get this to work, and if I
>> could have, I wouldn't expect the user to put this in the link anyway.
>> Does that make more sense?
>> Thanks,
>> Ryan
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:e5%2331j3HFHA.1476@.TK2MSFTNGP09.phx.gbl...
>> Is there a problem with Excel format?
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
>> news:OkccOY3HFHA.1108@.TK2MSFTNGP10.phx.gbl...
>> Since RS exports csv files using a comma delimiter, Excel doesn't by
>> default, open the file correctly. Is there a way to export to csv
>> using a
>> format which excel will handle correctly using its default settings?
>> Currently the user must open the file from within Excel and specify
>> "Delimited" not "Fixed Width" and go through the Text Import Wizard.
>> Thanks in advance,
>> Ryan Opfer
>>
>>
>>
>|||Here is an idea for you. Have an additional report that is very plain and
exports to Excel well. Have it be the same as the other on as far as the
data is concerned and the report parameters. Then add a link that uses jump
to URL and have it render the second shadow report as Excel passing it the
parameters from the current report. I call it a shadow report because you
can set it to not be visible in list view so the user never sees it. The
only way it gets run is when they click on the link.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
news:%23V19fKAIFHA.2784@.TK2MSFTNGP09.phx.gbl...
> An Excel format is what I tried first but I couldn't get around how Excel
> sees the document (it's too smart). Excel sees the document map and puts
it
> on sheet 1 then on sheet 2 (which has the main table data) it shows drill
> down. Ultimately this file will need to be imported into an MS SQL table
so
> it needs to be simple.
> Excel would work fine if I can remove the document map (obviously I can do
> this by just removing the document map from the report...and maybe that's
> something the user will have to live with) and drill down capabilities
(the
> user can't live without this though), but by then I'd pretty much be
looking
> at a plain csv file anyway.
> The key here is the user will be doing all this and just sending us back a
> basic csv or Excel file for the sql import so thereby has to be a simple
> process.
> From your reply's, I take it that RS can't defign how it exports to csv or
> for that matter Excel.
> Thanks for the help,
> Ryan
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23VOimb4HFHA.3076@.tk2msftngp13.phx.gbl...
> > You can render your report as an Excel file. No reason to use the CSV
> > format in the first place.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
> > news:uQS0Du3HFHA.3332@.TK2MSFTNGP15.phx.gbl...
> >> Thanks Bruce for the quick reply.
> >>
> >> The Excel format is fine provided you go through the Text Import Wizard
> >> of Excel and specify that the file is a comma delimited csv file not
> >> Fixed Width. I would like to figure out how to Export from RS using a
> >> format in which Excel will natively open the file as a comma delimited
> >> file and thereby bypass having to have the user go through the Text
> >> Import Wizard.
> >>
> >> There was a post back in November (Jan Bodey) that said she fixed this
by
> >> specifying rc:Encoding=ASCII, but I couldn't get this to work, and if I
> >> could have, I wouldn't expect the user to put this in the link anyway.
> >>
> >> Does that make more sense?
> >>
> >> Thanks,
> >>
> >> Ryan
> >>
> >>
> >> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> >> news:e5%2331j3HFHA.1476@.TK2MSFTNGP09.phx.gbl...
> >> Is there a problem with Excel format?
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
> >> news:OkccOY3HFHA.1108@.TK2MSFTNGP10.phx.gbl...
> >> Since RS exports csv files using a comma delimiter, Excel doesn't by
> >> default, open the file correctly. Is there a way to export to csv
> >> using a
> >> format which excel will handle correctly using its default settings?
> >> Currently the user must open the file from within Excel and specify
> >> "Delimited" not "Fixed Width" and go through the Text Import Wizard.
> >>
> >> Thanks in advance,
> >>
> >> Ryan Opfer
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>|||Thanks Bruce for all the help, I do appreciate it. This sounds like an
adequate solution that I think will work.
It does seems strange though (although seemingly common for MS) to have RS
export to csv using a format that Excel doesn't natively understand by
default. I would have hoped that whatever MS decides to set as a default
format for csv would be the same between MS applications. I guess that's
just something we should all be use to by now anyway.
Ryan Opfer
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:u1VflVAIFHA.3536@.TK2MSFTNGP14.phx.gbl...
> Here is an idea for you. Have an additional report that is very plain and
> exports to Excel well. Have it be the same as the other on as far as the
> data is concerned and the report parameters. Then add a link that uses
> jump
> to URL and have it render the second shadow report as Excel passing it the
> parameters from the current report. I call it a shadow report because you
> can set it to not be visible in list view so the user never sees it. The
> only way it gets run is when they click on the link.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
> news:%23V19fKAIFHA.2784@.TK2MSFTNGP09.phx.gbl...
>> An Excel format is what I tried first but I couldn't get around how Excel
>> sees the document (it's too smart). Excel sees the document map and puts
> it
>> on sheet 1 then on sheet 2 (which has the main table data) it shows drill
>> down. Ultimately this file will need to be imported into an MS SQL table
> so
>> it needs to be simple.
>> Excel would work fine if I can remove the document map (obviously I can
>> do
>> this by just removing the document map from the report...and maybe that's
>> something the user will have to live with) and drill down capabilities
> (the
>> user can't live without this though), but by then I'd pretty much be
> looking
>> at a plain csv file anyway.
>> The key here is the user will be doing all this and just sending us back
>> a
>> basic csv or Excel file for the sql import so thereby has to be a simple
>> process.
>> From your reply's, I take it that RS can't defign how it exports to csv
>> or
>> for that matter Excel.
>> Thanks for the help,
>> Ryan
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:%23VOimb4HFHA.3076@.tk2msftngp13.phx.gbl...
>> > You can render your report as an Excel file. No reason to use the CSV
>> > format in the first place.
>> >
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
>> > news:uQS0Du3HFHA.3332@.TK2MSFTNGP15.phx.gbl...
>> >> Thanks Bruce for the quick reply.
>> >>
>> >> The Excel format is fine provided you go through the Text Import
>> >> Wizard
>> >> of Excel and specify that the file is a comma delimited csv file not
>> >> Fixed Width. I would like to figure out how to Export from RS using a
>> >> format in which Excel will natively open the file as a comma delimited
>> >> file and thereby bypass having to have the user go through the Text
>> >> Import Wizard.
>> >>
>> >> There was a post back in November (Jan Bodey) that said she fixed this
> by
>> >> specifying rc:Encoding=ASCII, but I couldn't get this to work, and if
>> >> I
>> >> could have, I wouldn't expect the user to put this in the link anyway.
>> >>
>> >> Does that make more sense?
>> >>
>> >> Thanks,
>> >>
>> >> Ryan
>> >>
>> >>
>> >> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> >> news:e5%2331j3HFHA.1476@.TK2MSFTNGP09.phx.gbl...
>> >> Is there a problem with Excel format?
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
>> >> news:OkccOY3HFHA.1108@.TK2MSFTNGP10.phx.gbl...
>> >> Since RS exports csv files using a comma delimiter, Excel doesn't by
>> >> default, open the file correctly. Is there a way to export to csv
>> >> using a
>> >> format which excel will handle correctly using its default settings?
>> >> Currently the user must open the file from within Excel and specify
>> >> "Delimited" not "Fixed Width" and go through the Text Import Wizard.
>> >>
>> >> Thanks in advance,
>> >>
>> >> Ryan Opfer
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>|||For some reason, Excel doesn't understand Unicode CSV files by default. If
we did an ANSI CSV file (which you can specify in the DeviceInfo) then we
would lose data in certain scenarios. We could look at all of the characters
in the file and see whether we could use ANSI without data loss but this is
a lot of work.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
news:%23ETGwjAIFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Thanks Bruce for all the help, I do appreciate it. This sounds like an
> adequate solution that I think will work.
>
> It does seems strange though (although seemingly common for MS) to have RS
> export to csv using a format that Excel doesn't natively understand by
> default. I would have hoped that whatever MS decides to set as a default
> format for csv would be the same between MS applications. I guess that's
> just something we should all be use to by now anyway.
>
> Ryan Opfer
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:u1VflVAIFHA.3536@.TK2MSFTNGP14.phx.gbl...
>> Here is an idea for you. Have an additional report that is very plain and
>> exports to Excel well. Have it be the same as the other on as far as the
>> data is concerned and the report parameters. Then add a link that uses
>> jump
>> to URL and have it render the second shadow report as Excel passing it
>> the
>> parameters from the current report. I call it a shadow report because you
>> can set it to not be visible in list view so the user never sees it. The
>> only way it gets run is when they click on the link.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
>> news:%23V19fKAIFHA.2784@.TK2MSFTNGP09.phx.gbl...
>> An Excel format is what I tried first but I couldn't get around how
>> Excel
>> sees the document (it's too smart). Excel sees the document map and puts
>> it
>> on sheet 1 then on sheet 2 (which has the main table data) it shows
>> drill
>> down. Ultimately this file will need to be imported into an MS SQL table
>> so
>> it needs to be simple.
>> Excel would work fine if I can remove the document map (obviously I can
>> do
>> this by just removing the document map from the report...and maybe
>> that's
>> something the user will have to live with) and drill down capabilities
>> (the
>> user can't live without this though), but by then I'd pretty much be
>> looking
>> at a plain csv file anyway.
>> The key here is the user will be doing all this and just sending us back
>> a
>> basic csv or Excel file for the sql import so thereby has to be a simple
>> process.
>> From your reply's, I take it that RS can't defign how it exports to csv
>> or
>> for that matter Excel.
>> Thanks for the help,
>> Ryan
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:%23VOimb4HFHA.3076@.tk2msftngp13.phx.gbl...
>> > You can render your report as an Excel file. No reason to use the CSV
>> > format in the first place.
>> >
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
>> > news:uQS0Du3HFHA.3332@.TK2MSFTNGP15.phx.gbl...
>> >> Thanks Bruce for the quick reply.
>> >>
>> >> The Excel format is fine provided you go through the Text Import
>> >> Wizard
>> >> of Excel and specify that the file is a comma delimited csv file not
>> >> Fixed Width. I would like to figure out how to Export from RS using
>> >> a
>> >> format in which Excel will natively open the file as a comma
>> >> delimited
>> >> file and thereby bypass having to have the user go through the Text
>> >> Import Wizard.
>> >>
>> >> There was a post back in November (Jan Bodey) that said she fixed
>> >> this
>> by
>> >> specifying rc:Encoding=ASCII, but I couldn't get this to work, and if
>> >> I
>> >> could have, I wouldn't expect the user to put this in the link
>> >> anyway.
>> >>
>> >> Does that make more sense?
>> >>
>> >> Thanks,
>> >>
>> >> Ryan
>> >>
>> >>
>> >> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> >> news:e5%2331j3HFHA.1476@.TK2MSFTNGP09.phx.gbl...
>> >> Is there a problem with Excel format?
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Ryan Opfer" <ropfer@.usa.ibs.org> wrote in message
>> >> news:OkccOY3HFHA.1108@.TK2MSFTNGP10.phx.gbl...
>> >> Since RS exports csv files using a comma delimiter, Excel doesn't
>> >> by
>> >> default, open the file correctly. Is there a way to export to csv
>> >> using a
>> >> format which excel will handle correctly using its default
>> >> settings?
>> >> Currently the user must open the file from within Excel and specify
>> >> "Delimited" not "Fixed Width" and go through the Text Import
>> >> Wizard.
>> >>
>> >> Thanks in advance,
>> >>
>> >> Ryan Opfer
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>

Tuesday, February 14, 2012

CSV Export delimited not fixed

CSV Export

I'm using a webclient to pull back the different rendering format's
available from SQL Reports. I guess MS added CSV files and I would like to
bring that down - but I'm having a little trouble passing the paramater that
will bring it back. Following is my current list. Does anyone know what it
should by for csv? (I've tried CSV and it doesn't work.)
case _rsTypePDF:
fext = "pdf";
ReportFormat = "PDF";
break;
case _rsTypeIMAGE:
fext = "tif";
ReportFormat = "IMAGE";
break;
case _rsTypeMHTML:
fext = "htm";
ReportFormat = "MHTML";
break;
case _rsTypeEXCEL:
fext = "xls";
ReportFormat = "EXCEL";
break;
Thanks,
DavidGot it to work - but realize it's not what I need.
Is there a way to render just the straight ascii in a report retaining some
of the formatting such as tabs - carriage returns - etc ?
<drchips@.nospam.nospam> wrote in message
news:ugbpufmNGHA.740@.TK2MSFTNGP12.phx.gbl...
> I'm using a webclient to pull back the different rendering format's
> available from SQL Reports. I guess MS added CSV files and I would like
> to bring that down - but I'm having a little trouble passing the paramater
> that will bring it back. Following is my current list. Does anyone know
> what it should by for csv? (I've tried CSV and it doesn't work.)
> case _rsTypePDF:
> fext = "pdf";
> ReportFormat = "PDF";
> break;
> case _rsTypeIMAGE:
> fext = "tif";
> ReportFormat = "IMAGE";
> break;
> case _rsTypeMHTML:
> fext = "htm";
> ReportFormat = "MHTML";
> break;
> case _rsTypeEXCEL:
> fext = "xls";
> ReportFormat = "EXCEL";
> break;
>
> Thanks,
>
> David
>|||Hi David,
Welcome to use MSDN Managed Newsgroup Support.
From your description, my understanding of this issue is: You want to
export the report to CSV with tab delimited. If I misunderstood your
concern, please feel free to point it out.
Reporting Services can not do it directly. Reporting Services only could
export the CSV with comma delimited. I think the best way is to do it in
Excel. You can create a new worksheet in Excel
1. Import the data from the CSV file and select the Delimited option
at the first step and select Comma as the Delimiter.
2. After import from the CSV file, you can save it as the Text(Tab
Delimited) type.
Hope this will be helpful.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================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.|||I need a straight ASCII dump of a report with some of the formatting - such
as returns, etc. Is there a way to get that?
"Wei Lu" <t-weilu@.online.microsoft.com> wrote in message
news:r6JxVfsNGHA.2336@.TK2MSFTNGXA01.phx.gbl...
> Hi David,
> Welcome to use MSDN Managed Newsgroup Support.
> From your description, my understanding of this issue is: You want to
> export the report to CSV with tab delimited. If I misunderstood your
> concern, please feel free to point it out.
> Reporting Services can not do it directly. Reporting Services only could
> export the CSV with comma delimited. I think the best way is to do it in
> Excel. You can create a new worksheet in Excel
> 1. Import the data from the CSV file and select the Delimited option
> at the first step and select Comma as the Delimiter.
> 2. After import from the CSV file, you can save it as the Text(Tab
> Delimited) type.
>
> Hope this will be helpful.
> Sincerely yours,
> Wei Lu
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> =====================================================> 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.
>|||The other issue might be that he is getting the CSV in Unicode instead of
ASCII.
Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
If you don't want to have it appear in a new window then do this in jump to
URL:
=Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
Note the rc:Encoding that is what causes the CSV to come up in ASCII. Excel
does not know how to handle unicode and puts all the data in a single
column. With ASCII, Excel will automatically split the data out into
multiple columns.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Wei Lu" <t-weilu@.online.microsoft.com> wrote in message
news:r6JxVfsNGHA.2336@.TK2MSFTNGXA01.phx.gbl...
> Hi David,
> Welcome to use MSDN Managed Newsgroup Support.
> From your description, my understanding of this issue is: You want to
> export the report to CSV with tab delimited. If I misunderstood your
> concern, please feel free to point it out.
> Reporting Services can not do it directly. Reporting Services only could
> export the CSV with comma delimited. I think the best way is to do it in
> Excel. You can create a new worksheet in Excel
> 1. Import the data from the CSV file and select the Delimited option
> at the first step and select Comma as the Delimiter.
> 2. After import from the CSV file, you can save it as the Text(Tab
> Delimited) type.
>
> Hope this will be helpful.
> Sincerely yours,
> Wei Lu
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> =====================================================> 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 David,
Thanks for the update. And also thanks Bruce's great suggestion.
I test the solution Bruce used and it works fine on my side.
I use the following statement in a Jump to URL property of a textbox.
=Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
I think it meet your requirement.
Hope this will be helpful.
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================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.|||I'm doing this in C#.
I wasn't sure what parameter you set to "_blank" so I set the mimetype to
it. Here's my code snippet:
case _rsTypeCSV:
fext = "csv";
ReportFormat = "CSV";
encoding = "ASCII";
mimeType = "_blank";
break;
}
results = rs.Render(ReportName, ReportFormat, historyID, deviceInfo,
parameters, credentials, showHide, out encoding, out mimeType, out
reportHistoryParameters, out warnings, out streamIDs);
I'm still getting back:
textbox1,textbox47,textbox30,textbox11,textbox72,textbox65,textbox27,textbox21,textbox57,textbox9,textbox4,textbox16,textbox78,textbox80,textbox12,textbox25,textbox36,RBP,Pulse,LBP,Icd9CodesTxt,textbox61,textbox41,textbox6,ExerciseProtocol,ExerciseStage,Time,ExerciseMets,RatePressure,HeartRate1Min,textbox170,textbox166,textbox162,TermMeds,textbox158,textbox154,textbox146,textbox142,textbox138,textbox114,textbox115,textbox110,textbox111,textbox106,textbox107,textbox102,textbox103,NlLowEf,textbox65,textbox61,textbox57,textbox93,textbox89,textbox85,textbox81,textbox77,textbox73,textbox69,AerobicCapacity,textbox49,textbox45,IschemicResponse,textbox41,textbox37,textbox33,textbox21,textbox18,textbox15,textbox12,textbox4,textbox3,textbox9,DateTxt
Nuclear Stress Imaging Report,"Palms Office
The whole report is a medical record which I can't show you - however - it
has still lost all of it's formatting. I'm not sure what I'm doing wrong
here.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23$jdHVvNGHA.1716@.TK2MSFTNGP10.phx.gbl...
> The other issue might be that he is getting the CSV in Unicode instead of
> ASCII.
> Here is an example of a Jump to URL link I use. This causes Excel to come
> up with the data in a separate window:
> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> If you don't want to have it appear in a new window then do this in jump
> to URL:
> =Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
> Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
> Note the rc:Encoding that is what causes the CSV to come up in ASCII.
> Excel does not know how to handle unicode and puts all the data in a
> single column. With ASCII, Excel will automatically split the data out
> into multiple columns.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Wei Lu" <t-weilu@.online.microsoft.com> wrote in message
> news:r6JxVfsNGHA.2336@.TK2MSFTNGXA01.phx.gbl...
>> Hi David,
>> Welcome to use MSDN Managed Newsgroup Support.
>> From your description, my understanding of this issue is: You want to
>> export the report to CSV with tab delimited. If I misunderstood your
>> concern, please feel free to point it out.
>> Reporting Services can not do it directly. Reporting Services only could
>> export the CSV with comma delimited. I think the best way is to do it in
>> Excel. You can create a new worksheet in Excel
>> 1. Import the data from the CSV file and select the Delimited option
>> at the first step and select Comma as the Delimiter.
>> 2. After import from the CSV file, you can save it as the Text(Tab
>> Delimited) type.
>>
>> Hope this will be helpful.
>> Sincerely yours,
>> Wei Lu
>> Microsoft Online Partner Support
>> Get Secure! - www.microsoft.com/security
>> =====================================================>> 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.
>|||_blank is definitely not mime type. That had to do with the frame to show in
the browser.
What you will get back is comma separated variable. Looking at what you
posted it looks like that is what you go back. If you do not want comma
separated variables then don't use CSV export.
Also, the reason you are getting back all the textbox information is because
your report has multiple textboxes above the table. What I do is add
additional table header rows, merge the cells and put my titles etc there.
While working it out I suggest using a very clean report (nothing but your
table on it).
What formatting are you expecting? CSV puts all the fields for a row on one
line separated by commas. The CSV is great for getting data into Excel.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<drchips@.nospam.nospam> wrote in message
news:OoJ5nc9NGHA.3576@.TK2MSFTNGP15.phx.gbl...
> I'm doing this in C#.
> I wasn't sure what parameter you set to "_blank" so I set the mimetype to
> it. Here's my code snippet:
> case _rsTypeCSV:
> fext = "csv";
> ReportFormat = "CSV";
> encoding = "ASCII";
> mimeType = "_blank";
> break;
> }
> results = rs.Render(ReportName, ReportFormat, historyID, deviceInfo,
> parameters, credentials, showHide, out encoding, out mimeType, out
> reportHistoryParameters, out warnings, out streamIDs);
>
> I'm still getting back:
> textbox1,textbox47,textbox30,textbox11,textbox72,textbox65,textbox27,textbox21,textbox57,textbox9,textbox4,textbox16,textbox78,textbox80,textbox12,textbox25,textbox36,RBP,Pulse,LBP,Icd9CodesTxt,textbox61,textbox41,textbox6,ExerciseProtocol,ExerciseStage,Time,ExerciseMets,RatePressure,HeartRate1Min,textbox170,textbox166,textbox162,TermMeds,textbox158,textbox154,textbox146,textbox142,textbox138,textbox114,textbox115,textbox110,textbox111,textbox106,textbox107,textbox102,textbox103,NlLowEf,textbox65,textbox61,textbox57,textbox93,textbox89,textbox85,textbox81,textbox77,textbox73,textbox69,AerobicCapacity,textbox49,textbox45,IschemicResponse,textbox41,textbox37,textbox33,textbox21,textbox18,textbox15,textbox12,textbox4,textbox3,textbox9,DateTxt
> Nuclear Stress Imaging Report,"Palms Office
> The whole report is a medical record which I can't show you - however - it
> has still lost all of it's formatting. I'm not sure what I'm doing wrong
> here.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23$jdHVvNGHA.1716@.TK2MSFTNGP10.phx.gbl...
>> The other issue might be that he is getting the CSV in Unicode instead of
>> ASCII.
>> Here is an example of a Jump to URL link I use. This causes Excel to come
>> up with the data in a separate window:
>> ="javascript:void(window.open('" & Globals!ReportServerUrl &
>> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
>> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>> If you don't want to have it appear in a new window then do this in jump
>> to URL:
>> =Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
>> Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
>> Note the rc:Encoding that is what causes the CSV to come up in ASCII.
>> Excel does not know how to handle unicode and puts all the data in a
>> single column. With ASCII, Excel will automatically split the data out
>> into multiple columns.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Wei Lu" <t-weilu@.online.microsoft.com> wrote in message
>> news:r6JxVfsNGHA.2336@.TK2MSFTNGXA01.phx.gbl...
>> Hi David,
>> Welcome to use MSDN Managed Newsgroup Support.
>> From your description, my understanding of this issue is: You want to
>> export the report to CSV with tab delimited. If I misunderstood your
>> concern, please feel free to point it out.
>> Reporting Services can not do it directly. Reporting Services only could
>> export the CSV with comma delimited. I think the best way is to do it in
>> Excel. You can create a new worksheet in Excel
>> 1. Import the data from the CSV file and select the Delimited
>> option
>> at the first step and select Comma as the Delimiter.
>> 2. After import from the CSV file, you can save it as the Text(Tab
>> Delimited) type.
>>
>> Hope this will be helpful.
>> Sincerely yours,
>> Wei Lu
>> Microsoft Online Partner Support
>> Get Secure! - www.microsoft.com/security
>> =====================================================>> 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.
>>
>|||It a medical diagnosis report. I need a straight ascii dump that retains
it's formatting as much as possible (Carriage returns - hopefully some
positioning by using spaces etc. )
Is there another format besides csv that can give me this? This information
needs to be posted to a central EMR repository for patient data and no meta
info for formatting is accepted by the system.
The way I'm sort of handling it now is to create an mshtml file - write a
bat file to launch it from my app. At that point - a transcriptionist has
to stick her cursor in the browser - hit Cntrl-A - Cntrl C - close the
browser - switch to her EMR interface - hit Cntrl V and save it. This
method doesn't bring any of the html tags over and retains some formatting.
I need to be able to eliminate the transcriptionists intervention in this so
she can focus on all the other work she has to do. Any suggestions? (I can
automate the interface between the systems with no problem - I just need the
ASCII dump)
Thanks for all your input so far.
David
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:ec90zo9NGHA.3936@.TK2MSFTNGP12.phx.gbl...
> _blank is definitely not mime type. That had to do with the frame to show
> in the browser.
> What you will get back is comma separated variable. Looking at what you
> posted it looks like that is what you go back. If you do not want comma
> separated variables then don't use CSV export.
> Also, the reason you are getting back all the textbox information is
> because your report has multiple textboxes above the table. What I do is
> add additional table header rows, merge the cells and put my titles etc
> there.
> While working it out I suggest using a very clean report (nothing but your
> table on it).
> What formatting are you expecting? CSV puts all the fields for a row on
> one line separated by commas. The CSV is great for getting data into
> Excel.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> <drchips@.nospam.nospam> wrote in message
> news:OoJ5nc9NGHA.3576@.TK2MSFTNGP15.phx.gbl...
>> I'm doing this in C#.
>> I wasn't sure what parameter you set to "_blank" so I set the mimetype to
>> it. Here's my code snippet:
>> case _rsTypeCSV:
>> fext = "csv";
>> ReportFormat = "CSV";
>> encoding = "ASCII";
>> mimeType = "_blank";
>> break;
>> }
>> results = rs.Render(ReportName, ReportFormat, historyID, deviceInfo,
>> parameters, credentials, showHide, out encoding, out mimeType, out
>> reportHistoryParameters, out warnings, out streamIDs);
>>
>> I'm still getting back:
>> textbox1,textbox47,textbox30,textbox11,textbox72,textbox65,textbox27,textbox21,textbox57,textbox9,textbox4,textbox16,textbox78,textbox80,textbox12,textbox25,textbox36,RBP,Pulse,LBP,Icd9CodesTxt,textbox61,textbox41,textbox6,ExerciseProtocol,ExerciseStage,Time,ExerciseMets,RatePressure,HeartRate1Min,textbox170,textbox166,textbox162,TermMeds,textbox158,textbox154,textbox146,textbox142,textbox138,textbox114,textbox115,textbox110,textbox111,textbox106,textbox107,textbox102,textbox103,NlLowEf,textbox65,textbox61,textbox57,textbox93,textbox89,textbox85,textbox81,textbox77,textbox73,textbox69,AerobicCapacity,textbox49,textbox45,IschemicResponse,textbox41,textbox37,textbox33,textbox21,textbox18,textbox15,textbox12,textbox4,textbox3,textbox9,DateTxt
>> Nuclear Stress Imaging Report,"Palms Office
>> The whole report is a medical record which I can't show you - however -
>> it has still lost all of it's formatting. I'm not sure what I'm doing
>> wrong here.
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:%23$jdHVvNGHA.1716@.TK2MSFTNGP10.phx.gbl...
>> The other issue might be that he is getting the CSV in Unicode instead
>> of ASCII.
>> Here is an example of a Jump to URL link I use. This causes Excel to
>> come up with the data in a separate window:
>> ="javascript:void(window.open('" & Globals!ReportServerUrl &
>> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
>> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>> If you don't want to have it appear in a new window then do this in jump
>> to URL:
>> =Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
>> Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
>> Note the rc:Encoding that is what causes the CSV to come up in ASCII.
>> Excel does not know how to handle unicode and puts all the data in a
>> single column. With ASCII, Excel will automatically split the data out
>> into multiple columns.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Wei Lu" <t-weilu@.online.microsoft.com> wrote in message
>> news:r6JxVfsNGHA.2336@.TK2MSFTNGXA01.phx.gbl...
>> Hi David,
>> Welcome to use MSDN Managed Newsgroup Support.
>> From your description, my understanding of this issue is: You want to
>> export the report to CSV with tab delimited. If I misunderstood your
>> concern, please feel free to point it out.
>> Reporting Services can not do it directly. Reporting Services only
>> could
>> export the CSV with comma delimited. I think the best way is to do it
>> in
>> Excel. You can create a new worksheet in Excel
>> 1. Import the data from the CSV file and select the Delimited
>> option
>> at the first step and select Comma as the Delimiter.
>> 2. After import from the CSV file, you can save it as the Text(Tab
>> Delimited) type.
>>
>> Hope this will be helpful.
>> Sincerely yours,
>> Wei Lu
>> Microsoft Online Partner Support
>> Get Secure! - www.microsoft.com/security
>> =====================================================>> 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.
>>
>>
>|||CSV does not do what you want. I am not aware of a straight ASCII output.
You could do html if this is from your own app and then strip the html tags
out (the .net framework has a library that would allow that).
Bruce Loehle-Conger
<drchips@.nospam.nospam> wrote in message
news:etrP63IOGHA.2916@.tk2msftngp13.phx.gbl...
> It a medical diagnosis report. I need a straight ascii dump that retains
> it's formatting as much as possible (Carriage returns - hopefully some
> positioning by using spaces etc. )
> Is there another format besides csv that can give me this? This
> information needs to be posted to a central EMR repository for patient
> data and no meta info for formatting is accepted by the system.
> The way I'm sort of handling it now is to create an mshtml file - write a
> bat file to launch it from my app. At that point - a transcriptionist has
> to stick her cursor in the browser - hit Cntrl-A - Cntrl C - close the
> browser - switch to her EMR interface - hit Cntrl V and save it. This
> method doesn't bring any of the html tags over and retains some
> formatting.
> I need to be able to eliminate the transcriptionists intervention in this
> so she can focus on all the other work she has to do. Any suggestions? (I
> can automate the interface between the systems with no problem - I just
> need the ASCII dump)
> Thanks for all your input so far.
> David
>
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:ec90zo9NGHA.3936@.TK2MSFTNGP12.phx.gbl...
>> _blank is definitely not mime type. That had to do with the frame to show
>> in the browser.
>> What you will get back is comma separated variable. Looking at what you
>> posted it looks like that is what you go back. If you do not want comma
>> separated variables then don't use CSV export.
>> Also, the reason you are getting back all the textbox information is
>> because your report has multiple textboxes above the table. What I do is
>> add additional table header rows, merge the cells and put my titles etc
>> there.
>> While working it out I suggest using a very clean report (nothing but
>> your table on it).
>> What formatting are you expecting? CSV puts all the fields for a row on
>> one line separated by commas. The CSV is great for getting data into
>> Excel.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> <drchips@.nospam.nospam> wrote in message
>> news:OoJ5nc9NGHA.3576@.TK2MSFTNGP15.phx.gbl...
>> I'm doing this in C#.
>> I wasn't sure what parameter you set to "_blank" so I set the mimetype
>> to it. Here's my code snippet:
>> case _rsTypeCSV:
>> fext = "csv";
>> ReportFormat = "CSV";
>> encoding = "ASCII";
>> mimeType = "_blank";
>> break;
>> }
>> results = rs.Render(ReportName, ReportFormat, historyID, deviceInfo,
>> parameters, credentials, showHide, out encoding, out mimeType, out
>> reportHistoryParameters, out warnings, out streamIDs);
>>
>> I'm still getting back:
>> textbox1,textbox47,textbox30,textbox11,textbox72,textbox65,textbox27,textbox21,textbox57,textbox9,textbox4,textbox16,textbox78,textbox80,textbox12,textbox25,textbox36,RBP,Pulse,LBP,Icd9CodesTxt,textbox61,textbox41,textbox6,ExerciseProtocol,ExerciseStage,Time,ExerciseMets,RatePressure,HeartRate1Min,textbox170,textbox166,textbox162,TermMeds,textbox158,textbox154,textbox146,textbox142,textbox138,textbox114,textbox115,textbox110,textbox111,textbox106,textbox107,textbox102,textbox103,NlLowEf,textbox65,textbox61,textbox57,textbox93,textbox89,textbox85,textbox81,textbox77,textbox73,textbox69,AerobicCapacity,textbox49,textbox45,IschemicResponse,textbox41,textbox37,textbox33,textbox21,textbox18,textbox15,textbox12,textbox4,textbox3,textbox9,DateTxt
>> Nuclear Stress Imaging Report,"Palms Office
>> The whole report is a medical record which I can't show you - however -
>> it has still lost all of it's formatting. I'm not sure what I'm doing
>> wrong here.
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:%23$jdHVvNGHA.1716@.TK2MSFTNGP10.phx.gbl...
>> The other issue might be that he is getting the CSV in Unicode instead
>> of ASCII.
>> Here is an example of a Jump to URL link I use. This causes Excel to
>> come up with the data in a separate window:
>> ="javascript:void(window.open('" & Globals!ReportServerUrl &
>> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
>> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>> If you don't want to have it appear in a new window then do this in
>> jump to URL:
>> =Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
>> Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
>> Note the rc:Encoding that is what causes the CSV to come up in ASCII.
>> Excel does not know how to handle unicode and puts all the data in a
>> single column. With ASCII, Excel will automatically split the data out
>> into multiple columns.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Wei Lu" <t-weilu@.online.microsoft.com> wrote in message
>> news:r6JxVfsNGHA.2336@.TK2MSFTNGXA01.phx.gbl...
>> Hi David,
>> Welcome to use MSDN Managed Newsgroup Support.
>> From your description, my understanding of this issue is: You want to
>> export the report to CSV with tab delimited. If I misunderstood your
>> concern, please feel free to point it out.
>> Reporting Services can not do it directly. Reporting Services only
>> could
>> export the CSV with comma delimited. I think the best way is to do it
>> in
>> Excel. You can create a new worksheet in Excel
>> 1. Import the data from the CSV file and select the Delimited
>> option
>> at the first step and select Comma as the Delimiter.
>> 2. After import from the CSV file, you can save it as the
>> Text(Tab
>> Delimited) type.
>>
>> Hope this will be helpful.
>> Sincerely yours,
>> Wei Lu
>> Microsoft Online Partner Support
>> Get Secure! - www.microsoft.com/security
>> =====================================================>> 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.
>>
>>
>>
>|||One thing that might work for you but I haven't done is to use XML output
and then provide your own xslt to get a straight ascii representation of
your report.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<drchips@.nospam.nospam> wrote in message
news:etrP63IOGHA.2916@.tk2msftngp13.phx.gbl...
> It a medical diagnosis report. I need a straight ascii dump that retains
> it's formatting as much as possible (Carriage returns - hopefully some
> positioning by using spaces etc. )
> Is there another format besides csv that can give me this? This
> information needs to be posted to a central EMR repository for patient
> data and no meta info for formatting is accepted by the system.
> The way I'm sort of handling it now is to create an mshtml file - write a
> bat file to launch it from my app. At that point - a transcriptionist has
> to stick her cursor in the browser - hit Cntrl-A - Cntrl C - close the
> browser - switch to her EMR interface - hit Cntrl V and save it. This
> method doesn't bring any of the html tags over and retains some
> formatting.
> I need to be able to eliminate the transcriptionists intervention in this
> so she can focus on all the other work she has to do. Any suggestions? (I
> can automate the interface between the systems with no problem - I just
> need the ASCII dump)
> Thanks for all your input so far.
> David
>
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:ec90zo9NGHA.3936@.TK2MSFTNGP12.phx.gbl...
>> _blank is definitely not mime type. That had to do with the frame to show
>> in the browser.
>> What you will get back is comma separated variable. Looking at what you
>> posted it looks like that is what you go back. If you do not want comma
>> separated variables then don't use CSV export.
>> Also, the reason you are getting back all the textbox information is
>> because your report has multiple textboxes above the table. What I do is
>> add additional table header rows, merge the cells and put my titles etc
>> there.
>> While working it out I suggest using a very clean report (nothing but
>> your table on it).
>> What formatting are you expecting? CSV puts all the fields for a row on
>> one line separated by commas. The CSV is great for getting data into
>> Excel.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> <drchips@.nospam.nospam> wrote in message
>> news:OoJ5nc9NGHA.3576@.TK2MSFTNGP15.phx.gbl...
>> I'm doing this in C#.
>> I wasn't sure what parameter you set to "_blank" so I set the mimetype
>> to it. Here's my code snippet:
>> case _rsTypeCSV:
>> fext = "csv";
>> ReportFormat = "CSV";
>> encoding = "ASCII";
>> mimeType = "_blank";
>> break;
>> }
>> results = rs.Render(ReportName, ReportFormat, historyID, deviceInfo,
>> parameters, credentials, showHide, out encoding, out mimeType, out
>> reportHistoryParameters, out warnings, out streamIDs);
>>
>> I'm still getting back:
>> textbox1,textbox47,textbox30,textbox11,textbox72,textbox65,textbox27,textbox21,textbox57,textbox9,textbox4,textbox16,textbox78,textbox80,textbox12,textbox25,textbox36,RBP,Pulse,LBP,Icd9CodesTxt,textbox61,textbox41,textbox6,ExerciseProtocol,ExerciseStage,Time,ExerciseMets,RatePressure,HeartRate1Min,textbox170,textbox166,textbox162,TermMeds,textbox158,textbox154,textbox146,textbox142,textbox138,textbox114,textbox115,textbox110,textbox111,textbox106,textbox107,textbox102,textbox103,NlLowEf,textbox65,textbox61,textbox57,textbox93,textbox89,textbox85,textbox81,textbox77,textbox73,textbox69,AerobicCapacity,textbox49,textbox45,IschemicResponse,textbox41,textbox37,textbox33,textbox21,textbox18,textbox15,textbox12,textbox4,textbox3,textbox9,DateTxt
>> Nuclear Stress Imaging Report,"Palms Office
>> The whole report is a medical record which I can't show you - however -
>> it has still lost all of it's formatting. I'm not sure what I'm doing
>> wrong here.
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:%23$jdHVvNGHA.1716@.TK2MSFTNGP10.phx.gbl...
>> The other issue might be that he is getting the CSV in Unicode instead
>> of ASCII.
>> Here is an example of a Jump to URL link I use. This causes Excel to
>> come up with the data in a separate window:
>> ="javascript:void(window.open('" & Globals!ReportServerUrl &
>> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
>> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>> If you don't want to have it appear in a new window then do this in
>> jump to URL:
>> =Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
>> Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
>> Note the rc:Encoding that is what causes the CSV to come up in ASCII.
>> Excel does not know how to handle unicode and puts all the data in a
>> single column. With ASCII, Excel will automatically split the data out
>> into multiple columns.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Wei Lu" <t-weilu@.online.microsoft.com> wrote in message
>> news:r6JxVfsNGHA.2336@.TK2MSFTNGXA01.phx.gbl...
>> Hi David,
>> Welcome to use MSDN Managed Newsgroup Support.
>> From your description, my understanding of this issue is: You want to
>> export the report to CSV with tab delimited. If I misunderstood your
>> concern, please feel free to point it out.
>> Reporting Services can not do it directly. Reporting Services only
>> could
>> export the CSV with comma delimited. I think the best way is to do it
>> in
>> Excel. You can create a new worksheet in Excel
>> 1. Import the data from the CSV file and select the Delimited
>> option
>> at the first step and select Comma as the Delimiter.
>> 2. After import from the CSV file, you can save it as the
>> Text(Tab
>> Delimited) type.
>>
>> Hope this will be helpful.
>> Sincerely yours,
>> Wei Lu
>> Microsoft Online Partner Support
>> Get Secure! - www.microsoft.com/security
>> =====================================================>> 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.
>>
>>
>>
>|||That would be great.
What is the name of that library?
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:e0wOQnKOGHA.2624@.TK2MSFTNGP12.phx.gbl...
> CSV does not do what you want. I am not aware of a straight ASCII output.
> You could do html if this is from your own app and then strip the html
> tags out (the .net framework has a library that would allow that).
> Bruce Loehle-Conger
>
> <drchips@.nospam.nospam> wrote in message
> news:etrP63IOGHA.2916@.tk2msftngp13.phx.gbl...
>> It a medical diagnosis report. I need a straight ascii dump that retains
>> it's formatting as much as possible (Carriage returns - hopefully some
>> positioning by using spaces etc. )
>> Is there another format besides csv that can give me this? This
>> information needs to be posted to a central EMR repository for patient
>> data and no meta info for formatting is accepted by the system.
>> The way I'm sort of handling it now is to create an mshtml file - write a
>> bat file to launch it from my app. At that point - a transcriptionist
>> has to stick her cursor in the browser - hit Cntrl-A - Cntrl C - close
>> the browser - switch to her EMR interface - hit Cntrl V and save it.
>> This method doesn't bring any of the html tags over and retains some
>> formatting.
>> I need to be able to eliminate the transcriptionists intervention in this
>> so she can focus on all the other work she has to do. Any suggestions?
>> (I can automate the interface between the systems with no problem - I
>> just need the ASCII dump)
>> Thanks for all your input so far.
>> David
>>
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:ec90zo9NGHA.3936@.TK2MSFTNGP12.phx.gbl...
>> _blank is definitely not mime type. That had to do with the frame to
>> show in the browser.
>> What you will get back is comma separated variable. Looking at what you
>> posted it looks like that is what you go back. If you do not want comma
>> separated variables then don't use CSV export.
>> Also, the reason you are getting back all the textbox information is
>> because your report has multiple textboxes above the table. What I do is
>> add additional table header rows, merge the cells and put my titles etc
>> there.
>> While working it out I suggest using a very clean report (nothing but
>> your table on it).
>> What formatting are you expecting? CSV puts all the fields for a row on
>> one line separated by commas. The CSV is great for getting data into
>> Excel.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> <drchips@.nospam.nospam> wrote in message
>> news:OoJ5nc9NGHA.3576@.TK2MSFTNGP15.phx.gbl...
>> I'm doing this in C#.
>> I wasn't sure what parameter you set to "_blank" so I set the mimetype
>> to it. Here's my code snippet:
>> case _rsTypeCSV:
>> fext = "csv";
>> ReportFormat = "CSV";
>> encoding = "ASCII";
>> mimeType = "_blank";
>> break;
>> }
>> results = rs.Render(ReportName, ReportFormat, historyID, deviceInfo,
>> parameters, credentials, showHide, out encoding, out mimeType, out
>> reportHistoryParameters, out warnings, out streamIDs);
>>
>> I'm still getting back:
>> textbox1,textbox47,textbox30,textbox11,textbox72,textbox65,textbox27,textbox21,textbox57,textbox9,textbox4,textbox16,textbox78,textbox80,textbox12,textbox25,textbox36,RBP,Pulse,LBP,Icd9CodesTxt,textbox61,textbox41,textbox6,ExerciseProtocol,ExerciseStage,Time,ExerciseMets,RatePressure,HeartRate1Min,textbox170,textbox166,textbox162,TermMeds,textbox158,textbox154,textbox146,textbox142,textbox138,textbox114,textbox115,textbox110,textbox111,textbox106,textbox107,textbox102,textbox103,NlLowEf,textbox65,textbox61,textbox57,textbox93,textbox89,textbox85,textbox81,textbox77,textbox73,textbox69,AerobicCapacity,textbox49,textbox45,IschemicResponse,textbox41,textbox37,textbox33,textbox21,textbox18,textbox15,textbox12,textbox4,textbox3,textbox9,DateTxt
>> Nuclear Stress Imaging Report,"Palms Office
>> The whole report is a medical record which I can't show you - however -
>> it has still lost all of it's formatting. I'm not sure what I'm doing
>> wrong here.
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:%23$jdHVvNGHA.1716@.TK2MSFTNGP10.phx.gbl...
>> The other issue might be that he is getting the CSV in Unicode instead
>> of ASCII.
>> Here is an example of a Jump to URL link I use. This causes Excel to
>> come up with the data in a separate window:
>> ="javascript:void(window.open('" & Globals!ReportServerUrl &
>> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
>> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>> If you don't want to have it appear in a new window then do this in
>> jump to URL:
>> =Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
>> Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
>> Note the rc:Encoding that is what causes the CSV to come up in ASCII.
>> Excel does not know how to handle unicode and puts all the data in a
>> single column. With ASCII, Excel will automatically split the data out
>> into multiple columns.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Wei Lu" <t-weilu@.online.microsoft.com> wrote in message
>> news:r6JxVfsNGHA.2336@.TK2MSFTNGXA01.phx.gbl...
>> Hi David,
>> Welcome to use MSDN Managed Newsgroup Support.
>> From your description, my understanding of this issue is: You want to
>> export the report to CSV with tab delimited. If I misunderstood your
>> concern, please feel free to point it out.
>> Reporting Services can not do it directly. Reporting Services only
>> could
>> export the CSV with comma delimited. I think the best way is to do it
>> in
>> Excel. You can create a new worksheet in Excel
>> 1. Import the data from the CSV file and select the Delimited
>> option
>> at the first step and select Comma as the Delimiter.
>> 2. After import from the CSV file, you can save it as the
>> Text(Tab
>> Delimited) type.
>>
>> Hope this will be helpful.
>> Sincerely yours,
>> Wei Lu
>> Microsoft Online Partner Support
>> Get Secure! - www.microsoft.com/security
>> =====================================================>> 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.
>>
>>
>>
>>
>|||Here are several ideas for you:
http://weblogs.asp.net/rosherove/archive/2003/05/13/6963.aspx
http://www.freevbcode.com/ShowCode.asp?ID=110
This article gives some ideas plus how it can't do everything:
http://groups.google.com/group/microsoft.public.dotnet.languages.vb/browse_thread/thread/27332781e3126287/ceebf4e075a5a8ed%23ceebf4e075a5a8ed?sa=X&oi=groupsr&start=0&num=2
from the article:
>>>>>>>
How To Strip HTML Tags :
Well, the short answer is Regular Expressions, for example:
'(assuming that you have imported System.Text.RegularExpressions)
Dim oRegEx as Regex = RegEx("<[^>]*>")
Dim myNewString as string = oRegEx.Replace(myOldString, "")
However, that won't get you anywhere close to all the way. Even after you
entirely strip out all the <htmltags> you may be left with HTML-encoded
strings such as which you will also want to parse. These are easily
handled with
System.Web.HTTPUtility.HTMLDecode()
>>>>>>
You might be better off with XML and XSLT as I mentioned in another post.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<drchips@.nospam.nospam> wrote in message
news:O1NTj6WOGHA.2128@.TK2MSFTNGP10.phx.gbl...
> That would be great.
> What is the name of that library?
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:e0wOQnKOGHA.2624@.TK2MSFTNGP12.phx.gbl...
>> CSV does not do what you want. I am not aware of a straight ASCII output.
>> You could do html if this is from your own app and then strip the html
>> tags out (the .net framework has a library that would allow that).
>> Bruce Loehle-Conger
>>
>> <drchips@.nospam.nospam> wrote in message
>> news:etrP63IOGHA.2916@.tk2msftngp13.phx.gbl...
>> It a medical diagnosis report. I need a straight ascii dump that
>> retains it's formatting as much as possible (Carriage returns -
>> hopefully some positioning by using spaces etc. )
>> Is there another format besides csv that can give me this? This
>> information needs to be posted to a central EMR repository for patient
>> data and no meta info for formatting is accepted by the system.
>> The way I'm sort of handling it now is to create an mshtml file - write
>> a bat file to launch it from my app. At that point - a transcriptionist
>> has to stick her cursor in the browser - hit Cntrl-A - Cntrl C - close
>> the browser - switch to her EMR interface - hit Cntrl V and save it.
>> This method doesn't bring any of the html tags over and retains some
>> formatting.
>> I need to be able to eliminate the transcriptionists intervention in
>> this so she can focus on all the other work she has to do. Any
>> suggestions? (I can automate the interface between the systems with no
>> problem - I just need the ASCII dump)
>> Thanks for all your input so far.
>> David
>>
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:ec90zo9NGHA.3936@.TK2MSFTNGP12.phx.gbl...
>> _blank is definitely not mime type. That had to do with the frame to
>> show in the browser.
>> What you will get back is comma separated variable. Looking at what you
>> posted it looks like that is what you go back. If you do not want comma
>> separated variables then don't use CSV export.
>> Also, the reason you are getting back all the textbox information is
>> because your report has multiple textboxes above the table. What I do
>> is add additional table header rows, merge the cells and put my titles
>> etc there.
>> While working it out I suggest using a very clean report (nothing but
>> your table on it).
>> What formatting are you expecting? CSV puts all the fields for a row on
>> one line separated by commas. The CSV is great for getting data into
>> Excel.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> <drchips@.nospam.nospam> wrote in message
>> news:OoJ5nc9NGHA.3576@.TK2MSFTNGP15.phx.gbl...
>> I'm doing this in C#.
>> I wasn't sure what parameter you set to "_blank" so I set the mimetype
>> to it. Here's my code snippet:
>> case _rsTypeCSV:
>> fext = "csv";
>> ReportFormat = "CSV";
>> encoding = "ASCII";
>> mimeType = "_blank";
>> break;
>> }
>> results = rs.Render(ReportName, ReportFormat, historyID, deviceInfo,
>> parameters, credentials, showHide, out encoding, out mimeType, out
>> reportHistoryParameters, out warnings, out streamIDs);
>>
>> I'm still getting back:
>> textbox1,textbox47,textbox30,textbox11,textbox72,textbox65,textbox27,textbox21,textbox57,textbox9,textbox4,textbox16,textbox78,textbox80,textbox12,textbox25,textbox36,RBP,Pulse,LBP,Icd9CodesTxt,textbox61,textbox41,textbox6,ExerciseProtocol,ExerciseStage,Time,ExerciseMets,RatePressure,HeartRate1Min,textbox170,textbox166,textbox162,TermMeds,textbox158,textbox154,textbox146,textbox142,textbox138,textbox114,textbox115,textbox110,textbox111,textbox106,textbox107,textbox102,textbox103,NlLowEf,textbox65,textbox61,textbox57,textbox93,textbox89,textbox85,textbox81,textbox77,textbox73,textbox69,AerobicCapacity,textbox49,textbox45,IschemicResponse,textbox41,textbox37,textbox33,textbox21,textbox18,textbox15,textbox12,textbox4,textbox3,textbox9,DateTxt
>> Nuclear Stress Imaging Report,"Palms Office
>> The whole report is a medical record which I can't show you -
>> however - it has still lost all of it's formatting. I'm not sure what
>> I'm doing wrong here.
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:%23$jdHVvNGHA.1716@.TK2MSFTNGP10.phx.gbl...
>> The other issue might be that he is getting the CSV in Unicode
>> instead of ASCII.
>> Here is an example of a Jump to URL link I use. This causes Excel to
>> come up with the data in a separate window:
>> ="javascript:void(window.open('" & Globals!ReportServerUrl &
>> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
>> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>> If you don't want to have it appear in a new window then do this in
>> jump to URL:
>> =Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
>> Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
>> Note the rc:Encoding that is what causes the CSV to come up in
>> ASCII. Excel does not know how to handle unicode and puts all the
>> data in a single column. With ASCII, Excel will automatically split
>> the data out into multiple columns.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Wei Lu" <t-weilu@.online.microsoft.com> wrote in message
>> news:r6JxVfsNGHA.2336@.TK2MSFTNGXA01.phx.gbl...
>>> Hi David,
>>>
>>> Welcome to use MSDN Managed Newsgroup Support.
>>>
>>> From your description, my understanding of this issue is: You want
>>> to
>>> export the report to CSV with tab delimited. If I misunderstood your
>>> concern, please feel free to point it out.
>>>
>>> Reporting Services can not do it directly. Reporting Services only
>>> could
>>> export the CSV with comma delimited. I think the best way is to do
>>> it in
>>> Excel. You can create a new worksheet in Excel
>>>
>>> 1. Import the data from the CSV file and select the Delimited
>>> option
>>> at the first step and select Comma as the Delimiter.
>>>
>>> 2. After import from the CSV file, you can save it as the
>>> Text(Tab
>>> Delimited) type.
>>>
>>>
>>> Hope this will be helpful.
>>>
>>> Sincerely yours,
>>>
>>> Wei Lu
>>> Microsoft Online Partner Support
>>>
>>> Get Secure! - www.microsoft.com/security
>>> =====================================================>>> 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.
>>>
>>
>>
>>
>>
>>
>

Monday, March 19, 2012

Current Date help

Friday, February 24, 2012

Cube deployment

Cube deployment

Friday, February 17, 2012

CSV import into MS SQL.

CSV import into MS SQL.

CSV import into MS SQL.

CSV files

CSV files

CSV Export delimited not fixed

Tuesday, February 14, 2012

CSV Export delimited not fixed

CSV Export

Cursors vs. Queries

Blog Archive

About Me