exporting to csv

[Gio82]

Hi,

I have a problem which I think is a simple one to fix. however my limited knowledge of coding is letting me down.

In the first row of my WS I have data in col A-D in the row 2 I have data from A-M. When I save as csv, the csv file will show 9 extra commas in the first row. I need to import the file into another system therefore I need to remove the commas.

I do not want to have to enter word pad and manually delete these commas. I have been reading up on this and from what i undersand there is some VB code that can be used to stop this.

I have a VERY simple macro to save as csv.

[VBA]Sub createCustomerfile()
Sheet3.SaveAs Filename:="H:\" & Worksheets("Customer Account").Range("B1") & ".csv", _
FileFormat:=xlCSV

End Sub[/VBA]

Is there anything I can add to this code that will get rid of the extra commas?
Any tips will be greatly appreciated.
Thanks,
Gio82

[Kenneth Hobs]

It is working as designed. You need the commas to represent null or empty data. A CSV file must be setup as a database and follow those rules.

If you still need to remove them, one can use text file reading methods to do I think that would be a mistake. Post back if you must have it your way. Keep in mind that a routing that removes ,, might give unexpected results and the file will then not be a true CSV file.

[Gio82]

Hi Kenneth,

Many thanks for your reply.

i am afraid that i need to remove the extra commas. the csv needs to be imported into another system. the extra commas will cause errors when importing it.

The whole purpose is to streamline a process. Hence that's why I'd rather not have the users going into an editor an removing the commas.

Is there any other way of doing this?

Many thanks

Gio82

[Paul_Hossler]

(Read it wrong the first time)

I've manually written CSV files a row at a time, so that might be your only option

[VBA]
'write the list to a CSV or other file, with or without headers
'blank ListFileName will use WB folder and name, with CSV extension
Function WF_WriteFile(r As Range, Optional ListFileName As String = "", _
Optional Delim As String = ",", _
Optional IncludeHeaders As Boolean = False) As String

Dim sFile As String, sLine As String
Dim FileNum As Integer, iCommas As Long
Dim rRow As Range, rCell As Range, rEnd As Range

On Error GoTo NiceExit

If ListFileName = "" Then
sFile = ThisWorkbook.FullName
While Right(sFile, 1) <> "."
sFile = Left(sFile, Len(sFile) - 1)
Wend

sFile = sFile & "csv"
Else
sFile = ListFileName
End If



' Obtain next free file handle number
FileNum = FreeFile()
' Turn error checking off
On Error Resume Next
' Attempt to open destination file for output
Open sFile For Output As #FileNum
' Turn error checking on
On Error GoTo 0
' Loop for each row in selection
For Each rRow In r.Rows

Set rEnd = rRow.Cells(1, rRow.Columns.Count)
If IsEmpty(rEnd) Then Set rEnd = rEnd.End(xlToLeft)
' Loop for each column in selection
For Each rCell In Range(rRow.Cells(1, 1), rEnd.Offset(0, -1)).Cells
Print #FileNum, pvtFmtCsvStr(rCell.Text, Delim);
Next
'do the last value, without semi-colon
Print #FileNum, pvtFmtCsvStr(rEnd.Text, "")
' Start next iteration of RowCount loop
Next
'Close destination file
Close #FileNum
WF_WriteFile = sFile

Exit Function

NiceExit:
WF_WriteFile = ""
End Function
Private Function pvtFmtCsvStr(s As String, d As String) As String
Dim bBracket As Boolean
Dim T As String


bBracket = False

'if embedded quotes, double and mark to bracket
If InStr(s, Chr(34)) > 0 Then
bBracket = True
s = Application.WorksheetFunction.Substitute(s, Chr(34), Chr(34) & Chr(34))
End If

'see if there's embedded commas, if so bracket with quotes
If InStr(s, ",") > 0 Then bBracket = True

'see if we need to add bracketing quotes
If bBracket Then
pvtFmtCsvStr = """" & s & """" & d
Else
pvtFmtCsvStr = s & d
End If
End Function

[/VBA]




Paul