Extra lines at the botom of notepad when opening CSV

[vjay883]

Hi,

This is doing my nut in and can't find an answer, can anyone help?

I need to do the following:

1.Open a CSV file in notepad
2.Delete all blank rows at the bottom of the file (There are usually 2 lines - could be more)
3.Save the file

Some reason when I replace the commas with pipes and save the file it always has these blank rows at the bottom. Here is my code I am using:

Dim F As Integer
Dim StrFile As Variant
Dim StrLines As Variant
Dim pos As Integer

'Open file to read
F = FreeFile()
Open "G:\Asset Management\EAMS Prog\03 Tranche Mgt\EP06 Data Mgt\Specialist\AUTOMATION\Load_Files\" + myfilename + ".csv" For Input As F
StrFile = Input(LOF(F), #F)
Close F
'Edit file content
StrFile = Replace(StrFile, ",", "|")
StrLines = Split(StrFile, vbNewLine)
pos = InStr(StrLines(0), "EN")
If pos > 0 Then
StrLines(0) = Left(StrLines(0), pos - 1) & Replace(StrLines(0), "|", "", pos)
StrLines(0) = Chr(239) & Chr(187) & Chr(191) & StrLines(0)
StrFile = Join(StrLines, vbNewLine)
End If
'Save file
F = FreeFile()
Open "G:\Asset Management\EAMS Prog\03 Tranche Mgt\EP06 Data Mgt\Specialist\AUTOMATION\Load_Files_Ready\" + myfilename + ".csv" For Output As F
Print #F, StrFile
Close F

Cheers
V

[Kenneth Hobs]

Have you tried using Write rather than Print?

From the help for Print:

Because Print # writes an image of the data to the file, you must delimit the data so it prints correctly. If you use Tab with no arguments to move the print position to the next print zone, Print # also writes the spaces between print fields to the file.

Note
If, at some future time, you want to read the data from a file using the Input # statement, use the Write # statement instead of the Print # statement to write the data to the file. Using Write # ensures the integrity of each separate data field by properly delimiting it, so it can be read back in using Input #. Using Write # also ensures it can be correctly read in any locale.

[p45cal]

you could use a very basic method to strip off chr(10) and chr(13) characters from the end of Strfile after you've created it but before you pass it on into the file:

Do Until Asc(Mid(StrFile, Len(StrFile), 1)) <> 10 And Asc(Mid(StrFile, Len(StrFile), 1)) <> 13
  StrFile = Left(StrFile, Len(StrFile) - 1)
Loop

[snb]

As long as you do not use vbCrLF to separate lines, the method will add vbCrlf at the end of the file.
If you use the correct line separator (vbCrLf) no empty lines (=vbCrLf) will be added.

'Write' nor 'Print' has any effect on this phenomenon.

[vjay883]

Hi,

Thank you for your replies, p45cal I added you code to mine and seems to sort the problem.

Cheers
V

[Kenneth Hobs]

Of course if your string has a trailing vbCrLf set of codes then stripping it is the first step as p45cal inferred. One of several methods can be used to do that.

To semi-colon or not seems to be the question, in the general writing of code to not add a trailing vbCrLf for Print.

e.g.

Sub ArrayToFile()  Dim F As Integer, s As String, a() As String, fn As String 
  fn = "c:\temp\ArrayToFile.csv"
  s = "Kenneth,Ray,Hobson"
  s = Replace(s, ",", "|")
  
  a() = Split(s, "|")
  s = Join(a, vbCrLf)
  MsgBox s
  
  'Save file
  F = FreeFile()
  Open fn For Output As F
    Print #F, s;
  Close F
  
  Shell "cmd /c Notepad " & fn
End Sub