Solved

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

Posted on 2006-10-19
11
281 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
  • 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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PL/SQL query 14 50
Noob question:this site is sql vulns? 2 46
while loop in html mail format 5 32
How toselect unique values 3 9
Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

759 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now