Spaces at the end of CSV files

hayati · Nov 29, 2005

Hi,

In our company we are using CSV files to upload data to our Oracle database system. I am having a small problem that our IT team thinks there is nothing to do about, so I just wanted to double check with the experts on this form. Here it goes:

When I save the file as a CSV file apperently a blank line is added to the document. In order to get rid of this we have to open up the file by right clicking and choosing open with. Open the file with a notepad and delete the spaces after the last column. Each time you open up the file these steps have to be performed over again because somehow the spaces are added on to the file.

Is there any code I can add to the file that will help me with this problem or is there anyway to stop excel from adding the unnecessary spaces once the file is saved as a CSV. Why/how are these spaces added to begin with?

I am also being told by our IT dept that there is not a solution in Oracle for this problem

Thanks for all your help!

Cheers,
Matt

Oorang · Nov 29, 2005

This will create a CSV with no line-return on the end.

Option Explicit
Sub Output_Csv_Without_Final_Line_Return()
'Written By Aaron Bush 11/29/2005 Free for Public Use.
Dim FS, F, TS, S 'Text Stream Objects
Dim R As Long 'Row Number
Dim Record As String 'String containing each record.
Dim C As Object 'Cell Object.
'Not completly needed, included from help-file just to make your life easier.
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2
Const TristateTrue = -1
Const TristateFalse = 0
'You do not need to use a ChDir if you preface all file name refrences with a full path.
ChDir "C:\Documents and Settings\AB\Desktop"
'Clears out old file
On Error Resume Next
Kill "Book1.csv"
On Error GoTo 0
'Set Textstream Objects
Set FS = CreateObject("Scripting.FileSystemObject")
'(Creates the file)
FS.CreateTextFile "Book1.csv"
Set F = FS.GetFile("Book1.csv")
Set TS = F.OpenAsTextStream(ForWriting, TristateUseDefault)
'Loops through each cell and concatenates into a comma delimited record.
For R = ActiveSheet.UsedRange.Row To ActiveSheet.UsedRange.Rows.Count
 Record = Empty
 For Each C In Range(Rows(R).Address)
 Record = Record & C.Value & Chr(44)
 If C.Column >= ActiveSheet.UsedRange.Columns.Count Then Exit For
 Next C
 'Writes record to file minus final comma.
 TS.Write Mid(Record, 1, Len(Record) - 1)
 'Starts a new line in file unless on the final line.
 If R <> ActiveSheet.UsedRange.Rows.Count Then TS.writeblanklines (1)
Next R
TS.Close
End Sub

Oorang · Nov 30, 2005

I noticed my Previous Code was a little slow, this runs in about 5 seconds on 17,000 records.

Option Explicit
Dim CB As DataObject
Sub Output_Csv_Without_Final_Line_Return()
'Written By Aaron Bush 11/30/2005 Free for Public Use.
'Requires refrence to Microsoft Form 2.0 Object Library.
'(Usually C:\WINDOWS\System32\FM20.DLL).
Dim FS, F, TS 'Text Stream Objects
Const ForWriting As Byte = 2
Const TristateUseDefault As Integer = -2
Dim Record As String 'String containing each record.
'Set Textstream Object
Set FS = CreateObject("Scripting.FileSystemObject")
Set CB = New DataObject
'Clears out old file
On Error Resume Next
Kill "H:\Book1.csv"
On Error GoTo 0
'(Creates the file)
FS.CreateTextFile "C:\Book1.csv"
Set F = FS.GetFile("C:\Book1.csv")
Set TS = F.OpenAsTextStream(ForWriting, TristateUseDefault)
ActiveSheet.UsedRange.Copy
CB.GetFromClipboard
Record = CB.GetText(1)
'Replace Column dividers with commas
Record = Replace(Record, Chr(9), Chr(44))
'Writes record to file minus final comma.
TS.Write Mid(Record, 1, Len(Record) - 2)
TS.Close
End Sub

Barry Katcher · Nov 30, 2005

Another approach. Once you get your file into Excel, you can use Data>Filter>Autofilter to filter for blanks, and then delete those rows. You can also use the =TRIM() function to remove leading or trailing zeros.

Oorang · Nov 30, 2005

Barry, I think what Hyati is referring to is when you save a file as CSV from Excel; Excel puts the last two characters as a new line (10) and a carriage return (13). To see what he is talking about open the CSV with notepad and notice that when you use the down arrow you can drop down one line past the last row. In certain databases this can create blank records.

Barry Katcher · Nov 30, 2005

Ooops!

hayati · Dec 2, 2005

User-defined type not defined

Hi Oorang,

Sorry for my late reply. I was on travel and just had time to check back the board. Thank you for the time and effort!

Yes you are correct about what I was refering to. But I do have one problem with the code you posted.

I am getting an error where the Dim CB As DataObject is being highlighted and the error msg is: User-Defined type not defined.

I tried playing around with the code but couldn't figure out a way around it asI don't know why the problem is caused in the first place?

Also can I have it save the file in the following format:

c:\Loadforms\sheet1[K2].csv

Where I would like the code to pick up the value in K2 and use that as the name?

Thanks a lot for your help!
Cheers,
Matt

Oorang · Dec 2, 2005

Sorry I forgot to mention you need to enable a refrence to Microsoft Form 2.0 Object Library (Usually C:\WINDOWS\System32\FM20.DLL).

To do this:
On the VBE (Visual Basic Editor), select the Tools Menu, the select refrences, and place a checkbox by the entry that says "Microsoft Form 2.0 Object Library". Code should work after that.

hayati · Dec 2, 2005

Thankkkk youuu!!!

Oorang,

Thanks for the final tip. I was playing around with the references but couldn't figure it out. Now it works like a charm. This should save a lot of time and more importantly it just makes things more efficient.

I changed the code a little bit so now what is does is it asks the user if they would like to save the file as a csv when they are closing the file. I also changed the naming so that it saves the file as the value in K2. Here is the final code that I am using:

Code:

