March 15, 2002 at 2:44 pm
Hi, All
I have a q for U.
I created text file that contains Header Info & Data & Trailer Info.
Problem is in the trailer info.
Trailer info is kust one line with lots of spaces on it. But I dont know why it creates extra lines(actually 2) after trailer info.
My Q is how can I delete extra lines?
I was research for this one and I came up with KILL or EOF etc..command but it did not work..
Any Idea..
Thx in advance
Jay
March 15, 2002 at 3:03 pm
How are you currently outputting?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 15, 2002 at 3:39 pm
Here is script.
Basically I create Header first and add Data text file to it. After that I add Trailer info again to that text file.
Here is script..
/*************************************/
Const ForReading=1, ForWriting=2, ForAppending=8
Dim fso, f, ReadDataFile, AppendToHeader,ReadTrailerFile,FinalFile
Dim dMonth, dDay, dTime,dMin, dFillStr, xLoop
Set fso = CreateObject("Scripting.FileSystemObject")
'Create Text File & Insert Header Info
Set f = fso.CreateTextFile("c:\testfile.txt", True)
Select Case Len(Month(Date))
Case "1"
dMonth = "0" & Month(Date)
Case Else
dMonth = Month(Date)
End Select
Select Case Len(Day(Date()))
Case "1"
dDay = "0" & Day(date())
Case Else
dDay = Day(date())
End Select
Select Case Right(Time(), 2)
Case "AM"
dTime = "0" & Left(Time(), 1)
Case Else
dTime = Left(Time(), 1) + 12
End Select
Select Case Len(Replace(Time(), ":", ""))
Case "8"
dMin = Mid(Replace(Time(), ":", ""), 2, 4)
Case "9"
dMin = Mid(Replace(Time(), ":", ""), 3, 4)
End Select
For xLoop = 1 To 815
dFillStr = dFillStr & " "
Next
f.WriteLine("STKOPTHDR " & YEAR(DATE( )) & dMonth & dDay & dTime & dMin & " XOP1105" & dFillStr)
f.Close
'Open Data File to read
Set fso=CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile("C:\Documents and Settings\jrhee\Desktop\xop1105.txt", ForReading)
ReadDataFile=f.ReadAll
'MsgBox ReadDataFile
'Open Header text file to append Data file
Set f = fso.OpenTextFile("C:\testfile.txt", ForAppending)
AppendToHeader=f.Write(ReadDataFile)
f.Close
'Open Trailer Text file before inset into Final file
Set fso=CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile("C:\Documents and Settings\jrhee\Desktop\TrailerInfo.txt", ForReading)
ReadTrailerFile=f.ReadAll
'Open text file(Header+Data) to insert Trailer info
Set f = fso.OpenTextFile("C:\testfile.txt", ForAppending)
FinalFile=f.Writeline(ReadTrailerFile)
f.Close
Set fso=Nothing
Set f=Nothing
Main = DTSTaskExecResult_Success
/***************************************/
Thx
Jay
March 15, 2002 at 5:24 pm
I believe you're getting that "extra line" every time you do f.close. The close must insert an extra linefeed character, although I can't put my finger on it. It doesn't show up in Word even if you show all non-printing characters. The line count is correct in Visual Studio, but the cursor is on the next line. Weird. If anyone knows how to get around this using the scripting object model, please post - I'd like to know too.
The more I use SQL, the less I need (or like) VB. If you absolutely have to have the exact lines with no blanks, and have SQL Server already (obviously) I'd dump the contents of all three files into one table and bcp the results out into a file.
create table dbo._temp
(
id int identity,
data nvarchar(4000)
)
declare @sql nvarchar(4000)
declare @MyFile1 nvarchar(255), @MyFile2 nvarchar(255), @MyFile3 nvarchar(255), @MyOutputFile nvarchar(255)
set @sql = 'bcp mydatabase.dbo._temp in "' + @MyFile1 + '" -t -c"
exec master..xp_cmdshell @sql
set @sql = 'bcp mydatabase.dbo._temp in "' + @MyFile2 + '" -t -c"
exec master..xp_cmdshell @sql
set @sql = 'bcp mydatabase.dbo._temp in "' + @MyFile3 + '" -t -c"
exec master..xp_cmdshell @sql
set @sql = 'bcp "select * from mydatabase.dbo._temp order by id" queryout "' + @MyOutputFile + '" -t -c"
Good luck
John
March 15, 2002 at 5:27 pm
Just reread my post and it's pretty sloppy. Jay, if decide to go the bcp route and need help with the exact syntax, drop me an email.
Thanks,
John
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply