Spaces at the end of CSV files

[hayati]

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]

hehe Nice, you made a version that does selections... Which got me thinking... See below:
This does not need the scripting runtime library.

<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">Dim</SPAN> OrgCB <SPAN style="color:#00007F">As</SPAN> DataObject
<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Const</SPAN> T <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "CSV Manager"
<SPAN style="color:#00007F">Sub</SPAN> CSVControl()
<SPAN style="color:#007F00">'Written By Aaron Bush 11/30/2005 Free for Public Use.</SPAN>
<SPAN style="color:#007F00">'Writes or converts an already existing CSV file to a a CSV file that</SPAN>
<SPAN style="color:#007F00">'does not have a CR/LF as the final character.</SPAN>
<SPAN style="color:#007F00">'12/21/2005 - Additional Functionality added after discussion with Greg Tugby</SPAN>
<SPAN style="color:#007F00">'(See http://www.mrexcel.com/board2/viewtopic.php?t=181925_)</SPAN>
<SPAN style="color:#007F00">'Functions added:</SPAN>
<SPAN style="color:#007F00">'   -Allows user to choose between converting an already existing file,</SPAN>
<SPAN style="color:#007F00">'   the entire activesheet, or just a selection.</SPAN>
<SPAN style="color:#007F00">'   -Added option to preserve blank rows above used range.</SPAN>
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#007F00">'Commentary:    Uses msgboxes, but would look MUCH nicer with</SPAN>
<SPAN style="color:#007F00">'               a User Form. Has been set up to try and facilitate an</SPAN>
<SPAN style="color:#007F00">'               easy conversion.</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> MB1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">'Messagebox</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> S <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>   <SPAN style="color:#007F00">'Sets Selection to convert.</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> R <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>   <SPAN style="color:#007F00">'Sets empty Record yes/no.</SPAN>
Set OrgCB = <SPAN style="color:#00007F">New</SPAN> DataObject
OrgCB.GetFromClipboard
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN>
R = MsgBox("Do you want to preserve blank rows above the data range?" & Chr(10) & "(If unsure select " & Chr(34) & "No" & Chr(34) & ".)", 65828)
MB1 = MsgBox("Do you want to convert an already existing file?", 65571, T)
<SPAN style="color:#00007F">If</SPAN> MB1 = 6 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Call</SPAN> PrepareCSV(0, R)
<SPAN style="color:#00007F">If</SPAN> MB1 = 2 <SPAN style="color:#00007F">Or</SPAN> MB1 = 6 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> Point1
MB1 = MsgBox("Do you want to convert the entire active sheet?", 65571, T)
<SPAN style="color:#00007F">If</SPAN> MB1 = 6 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Call</SPAN> PrepareCSV(1, R)
<SPAN style="color:#00007F">If</SPAN> MB1 = 2 <SPAN style="color:#00007F">Or</SPAN> MB1 = 6 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> Point1
MB1 = MsgBox("Converting selection only.", 65601, T, R)
<SPAN style="color:#00007F">If</SPAN> MB1 = 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Call</SPAN> PrepareCSV(2, R)
Point1:
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
OrgCB.PutInClipboard
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Function</SPAN> PrepareCSV(S <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, R <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>)
<SPAN style="color:#00007F">Dim</SPAN> FS, F, TS                   <SPAN style="color:#007F00">'Text Stream Objects</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> FN <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>                <SPAN style="color:#007F00">'Filename</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> FN2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>               <SPAN style="color:#007F00">'Short Filename</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">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:#007F00">'<SPAN style="color:#00007F">Set</SPAN> 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:#00007F">If</SPAN> S <> 0 <SPAN style="color:#00007F">Then</SPAN>
    FN = Application.GetSaveAsFilename(InitialFileName:=Mid(ActiveWorkbook.Name, 1, InStr(ActiveWorkbook.Name, ".") - 1), FileFilter:="CSV Files (*.CSV),*.csv", Title:=T & " - Save as?")
    Else: FN = Application.GetOpenFilename(FileFilter:="CSV Files (*.CSV),*.csv,Microsoft Excel Files (*.xls),*.xls", Title:=T & " - Select file to convert:")
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">If</SPAN> FN = "False" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN>
<SPAN style="color:#00007F">If</SPAN> S = 0 <SPAN style="color:#00007F">Then</SPAN>
        Workbooks.Open FN
        FN2 = ActiveWorkbook.Name
        Sheets.Copy
        Windows(FN2).Close
        Kill FN
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">If</SPAN> S = 2 <SPAN style="color:#00007F">Then</SPAN>
    Selection.Copy
    <SPAN style="color:#00007F">Else</SPAN>
    <SPAN style="color:#00007F">If</SPAN> R = 6 <SPAN style="color:#00007F">Then</SPAN>
        Range("A1", ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)).Copy
        <SPAN style="color:#00007F">Else</SPAN>
        ActiveSheet.UsedRange.Copy
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
FS.CreateTextFile FN
Set F = FS.GetFile(FN)
Set TS = F.OpenAsTextStream(ForWriting, TristateUseDefault)
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
Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">If</SPAN> S = 0 <SPAN style="color:#00007F">Then</SPAN> ActiveWorkbook.Close
MsgBox "Conversion complete.", 65600, T
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

</FONT>