Option Explicit
Dim CB As DataObject
Private Sub Workbook_BeforeClose(Cancel As Boolean)
' Ask user to save
Dim Response As Integer
Response = 0
Response = MsgBox("Would you like to save this file as a csv file?" & Chr(13) & "Click OK to proceed or CANCEL to abort.", 257, "Please confirm to save")
If Response = 1 Then
    'Written By Aaron Bush 11/30/2005 Free for Public Use.
    'Requires refrence to Microsoft Form 2.0 Object Library.
    '(Usually C:\WINDOWS\System32\FM20.DLL).
    Dim FS, F, TS                   'Text Stream Objects
    Dim name As String
    Const ForWriting As Byte = 2
    Const TristateUseDefault As Integer = -2
    Dim Record As String            'String containing each record.
    'Set Textstream Object
    Set FS = CreateObject("Scripting.FileSystemObject")
    Set CB = New DataObject
    Sheets("Sheet1").Select
    name = Cells(2, 11).Value
    Sheets("Sheet2").Select
    'Clears out old file
    On Error Resume Next
    Kill "C:\Massloads\" & name & ".csv"
    On Error GoTo 0
    '(Creates the file)
    FS.CreateTextFile "C:\Massloads\" & name & ".csv"
    Set F = FS.GetFile("C:\Massloads\" & name & ".csv")
    Set TS = F.OpenAsTextStream(ForWriting, TristateUseDefault)
    ActiveSheet.UsedRange.Copy
    CB.GetFromClipboard
    Record = CB.GetText(1)
    'Replace Column dividers with commas
    Record = Replace(Record, Chr(9), Chr(44))
    'Writes record to file minus final comma.
    TS.Write Mid(Record, 1, Len(Record) - 2)
    TS.Close
    MsgBox "File saved under Massload folder on C drive" & Chr(13) & "          file name: " & name & ".csv", vbOKOnly
    End If
End Sub

Thank you very much for your help!

Regards,
Matt

Greg Truby · Dec 20, 2005

Hi Oorang,

For my own purposes, I had been meaning to write utility for myself whereby I can grab a range of cells and kick them into a CSV file. Your code prompted me to go ahead and write it. Using your code as a starting point I wrote the following for myself. I'll either add an option to my main FILE menu or an option to my CELL popup to call the function. Thanks for the kick in the pants.

Regards,

Greg<hr />Option Explicit

Sub WriteCSVwithoutFinalCRLF()

' Utility to take selected range or a worksheet's USEDRANGE
' and write to a CSV file. Simply saving a worksheet as CSV
' appends an extraneous CR/LF to the end of the file which
' may cause importing programs to erroneously process
' an empty line at the end of the file.

' Written by Greg Truby, Dec 2005
' Original idea from Aaron Bush (Oorang) 11/30/2005
' http://www.mrexcel.com/board2/viewtopic.php?t=181925

' Required references:
' * MS Scripting Runtime
' * MS Forms 2.0

 If ActiveSheet.Type <> XlSheetType.xlWorksheet Then
 MsgBox "This utility only works with worksheets, not charts.", _
 vbCritical, "Aborting"
 Exit Sub ' |--¿xsub-->
 End If

 ' Scripting objects
 Dim fsoNew As Scripting.FileSystemObject, _
 filCSV As Scripting.File, _
 txtNew As Scripting.TextStream

 ' Forms object
 Dim doClipboard As MSForms.DataObject

 ' Excel/Office objects/types
 Dim strRecord As String, strFileName As String, strPath As String
 Dim intResponse As Integer, fdSaveAsDlg As FileDialog, rngToWrite As Range

 ' initialize/instantiate
 Set fsoNew = New FileSystemObject
 Set doClipboard = New DataObject

 ' put the selected range or usedrange into a string
 ' by copy to clipboard and getting from clipboard.
 Set rngToWrite = ActiveCell
 If TypeName(Selection) = "Range" Then Set rngToWrite = Selection
 If rngToWrite.Count = 1 Then Set rngToWrite = ActiveSheet.UsedRange
 rngToWrite.Copy
 With doClipboard
 doClipboard.GetFromClipboard
 strRecord = .GetText(1)
 End With
 Application.CutCopyMode = False

 ' replace the default delimiters (tabs) with commas
 strRecord = Replace(strRecord, vbTab, ",")

 ' create default filename for CSV output
 strFileName = Replace(ActiveWorkbook.FullName, ".xls", _
 " - " & ActiveSheet.Name & ".csv")

 ' _____Use FILEDIALOG object to retrieve file name to write to
 ' the SAVEAS fd offers built-in overwrite confirmation._____
 Set fdSaveAsDlg = Application.FileDialog(msoFileDialogSaveAs)
 With fdSaveAsDlg
 .InitialFileName = strFileName
 .InitialView = msoFileDialogViewDetails
 .AllowMultiSelect = False
 .FilterIndex = 11 ' CSV filter
 .Title = "Save range " _
 & rngToWrite.Address(False, False) _
 & " as"
 If .Show <> -1 Then
 MsgBox "Canceled per user request.", _
 vbCritical, "Process aborted"
 GoTo CleanUp ' |--¿xsub?-->
 End If
 strFileName = .SelectedItems(1)
 End With

 ' write the string back out to a file, sans the last two characters
 If Dir(strFileName) <> vbNullString Then Kill strFileName
 fsoNew.CreateTextFile strFileName
 Set filCSV = fsoNew.GetFile(strFileName)
 Set txtNew = filCSV.OpenAsTextStream(ForWriting, TristateUseDefault)
 With txtNew
 .Write Mid(strRecord, 1, Len(strRecord) - 2)
 .Close
 End With

CleanUp:
 Set txtNew = Nothing
 Set filCSV = Nothing
 Set fsoNew = Nothing
 Set rngToWrite = Nothing
 Set fdSaveAsDlg = Nothing
 Set doClipboard = Nothing
End Sub

Spaces at the end of CSV files

hayati

Board Regular

Some videos you may like

Excel Facts

Oorang

Well-known Member

Oorang

Well-known Member

Barry Katcher

Well-known Member

Oorang

Well-known Member

Barry Katcher

Well-known Member

hayati

Board Regular

Oorang

Well-known Member

hayati

Board Regular

Greg Truby

MrExcel MVP

Similar threads

Watch MrExcel Video

Forum statistics

Share this page