How do i add a carriage return in VBScript?
-
I'm querying a database and creating a textfile destination. instead of printing the entire row and changing to the new row on carriage return, i want to do a CR after each column of data. I'm assuming i have to do it as ascii, just not quite sure how. Any ideas?
-
OK, insert standard disclaimer about what you need this for and this not necessarily being the best solution..., but, I have done this in the past:
select a + char(13) + char(10) --carriage return & line feed
,b...
if character data, or convert to character and add CRLF, or insert a column between each:
select a, char(13)+char(10), b, char(13)+char(10)
Or use BCP and specify, or probably 10 other klugy things...
-
Generally speaking, a carriage return in VBScript i vbCrLf, ex.:
WScript.Echo "Hello" & vbCrLf & "World"
displays
Hello
World
I do hope this very short and general answer is somehow usefull.
/Niels Grove-Rasmussen
-
Thank you, gentlemen... both solutions worked for me.
Appreciate that information.
Ray
-
Ok, i have a new question... i'm using this ActiveX script in the DTS package to basically build a template report in a TXT file that can be generated automatically. right now i'm just pulling in 3 columns from the DB. When i try outputting those 3 columns to the Text file though, i am obviously doing something wrong because it looks incredibly wrong. I've take the SAME data to another table in SQL server and it transforms just fine. What am i missing? Here's what i'm doing in my script:
Function Main()
dim sCustomer, sStatus, sHEAT
sCustomer = "Account : " & DTSSource("Customer") & vbCrLf
sStatus = "Status : " & DTSSource("Status") & vbCrLf
sHEAT = "HEAT Ticket : " & DTSSource("HEAT") & vbCrLf
' MsgBox ( sCustomer )
' MsgBox ( sHEAT )
' MsgBox ( sStatus )
DTSDestination("Customer") = sCustomer
DTSDestination("Status") = sStatus
DTSDestination("HEAT") = sHeat
Main = DTSTransformStat_OK
End Function
Pretty straight forward, right? But instead of getting 3 rows of data, which is what i would expect, i get the following:
Account : CIBC
Status : THEAT
It basically looks like a bunch of extra data, but i've tried trimming the fields, etc, and get the same results. I assume I am just missing something about setting up text files for output?
Any ideas or help are appreciated.
Ray
-
'i am making a few assumptions about what you are doing here:
'here is what i would suggest (and should work with no problems when outputting to a text file):
'you can make it fancier by having the output file name 'dynamic', checking to see if the file exists first, etc
Dim fso '--file scripting object
Dim fout '--a text file
Dim rs
Dim cnn1 '--connection
Dim sql 'sql string, etc
Set fso = CreateObject("Scripting.FileSystemObject")
Set fout1 = fso.CreateTextFile("c:/test.txt")
Set cnn1 = CreateObject("adodb.Connection")
Set rs = CreateObject("adodb.recordset")
cnn1.connectionstring = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=XXdbname;Data Source=XXservername"
cnn1.open
Set rs = cnn1.execute("select Customer, Status, HEAT from XXXX_tablename_XXXX")
'my test sql "select User_Login as Customer, User_FirstNm as Status, User_LastNm as HEAT from users"
Do While Not rs.EOF
sCustomer = "Account : " & rs("Customer") & vbCrLf
sStatus = "Status : " & rs("Status") & vbCrLf
sHEAT = "HEAT Ticket : " & rs("HEAT") & vbCrLf
fout1.writeline sCustomer & sStatus & sHEAT
'just in case...
sCustomer = ""
sStatus = ""
sHEAT = ""
rs.movenext
Loop
rs.Close
Set rs = Nothing
'if you are done with the conn
cnn1.Close
Set cnn1 = Nothing
'if you are done with the text file here too
fout1.Close
Set fout1 = Nothing
Set fso = Nothing
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply