Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2006-10-19
11
Medium Priority
?
299 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 500 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 Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

618 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