Data export from SQl in CSV format

Hi
I want to export data out of SQL database using a query into a Comma Delimited(CSV) file. Some one suggested that I can use BCP.exe, I am not sure how to use it properly.
Can some please suggest how to do it? I am not a SQL DBA & is struggling with this.It's much easier to do this using the DTS Data Export under the Tools menu
in the Enterprise Manager. Pick Text File as your destination. The default
format is CSV with embedded double quotes correctly handled.
If you use BCP, you'll have to deal with the embedded double quotes in each
column where that's an possibility, using a TSQL function such as REPLACE().
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"singh" <anonymous@.discussions.microsoft.com> wrote in message
news:6ECE17F7-1A1D-4526-A94E-DCD97F90E04C@.microsoft.com...
> Hi
> I want to export data out of SQL database using a query into a Comma
Delimited(CSV) file. Some one suggested that I can use BCP.exe, I am not
sure how to use it properly.
> Can some please suggest how to do it? I am not a SQL DBA & is struggling
with this.
>|||You can also use command line osql.exe and there is documentation in Books
on Line on the syntax.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Rand;
Could you please elaborate? Are you talking about the -s command line option
of osql.exe?
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Rand Boyd [MS]" <rboyd@.onlinemicrosoft.com> wrote in message
news:4roszLKqDHA.2604@.cpmsftngxa06.phx.gbl...
> You can also use command line osql.exe and there is documentation in Books
> on Line on the syntax.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>|||The -s parameter does allow you to define a column delimiter. The default,
I believe, is a tab.
Rand
This posting is provided "as is" with no warranties and confers no rights.

data conversion in during DTS import

Hi,
I have a text file in which one field is a date but in
file it is in '20030818' format and I am tring to import
that file to a table which has a column datetime.
is it possible to convert '20030818' to datetime during
DTS import?
if yes please let me know how ?
Thanks.use
1 .vbscript tranformation(activex)
2 .select convert(datetime, '20030818')
3. dts datetime conversion facility
rohan
>--Original Message--
>Hi,
>I have a text file in which one field is a date but in
>file it is in '20030818' format and I am tring to import
>that file to a table which has a column datetime.
>is it possible to convert '20030818' to datetime during
>DTS import?
>if yes please let me know how ?
>Thanks.
>.
>|||use
1 .vbscript tranformation(activex)
2 .select convert(datetime, '20030818')
3. dts datetime conversion facility
rohan
>--Original Message--
>Hi,
>I have a text file in which one field is a date but in
>file it is in '20030818' format and I am tring to import
>that file to a table which has a column datetime.
>is it possible to convert '20030818' to datetime during
>DTS import?
>if yes please let me know how ?
>Thanks.
>.
>|||use
1 .vbscript tranformation(activex)
2 .select convert(datetime, '20030818')
3. dts datetime conversion facility
rohan
>--Original Message--
>Hi,
>I have a text file in which one field is a date but in
>file it is in '20030818' format and I am tring to import
>that file to a table which has a column datetime.
>is it possible to convert '20030818' to datetime during
>DTS import?
>if yes please let me know how ?
>Thanks.
>.
>

Data Conversion

I have single letters in a Flat file and it is in the string format,but now i want to convert it in to int format.I have tried doing this by SSIS but it is not working.
I used data conversion and copy column transformation.

I got a error message.

it automatically correct the meta data mismatch. But when i run the package, it gives the following error message.

Error: 0xC0209029 at Data Flow Task, Data Conversion [75]: The "output column "ReceiptType" (112)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "ReceiptType" (112)" specifies failure on error. An error occurred on the specified object of the specified component.

This is just one error message.

Please can you tell me the steps to do this correctly.

Also I need to know how to run a SSIS package through command line?

Thanks

Nishan

Did you say you were trying to convert letters to int? Can't do that. Is there any more info?|||

I have a flat file which has string types of data. I want to convert them into int type before I load them into destination table. I used data convertion and derived column but neither worked for me.

Ex;

Source file (String type);

column 1

B

B

C

C

Destination file format should look like this. B - > 1 , C - > 2

Destination File ( int Type);

Column 1

