Adjust CSV Output to Remove Xtra Commas

E

erock24

Well-known Member
Joined
Oct 26, 2006
Messages
1,163
I am creating CSV files via macro. Everything is working as it should. I was just curious if it is possible to edit the output to clean extra commas.

For example if i create a csv with 2 rows. The 1st row has 1 column used and the 2nd has 8 columns used. The 1st row will have 7 commas (as it should) that I currently have to manually remove via Ultraedit text editor. Is there a way to automate this removal from the creation of the csv file?

Any file i create will always only have 2 different length of data interms of columns used. What I mean is the file will always have rows with data in just 1 column and rows with data in more, but the more cols will always be the same. So the number of commas to remove would be = (Total Columns Used -1). In the previous example we would need to remove all instances of 7 comma groups or ",,,,,,"

I hope there's a solution.
Thanks,
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
erock24,

Can we see the macro code?

At the beginning of your posted code, enter the following without the quote marks:["code"]


Your code goes here.


At the end of your posted code, enter the following without the quote marks:
["/code"]


Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php
 
Upvote 0
What does this mean?

erock said:
rows with data in just 1 column and rows with data in more
Click to expand...

As well as posting you current code could you post some sample data.:)
 
Upvote 0
The way I read the question, one possible way to do what you want would be to save the file as csv, but with a txt extension, reopen it as text only (into a single column) then use find/replace to remove the extra commas.
Something like...
Code: 
Sub SaveClean()
'
' 
    Dim WorkingFile As String, CsvTextFile As String, FinalFileName As String, i As Integer, numcols As Long
    WorkingFile = ActiveWorkbook.Name
    CsvTextFile = Left(WorkingFile, InStrRev(WorkingFile, ".")) & "txt"
    ChDir "C:\temp"
    
    ActiveWorkbook.SaveAs fileName:=CsvTextFile, FileFormat:=xlCSV, _
        CreateBackup:=False
    
    numcols = ActiveSheet.UsedRange.Columns.Count
    ActiveWindow.Close SaveChanges:=False
    Workbooks.OpenText fileName:=CsvTextFile
    For i = 1 To numcols - 1
        ReplaceString = ReplaceString & ","
    Next i
    Cells.Replace What:=ReplaceString, Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    FinalFileName = Left(CsvTextFile, Len(CsvTextFile) - 3) & "csv"
    ActiveWorkbook.SaveAs fileName:=FinalFileName, FileFormat:=xlCSV, _
        CreateBackup:=False
            
    ActiveWindow.Close SaveChanges:=False
End Sub
Hope this helps,
Cindy
 
Upvote 0
The excel sheet would look like this (however there are no * in the columns...these are blank)

<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
ABCDEF
1RunTime P:01:01:01*****
21233256945612389
3RunTime P:01:01:02*****
4564978954555444544453145
Book1


</body></html>

all my code does is save it as a csv, which then makes it look like this:
<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
A
1RunTime P:01:01:01,,,,,
21233,25,69,456,123,89
3RunTime P:01:01:02,,,,,
45649,789,54555,4445,4445,3145
Sheet1

</body></html>
I want to somehow remove all the extra commas after rows 1 and 3. Currently, I open the created csv file in a text editor and search for a group of 5 commas (6-1) and replace with nothing. I thought there might be a way to do this as the file is created.
 
Upvote 0
I think the macro I provided above will do exactly what you want, then, although it does create an intermediate .txt file.
Your call, of course, as to whether that's acceptable.
Cindy
 
Upvote 0
What code?:)

I think it would help to see how exactly you are creating the CSV file.
 
Upvote 0
Working with the data you posted, this code:
Code: 
Option Explicit
 
Sub CreateCSV()
Dim ws As Worksheet
Dim rng As Range
Dim strFileName As String
Dim FF As Long
Dim NoVals As Long
Dim arrVals
    Set ws = Worksheets("Sheet1")
    Set rng = Range("A1")
    
    strFileName = ws.Name & ".csv"
    
    FF = FreeFile()
    
    Open "C:\" & strFileName For Output As #FF
    
        While rng.Value <> ""
            
            NoVals = Application.WorksheetFunction.CountA(rng.EntireRow)
            
            If NoVals = 1 Then
                Print #FF, rng.Value
            Else
                arrVals = rng.Resize(, NoVals).Value
                arrVals = Application.WorksheetFunction.Transpose(arrVals)
                arrVals = Application.WorksheetFunction.Transpose(arrVals)
                Print #FF, Join(arrVals, ",")
            End If
            
            Set rng = rng.Offset(1)
        Wend
        
    Close #FF
    
End Sub

Created this file, called Sheet1.csv.

RunTime P:01:01:01
1233,25,69,456,123,89
RunTime P:01:01:02
5649,789,54555,4445,4445,3145
 
Upvote 0
Norie,
The code works perfect. Exactly what I was after.

cindy,
I tested you code too, but it didn't work. It created .txt file with all the commas still and that was it. Not sure what went wrong??

Thank you all for your help.
 
Upvote 0
Glad to hear that Norie's worked. Mine worked on my system...but I probably made some assumption that wasn't true on your system.
Cindy
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Getting Power Query data to an Excel worksheet
Replies
4
Views
302
MichaelMorris
M
C
  • Question
Excel VBA CSV export query
Replies
1
Views
141
cerberus1845
C
S
  • Question
CSV export to remove column
Replies
1
Views
179
StellarRumble59
S
neilp
  • Solved
Saving as a CSV
Replies
2
Views
408
neilp
neilp
B
  • Question
How to avoid scientific notation when converting to string
Replies
2
Views
453
bar_ba_dos
B

Forum statistics

Threads
1,215,869
Messages
6,127,416
Members
449,382
Latest member
DonnaRisso

Share this page

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