CODEWITHSUNDEEP
vbaexcelcsv
jaysoncopes
jaysoncopes

Reputation: 854

Excel Exports Extra Commas to CSV

I have a large macro program run through Excel 2010 that, after formatting large amounts of data into another table and exporting the workbook as a CSV file (by large amounts of data I mean thousands of rows, up to over 59,000 rows). Recently, my files have started ending up with an extra row of commas at the end like so:

data,data,data,data,number,date
data,data,data,data,number,date
,,,,,

I am exporting these files to an SQL database using a stored procedure, so ensuring that there are no extra commas to screw with the program is essential. So, with that said, what is happening and how can I prevent it? I can provide any code or information that you believe is missing.

NOTE: It only appears to be happening on files with a couple thousand lines at least of data. One file exported often has 2,000+ and another must have 59,000+ for the table to be exported.

EDIT1: Here's the macro I'm using, just in case it would be helpful (requested by Ditto)

Sub exportTable()
Dim varIsOpen As Boolean
Dim varSaveLocation1 As String, varSaveLocation2 As String
varIsOpen = False

If ThisWorkbook.Sheets("ControlSheet").Range("D2").value = "" Then
    varSaveLocation1 = ThisWorkbook.Path & "\CSVREVIEW\"
    varSaveLocation2 = varSaveLocation1 & Year(Now) & Month(Now) & Day(Now) & Hour(Now) & Minute(Now)
Else
    varSaveLocation1 = ThisWorkbook.Sheets("ControlSheet").Range("D2").value
    If Right(varSaveLocation1, 1) <> "\" Then varSaveLocation1 = varSaveLocation1 & "\"
    varSaveLocation2 = varSaveLocation1 & Year(Now) & Month(Now) & Day(Now) & Hour(Now) & Minute(Now)
End If
    For counter = 1 To Workbooks.Count
        If Workbooks(counter).Name = "TableBook.xls" Then varIsOpen = True

        If varIsOpen = True Then Exit For
    Next

    If varIsOpen = False Then GoTo isClosed

Workbooks("TableBook").Activate

Application.DisplayAlerts = False

'Check if TableBook is empty and don't export if so
If Workbooks("TableBook").Sheets("logFile").Range("A1").value = "" Then
    Workbooks("TableBook").Close
    GoTo isClosed
End If

'On Error Resume Next
If Len(Dir(varSaveLocation1, vbDirectory)) = 0 Then
   MkDir varSaveLocation1
End If
If Len(Dir(varSaveLocation2, vbDirectory)) = 0 Then
    MkDir varSaveLocation2
End If
'On Error GoTo 0

ActiveWorkbook.Sheets("test").Activate
ActiveWorkbook.SaveAs varSaveLocation2 + "\test", xlCSV

ActiveWorkbook.Sheets("part").Activate
ActiveWorkbook.SaveAs varSaveLocation2 + "\part", xlCSV

ActiveWorkbook.Sheets("logFile").Activate
ActiveWorkbook.SaveAs varSaveLocation2 + "\logFile", xlCSV

ActiveWorkbook.Sheets("deltaLimits").Activate
ActiveWorkbook.SaveAs varSaveLocation2 + "\deltaLimits", xlCSV

ActiveWorkbook.Close

Application.DisplayAlerts = True

isClosed:
End Sub

Upvotes: 2

Views: 7945

Answers (3)

BeycanYavuz
BeycanYavuz

Reputation: 1

I had the exactly same problem. If the entire sheet is formatted, even just Text type or Text Height, Excel detects even empty cells as data. You can delete the entire formatted columns/ rows as described above Cœur. Or just create a new sheet without formatting anything and copy your code or change the address.

Upvotes: 0

pezet
pezet

Reputation: 9

I had an issue which looked the same (extra commas in csv) and it turned out that I was exporting one extra line in my loop and the cells I was using were empty, therefore I got commas only

Upvotes: 0

user4039065
user4039065

Reputation:

Tap Ctrl+End to see what Excel believes are the extents of your data. If it is beyond what you want to export, use Home ► Editing ► Clear ► Clear All to wipe all values and formatting from the rows below and the columns to the right of your desired data region and save the workbook. Excel 2010 (with all SPs) will adjust to the CurrentRegion and Ctrl+End should now take you to the correct last cell.

Earlier versions of Excel (or XL2010 without all SPs) may require additional steps (see Unwanted extra blank pages in Excel).

Upvotes: 3

Related Questions