1

1

2

2

Can you explain the setps to do this conversion using SSIS

Thanks

|||The Derived Column task should work for you: Use the conditional operator|||Shamen,
Please keep your posts together in one thread.

This can be done in a lookup transformation. Use the following SQL for the lookup:

select "B",1
union all
select "C",2
union all
select "D",3"
....

Then hook up your source to the lookup component, selecting the second column as the return value. Coming out of the lookup component, you'll have the number associated with the letter and can then go into a flat file destination.|||

Thanks Pill and SQL Pro...I will try..at the same time I want to convert the data type too.

Yes I will keep all my posting together in one thread...

Also I have another question...Once I create the package can I run it on command line? How can I do that?

Thanks

|||Thanks I will try|||

shamen wrote:

Also I have another question...Once I create the package can I run it on command line? How can I do that?

DTEXEC runs packages from the command line.

|||

Thanks....Still I'm having a problem with data conversion...I tried using both derived column and data conversion transformation....but still no luck...

This is the error message I got when I tried with data conversion transformation.

SSIS package "Package.dtsx" starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x402090DC at Data Flow Task, Flat File Source [1]: The processing of file "C:\p4_coventry1666_DEV02\Docs\ThirdParty\EPICWare\EPICWARE Flat File.txt" has started.

Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.

Error: 0xC02020C5 at Data Flow Task, Data Conversion [75]: Data conversion failed while converting column "ReceiptType" (67) to column "ReceiptType" (112). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Error: 0xC0209029 at Data Flow Task, Data Conversion [75]: The "output column "ReceiptType" (112)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "ReceiptType" (112)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component "Data Conversion" (75) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Error: 0xC02020C4 at Data Flow Task, Flat File Source [1]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0209029.

Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x402090DD at Data Flow Task, Flat File Source [1]: The processing of file "C:\p4_coventry1666_DEV02\Docs\ThirdParty\EPICWare\EPICWARE Flat File.txt" has ended.

Information: 0x402090DF at Data Flow Task, OLE DB Destination [9]: The final commit for the data insertion has started.

Information: 0x402090E0 at Data Flow Task, OLE DB Destination [9]: The final commit for the data insertion has ended.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (9)" wrote 0 rows.

Task failed: Data Flow Task

Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package.dtsx" finished: Failure.

The program '[4360] Package.dtsx: DTS' has exited with code 0 (0x0).

|||

You can use the CODEPOINT function to return a numeric value for a character. That means you can use an expression in a Derived Column transform to do the work:

Expression: CODEPOINT(UPPER(YourColumn))-64

(the 64 is there because CODEPOINT("A") = 65)

This will work nicely where you have a one-character value to transform.

You can craft a more elaborate expression for multiple characters if required.

Dylan.

|||

Thanks Dylan...But at the same time I want to convert data type from String to Int.....

I'm supposed to get specific value for specific letters. As a example for letter B I'm supposed to get 1.

B - > 1

C- > 2

But first of all I have to figure it out how to convert data from string to Int.

Thanks

|||

Use the Lookup as Phil suggested. It is by far the simplest solution to this problem.

|||

OK, but as pointed out above, you are on a fruitless quest.

A letter is not a number, so it cannot be converted to an int.

What you are saying is like asking how mainy legs a daisy has, or how many teeth in an elephant's trunk. It just doesn't make sense to talk about converting a letter into an int. By definition an int is a number, which is not a letter.

That said, what you CAN talk about is removing the column with the letter and replacing it with a corresponding column containing an int.

Then it is just a matter of defining the rule by which you convert your letter into a number.

One such rule is to say there is a specific mapping. So B=2, F=61, R=12.

In this case the select statement above is a good way of performing that mapping. However you can't deal with unknown values. This is like saying "African elephants have 6 teeth in their trunk. Asian elephants have 9 teeth." Using this rule, you can answer the question "How many teeth in an African elephant's trunk?" but not "How many teeth in an Australian elephant's trunk?" because you have no information about Australian elephants.

The other approach is to say there is a formula, so B=2, C=3, D=4, E=5. In this case you can say that even if you have never used an "F", if one were to be used for some reason then it would be equal to 6. That is where functions like CODEPOINT can help.

