Solved

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

Posted on 2006-10-19
11
291 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

856 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