DTS Export Table Rows --> Text File 2

[JohnBates]

Hi experts,

(Sorry for the cross-post; I submitted this to the SQL Programming forum before discovering there is a special section for DTS.)

This is SQL Server 2000 Standard.

I want to export data to a .txt file and have all the data be contiguos, together with no spaces.

Here is a sample table layout:
Col Name Data Type Value
Cust# char 11 00000052314
TransType char 1 A
DivCode char 2 48

(Note that each value is the max length allowed for that column... This is actually the way my input data is.)


** The destination text file that gets generated by my DTS package contains spaces between the data.... for ex
00000052314 A 48

** What I need is....no spaces
00000052314A48

I start with a blank .txt file.

Have I specified something wrong in the 'delimited by' stuff OR some other silly mistake?
Also, there is a button for Destination that says 'Populate from Columns' - Have tried it and still get the spaces.

Thanks for any help you can give,
John

 

[tb]

Let me ask you the question again ...

Do you use the Text File (Destination) ?

In the Text File Properties (Text File (Destination) ... Properties) ... What is selected? My guess is the selected option there is Fixed Field.


I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[JohnBates]

Thanks tb,

Yes, I'm using the Destination Text file.

I think I have tried both Fixed field and Delimited.
But with delimited, what would the delimeter char be? The source is a SQL table.

thanks, John

 

[tb]

You choose what the delimeter will be in the Destination ie the file ... unfortunately it is a bit limiting.
Only:
=====
,
;
TAB
|



I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[JohnBates]

Thanks tb,

Maybe I dont understand what you are saying.

** What I need is....no spaces
00000052314A48

I dont want any special char inserted into my output.

Think I tried TAB already.

I'm going to need some help on this one.

John

 

[john76]

Just an option to consider...

Use an Execute SQL task to create an output table that concatinates the output fields into a single field, something like this:

SELECT LTrim(RTrim(CONVERT(varchar, Cust#))) + LTrim(RTrim(CONVERT(varchar, TransType))) + LTrim(RTrim(CONVERT(varchar, DivCode)))
  INTO  OutputTable
  FROM  MyTable

Then bcp the results out from the table.
Messy perhaps, but should work.

HTH,
John

 

[john76]

oops, don't forget to assign the field a name of some sort.

John

 

[JohnBates]

thanks john76,

I'll give that a try.
John

 

[tb]

Another option is to have an AxtiveX script task that will read and replace all the delimeters after the export.
Depending on how big the output file is - this might prove to be a bit time consuming

I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[JohnBates]

tb I like your suggestion because I already have steps that generate the text file.

I now just need to remove the spaces in the .txt file
changing 00000052314 A 48

to 00000052314A48

Can someone give me a tip on what to do inside the ActiveXscript task?

thanks, John

 

[tb]

Something like this ..

Dim fso ' File System Object
Dim theFile ' TextStream
Dim sFileText 'String

Set fso = CreateObject("Scripting.FileSystemObject")
Set theFile = fspenTextFile("FilePath/FileName", ForReading, False)

sFileText = Replace(theFile.ReadAll, "Delimeter", "", 1, , vbTextCompare)

theFile.Close

Set theFile = fspenTextFile("FilePath/FileName"", ForWriting, False)

theFile.Write sFileText

theFile.Close

This might even prove to me fast (did a small test)

Hope this helps
Good Luck




I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[JohnBates]

ok I've changed strategy a bit.

I'm trying john76's method....

SELECT LTrim(RTrim(CONVERT(varchar, TransType)))+
LTrim(RTrim(CONVERT(varchar, GMDiv)))+
LTrim(RTrim(CONVERT(varchar, BAC)))

INTO EnrollChanges.EnrollmentChangesOut
FROM EnrollmentChanges

My hang-up now is how to specify/qualify the column name in the INTO clause - I get an error of "no col was specified for column 1"

Thanks, John

 

[JohnBates]

OK, I'm trying the ActiveX Task becuase I need to get this moving in a positive direction.

I'm getting a VBScript error "Invalid procedure call or argument" at this stmt:

Set theFile = fspenTextFile("\\GM\C$\sys\paragon\gm\Files\Outbound\ACB_EnrollmentChanges.txt", ForReading, False)

The path is correct, the .txt file does exist.

I may need to post this in the ActiveX forum.

John

 

[john76]

I hope you're getting this taken care of one way or another, but to answer your question, this should work.

Depending on syntax preferences:

SELECT LTrim(RTrim(CONVERT(varchar, Cust#))) + LTrim(RTrim(CONVERT(varchar, TransType))) + LTrim(RTrim(CONVERT(varchar, DivCode))) [COLOR=red]AS FieldName[/color]
  INTO  OutputTable
  FROM  MyTable

or...

SELECT [COLOR=red]FieldName =[/color] LTrim(RTrim(CONVERT(varchar, Cust#))) + LTrim(RTrim(CONVERT(varchar, TransType))) + LTrim(RTrim(CONVERT(varchar, DivCode)))
  INTO  OutputTable
  FROM  MyTable

Then from an "Execute Process" task:
Win32 Process: bcp
Paramaters: <database>..OutputTable out "<path to text file>.<text file name>.txt" -T -c

HTH,
John

 

[john76]

(ahem) Ignore the statement on the Parameters line.

 

[JohnBates]

Thanks john76 ! Now we're getting somewhere.

I'm using the SELECT.... AS FieldName method.

Can I have any control over the attributes of the column in the out file? It is varchar 240, yet the input data is much less tahn 240, it is 80 length.

I tried AS FieldName char(80) but gor a syntax error.

Can I assign a datatype and lenght?

Thanks very much for your patience today. John

 

[john76]

No problem on the help,

Try:

SELECT FieldName = [COLOR=red]CONVERT(char(80), ([/color]
    LTrim(RTrim(CONVERT(varchar, Cust#))) +
    LTrim(RTrim(CONVERT(varchar, TransType))) +
    LTrim(RTrim(CONVERT(varchar, DivCode)))
[COLOR=red]  )[/color]
  INTO  OutputTable
  FROM  MyTable

Essentially, you are building the "sentance" from the individual fields, then converting the entire "sentance" to a fixed length form.
Cheers,
John

 

[john76]

Hope this gets it for you, I'm taking off for the day.

Good luck!
John

 

[JohnBates]

Thanks very much tb and john76! You made my day alot better and this project is working well now.

If you have read my TT profile you know that I'm coming from a different technical background... and I'm older too.

I just wish I knew half of what you guys know.

John