This is like saying "All daisys have twice as many legs as petals." Now you can look at any daisy, and answer the question "How many legs on my daisy?"

Notice that what we have done is create two different methods for answering questions that do not make sense in the real world. And likewise, we have revealed two different methods for turning a letter into an int, even though doing so does not make sense.

You should stop thinking about turning your letter into an int, and focus instead on dropping the column and replacing it with a derived int column. You are not converting anything, you are transforming and replacing. If it looks like "converting" to your pointy-haired managers, then that's fine, but you will know better on the inside.

Good luck,

Dylan.

|||

jwelch wrote:

Use the Lookup as Phil suggested. It is by far the simplest solution to this problem.

That would depend on context and maintainability requirements.

If the letter value can be derived by formula rather than by lookup, that would be more appropriate in my opinion. If you are hardcoding values in a lookup, then you are creating a potential maintenance problem.

In addition, creating a complex union query where there are only ever two values to look up would be overcomplicated when an IF expression would be simpler.

Without knowing more about the requirements and implementation specifics, it is risky to talk about which solution is simplest. Normally I wouldn't care, but I've taken issue in this case because I've had to maintain a fair amount of work recently that was done using this "simplest" approach. Two years later, it has turned out to be a major pain.

|||

OK...Well I will try...

Thanks all

data conversion

Geographic data format (GDF) is used primarily for navigation systems. I was wondering if anyone was familiar with this format and if I can use either DTS or SSIS to import the GDF flat files into SQL?

TheWheel wrote:

Geographic data format (GDF) is used primarily for navigation systems. I was wondering if anyone was familiar with this format and if I can use either DTS or SSIS to import the GDF flat files into SQL?

Is the GDF file comma separated? Tab delimited?

Cyrstal Report and ASP- HEEEEELLLP!

Hello:

I am using ASP and Crystal report. I created a .rpt file on a dev server and I can access the report in pdf format using ASP.

But now I want to implement the same on production server. I copied the .rpt file and changed the database settings. The preview pane shows the new data. But when I call the report from ASP and convert it to pdf I get nothing. Just the report heading and no rows get selected.

I know there is data in the database, and I confirm that the db connection is ok.

Any clues?

Also why do we specify the
Set crtable = session("oRpt").Database.Tables.Item(1)
crtable.SetLogonInfo CStr(databaseServer), CStr(databaseTables), CStr(databaseUser), CStr(databasePassword)

in the ASP, when we have to specify the same credentials in the rpt file as well?

Is is adviced to save the report with data or without?

Your help will be greatly appreciated.
ThanksThe report is not to be saved with that data.If u save the report with the data the data will be not be refreshed each time while opening the report.

Set crtable = session("oRpt").Database.Tables.Item(1)
crtable.SetLogonInfo CStr(databaseServer), CStr(databaseTables), CStr(databaseUser), CStr(databasePassword)

These lines are written so that the report can log on to the data server.|||Hi

Iam going to include a crpt report in our existing asp application.
I dont have any idea to code for how to call and use crpt in asp file.

Pls, any one give me sample codings for the same.
mail id saravanakumar77@.yahoo.com

regards
Saravanakumar.D

CVS Export in ASCII format

For the CSV export problem, where excel opens data into one column, I
had to add a report link using the below code to fix the problem. Is
there a way to change the underlying export encoding in reporting
services?
="javascript:void(window.open(top.frames[0].frames[1].location.href.replace('Format=HTML4.0','Format=CSV&rc%3aEncoding=ASCII'),'_blank'))"There is for RS 2005. Not for RS 2000.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<slov1@.hotmail.com> wrote in message
news:1135297330.158158.120010@.g49g2000cwa.googlegroups.com...
> For the CSV export problem, where excel opens data into one column, I
> had to add a report link using the below code to fix the problem. Is
> there a way to change the underlying export encoding in reporting
> services?
> ="javascript:void(window.open(top.frames[0].frames[1].location.href.replace('Format=HTML4.0','Format=CSV&rc%3aEncoding=ASCII'),'_blank'))"
>