Solved

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

Posted on 2006-10-19
11
296 Views
Last Modified: 2012-08-14
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
Comment
Question by:cswebdev
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
11 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17771265
You can create it using BCP or DTS and when it is created you can truncate the last two bytes of the file.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17771268
P.S.  You can do that with something like .NET or even VBScript.
0
 

Author Comment

by:cswebdev
ID: 17773085
Can you give me an example of what you mean?
0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17773590
Which part?  Creating the text file or losing the last two bytes of the file?
0
 

Author Comment

by:cswebdev
ID: 17774412
Losing the last two bytes of the file, please.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17775293
Is using VB OK or would you rather VbScript?
0
 

Author Comment

by:cswebdev
ID: 17775415
VB is fine, thanks.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17775559
If no one has come up with an answer, I will post a solution when I get home tonight.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17786328
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 125 total points
ID: 17786344
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
 

Author Comment

by:cswebdev
ID: 17790441
Thank you very much!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question