[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

Write a file without the last hard return/carriage return/line feed.

My Problem:
I am using Microsoft SQL 2000 to create a flat file and all but the last record must end with a hard return.  I am using bcp to write the file but when I open the file with a text editor, there is an additional line at the end.  I have tried using REPLACE on the last line, but that didn't work.

My request:
How can I write this file without that last hard return?  
0
cswebdev
Asked:
cswebdev
  • 7
  • 4
1 Solution
 
Anthony PerkinsCommented:
You can create it using BCP or DTS and when it is created you can truncate the last two bytes of the file.
0
 
Anthony PerkinsCommented:
P.S.  You can do that with something like .NET or even VBScript.
0
 
cswebdevAuthor Commented:
Can you give me an example of what you mean?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Anthony PerkinsCommented:
Which part?  Creating the text file or losing the last two bytes of the file?
0
 
cswebdevAuthor Commented:
Losing the last two bytes of the file, please.
0
 
Anthony PerkinsCommented:
Is using VB OK or would you rather VbScript?
0
 
cswebdevAuthor Commented:
VB is fine, thanks.
0
 
Anthony PerkinsCommented:
If no one has come up with an answer, I will post a solution when I get home tonight.
0
 
Anthony PerkinsCommented:
The traditional way of doing this, was to read eack line of the file and rewrite it to a new file without the last CrLf.  While this does work it is a lot faster to use some API calls to in effect shorten the file by 2 bytes.  This way there is no I/O involved whatsoever.

This code looks fairly long, but is not really, I have left the error checking to be on the safe side, especially while you are testing:

Option Explicit

Private Const GENERIC_WRITE As Long = &H40000000
Private Const GENERIC_READ As Long = &H80000000
Private Const FILE_ATTRIBUTE_NORMAL As Long = &H80
Private Const OPEN_ALWAYS As Long = 4
Private Const FILE_BEGIN As Long = 0
Private Const INVALID_HANDLE_VALUE As Long = -1
Private Const INVALID_FILE_SIZE As Long = &HFFFFFFFF

Private Declare Function CreateFile Lib "kernel32" Alias "CreateFileA" (ByVal lpFileName As String, _
                                ByVal dwDesiredAccess As Long, ByVal dwShareMode As Long, _
                                ByVal lpSecurityAttributes As Long, ByVal dwCreationDisposition As Long, _
                                ByVal dwFlagsAndAttributes As Long, ByVal hTemplateFile As Long) As Long
Private Declare Function GetFileSize Lib "kernel32" (ByVal hfile As Long, lpFileSizeHigh As Long) As Long
Private Declare Function SetFilePointer Lib "kernel32" (ByVal hfile As Long, ByVal lDistanceToMove As Long, _
                                lpDistanceToMoveHigh As Long, ByVal dwMoveMethod As Long) As Long
Private Declare Function SetEndOfFile Lib "kernel32" (ByVal hfile As Long) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal hfile As Long) As Long


Private Sub TruncateLastCrLf(FileName As String)
Dim hfile As Long
Dim FileLength As Long

hfile = CreateFile(FileName, GENERIC_WRITE Or GENERIC_READ, 0, 0, OPEN_ALWAYS, FILE_ATTRIBUTE_NORMAL, 0)
If hfile <> INVALID_HANDLE_VALUE Then
    FileLength = GetFileSize(hfile, ByVal 0&)   ' Assume that the file is less than 2GB
    If FileLength <> INVALID_FILE_SIZE Then
        If SetFilePointer(hfile, FileLength - 2, ByVal 0&, FILE_BEGIN) > 0 Then
            If SetEndOfFile(hfile) Then
                Debug.Print "Success!"
            Else
                Debug.Print "Could not truncate the file"
            End If
        Else
            Debug.Print "Could not set the file pointer."
        End If
    Else
        Debug.Print "Could not get the file length."
    End If
    CloseHandle hfile
Else
    Debug.Print "Could not open the file."
End If

End Sub
0
 
Anthony PerkinsCommented:
Here is the abbreviated code without the error checking:

Option Explicit

Private Const GENERIC_WRITE As Long = &H40000000
Private Const GENERIC_READ As Long = &H80000000
Private Const FILE_ATTRIBUTE_NORMAL As Long = &H80
Private Const OPEN_ALWAYS As Long = 4
Private Const FILE_BEGIN As Long = 0
Private Const INVALID_HANDLE_VALUE As Long = -1
Private Const INVALID_FILE_SIZE As Long = &HFFFFFFFF

Private Declare Function CreateFile Lib "kernel32" Alias "CreateFileA" (ByVal lpFileName As String, _
                                ByVal dwDesiredAccess As Long, ByVal dwShareMode As Long, _
                                ByVal lpSecurityAttributes As Long, ByVal dwCreationDisposition As Long, _
                                ByVal dwFlagsAndAttributes As Long, ByVal hTemplateFile As Long) As Long
Private Declare Function GetFileSize Lib "kernel32" (ByVal hfile As Long, lpFileSizeHigh As Long) As Long
Private Declare Function SetFilePointer Lib "kernel32" (ByVal hfile As Long, ByVal lDistanceToMove As Long, _
                                lpDistanceToMoveHigh As Long, ByVal dwMoveMethod As Long) As Long
Private Declare Function SetEndOfFile Lib "kernel32" (ByVal hfile As Long) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal hfile As Long) As Long

Private Sub TruncateLastCrLf(FileName As String)
Dim hfile As Long
Dim FileLength As Long

hfile = CreateFile(FileName, GENERIC_WRITE Or GENERIC_READ, 0, 0, OPEN_ALWAYS, FILE_ATTRIBUTE_NORMAL, 0)
FileLength = GetFileSize(hfile, ByVal 0&)   ' Assume that the file is less than 2GB
SetFilePointer hfile, FileLength - 2, ByVal 0&, FILE_BEGIN
SetEndOfFile hfile
CloseHandle hfile

End Sub
0
 
cswebdevAuthor Commented:
Thank you very much!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now