Spaces at the end of CSV files

hayati

Board Regular
Joined
Oct 3, 2005
Messages
118
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
This will create a CSV with no line-return on the end.

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

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

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> WriteCSVwithoutFinalCRLF()

<SPAN style="color:#007F00">' Utility to take selected range or a worksheet's USEDRANGE</SPAN>
<SPAN style="color:#007F00">' and write to a CSV file.  Simply saving a worksheet as CSV</SPAN>
<SPAN style="color:#007F00">' appends an extraneous CR/LF to the end of the file which</SPAN>
<SPAN style="color:#007F00">' may cause importing programs to erroneously process</SPAN>
<SPAN style="color:#007F00">' an empty line at the end of the file.</SPAN>

<SPAN style="color:#007F00">' Written by Greg Truby, Dec 2005</SPAN>
<SPAN style="color:#007F00">' Original idea from Aaron Bush (Oorang) 11/30/2005</SPAN>
<SPAN style="color:#007F00">' http://www.mrexcel.com/board2/viewtopic.php?t=181925</SPAN>

<SPAN style="color:#007F00">' Required references:</SPAN>
<SPAN style="color:#007F00">'   * MS Scripting Runtime</SPAN>
<SPAN style="color:#007F00">'   * MS Forms 2.0</SPAN>

    <SPAN style="color:#00007F">If</SPAN> ActiveSheet.Type <> XlSheetType.xlWorksheet <SPAN style="color:#00007F">Then</SPAN>
        MsgBox "This utility only works with worksheets, not charts.", _
                vbCritical, "Aborting"
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>                                                            <SPAN style="color:#007F00">' |--¿xsub--></SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

    <SPAN style="color:#007F00">' Scripting objects</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> fsoNew <SPAN style="color:#00007F">As</SPAN> Scripting.FileSystemObject, _
        filCSV  <SPAN style="color:#00007F">As</SPAN> Scripting.File, _
        txtNew <SPAN style="color:#00007F">As</SPAN> Scripting.TextStream
        
    <SPAN style="color:#007F00">' Forms object</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> doClipboard <SPAN style="color:#00007F">As</SPAN> MSForms.DataObject
    
    <SPAN style="color:#007F00">' Excel/Office objects/types</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> strRecord <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strFileName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> intResponse <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, fdSaveAsDlg As FileDialog, rngToWrite As Range
    
    <SPAN style="color:#007F00">' initialize/instantiate</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> fsoNew = <SPAN style="color:#00007F">New</SPAN> FileSystemObject
    <SPAN style="color:#00007F">Set</SPAN> doClipboard = New DataObject
    
    <SPAN style="color:#007F00">' put the selected range or usedrange into a string</SPAN>
    <SPAN style="color:#007F00">' by copy to clipboard and getting from clipboard.</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> rngToWrite = ActiveCell
    <SPAN style="color:#00007F">If</SPAN> TypeName(Selection) = "Range" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Set</SPAN> rngToWrite = Selection
    <SPAN style="color:#00007F">If</SPAN> rngToWrite.Count = 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Set</SPAN> rngToWrite = ActiveSheet.UsedRange
    rngToWrite.Copy
    <SPAN style="color:#00007F">With</SPAN> doClipboard
        doClipboard.GetFromClipboard
        strRecord = .GetText(1)
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN>
    
    <SPAN style="color:#007F00">' replace the default delimiters (tabs) with commas</SPAN>
    strRecord = Replace(strRecord, vbTab, ",")
    
    <SPAN style="color:#007F00">' create default filename for CSV output</SPAN>
    strFileName = Replace(ActiveWorkbook.FullName, ".xls", _
                  " - " & ActiveSheet.Name & ".csv")
        
    <SPAN style="color:#007F00">' _____Use FILEDIALOG object to retrieve file name to write to</SPAN>
    <SPAN style="color:#007F00">'      the SAVEAS fd offers built-in overwrite confirmation._____</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> fdSaveAsDlg = Application.FileDialog(msoFileDialogSaveAs)
    <SPAN style="color:#00007F">With</SPAN> fdSaveAsDlg
        .InitialFileName = strFileName
        .InitialView = msoFileDialogViewDetails
        .AllowMultiSelect = <SPAN style="color:#00007F">False</SPAN>
        .FilterIndex = 11                                               <SPAN style="color:#007F00">' CSV filter</SPAN>
        .Title = "Save range " _
               & rngToWrite.Address(False, False) _
               & " as"
        <SPAN style="color:#00007F">If</SPAN> .Show <> -1 <SPAN style="color:#00007F">Then</SPAN>
            MsgBox "Canceled per user request.", _
                    vbCritical, "Process aborted"
            <SPAN style="color:#00007F">GoTo</SPAN> CleanUp                                                <SPAN style="color:#007F00">' |--¿xsub?--></SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        strFileName = .SelectedItems(1)
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    
    <SPAN style="color:#007F00">' write the string back out to a file, sans the last two characters</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Dir(strFileName) <> vbNullString <SPAN style="color:#00007F">Then</SPAN> Kill strFileName
    fsoNew.CreateTextFile strFileName
    <SPAN style="color:#00007F">Set</SPAN> filCSV = fsoNew.GetFile(strFileName)
    <SPAN style="color:#00007F">Set</SPAN> txtNew = filCSV.OpenAsTextStream(ForWriting, TristateUseDefault)
    <SPAN style="color:#00007F">With</SPAN> txtNew
        .Write Mid(strRecord, 1, Len(strRecord) - 2)
        .Close
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    
CleanUp:
    <SPAN style="color:#00007F">Set</SPAN> txtNew = <SPAN style="color:#00007F">Nothing</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> filCSV = <SPAN style="color:#00007F">Nothing</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> fsoNew = <SPAN style="color:#00007F">Nothing</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> rngToWrite = <SPAN style="color:#00007F">Nothing</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> fdSaveAsDlg = <SPAN style="color:#00007F">Nothing</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> doClipboard = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top