Change CSV file to XLS

Hi,

I want to automate my performance monitoring Reports(sql2000). So I need to import performance monitor output log file .CSV to a table. I feel converting this .CSV to .XLS is good way to importing the log thru DTS. Is there any command/ script to change the file type .CSV to .XLS, so that i can include this one into DTS Step.

thanksOriginally posted by krishna
Hi,

I want to automate my performance monitoring Reports(sql2000). So I need to import performance monitor output log file .CSV to a table. I feel converting this .CSV to .XLS is good way to importing the log thru DTS. Is there any command/ script to change the file type .CSV to .XLS, so that i can include this one into DTS Step.

thanks

Why can you not import the csv file? What advantages does importing a .XLS file provide?|||because, it is easy to import excel file than csv format.|||DTS can handle .CSV files just as easy as it can handle .XLS files that is why I was askng for advantages. Sorry I couldn't help.

Originally posted by krishna
because, it is easy to import excel file than csv format.|||I don't see any issues in handling .CSV or .XLS by Excel and SQL server DTS.|||what datasouce i should use ? Microsoft Text-Treiber(*.txt,*.csv)?
If i use this one as datasource for to import CSV file into table it is not converting datetime column. I was thinking of using Microsoft Excel for the datasource thats why i asked about converting csv to xls

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
>
>

Change column datatype "$2,000" to 2000

Hello,

How do I change the datatype of a column in a CSV file. Preferably in the select statement (apparently Cast, Convert, & Replace functions don't work when selecting from a CSV).

I have a page where users upload their CSV files to and then I use SQLBulkCopy to insert all the records into my sql table. The problem is with columns of money data.

EX: "$2,000" >> 2000

The CSV file interprets the "$2,000" as a string of text. How do I convert it to a decimal so I can insert it into my sql database?

The CSV doesn't interpret anything, CSV files have no data types, it's all just text. You can use a text editor to do a search and replace and remove all the dollar signs and periods if you wish. You should be able to set the SQL data type for currency as well. Or use a DTS job to change the data format instead of doing a bulk copy.

Jeff

|||

Hi Jeff,

As I said, users are uploading CSV files to my site where I use SQLBulkCopy to automatically upload the records to a SQL table. So that means no manual editing of the csv file and no using DTS.

Or can you do a "DTS job" via code? If so I've never done that before and a code example would be much appreciated.

You're right, I shouldn't have said CSV interprets it. I meant while reading from the CSV to a datatable, etc it is interpreted as string data.

Thanks a bunch,

|||

Moderators: please stop marking this post as resolved/answered. It is obvious Jeff's response is NOT the answer. Marking it so only removes the chance of my question actually being answered.

|||

You can probably trigger a DTS job via code, I've never looked at whether this could be done or how. You may need to parse the CSV file in code as it is uploladed, before the bulk copy, to strip the string portions out and format the currency as a number. I'm not sure if a SQLBulkCopy can change data types, I'm not familiar enough with it to say.

Jeff

PS: This isn't the answer either I'm afraid... :)