Adjust CSV Output to Remove Xtra Commas

S

steve2115

Board Regular
Joined
Mar 17, 2014
Messages
82
Need help modifying the below code to meet my requirement. Any help would be greatly appreciated. I have 100's of files to convert.


Code: 
Option Explicit
 
Sub CreateCIF()
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 & ".cif"
    
    FF = FreeFile()
    
    Open "C:\Users\skeebaug\Desktop\" & strFileName For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 
    
        While rng.Value <> ""
            
            NoVals = Application.WorksheetFunction.CountA(rng.EntireRow)
            
            If NoVals = 1 Then
                Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] , rng.Value
            Else
                arrVals = rng.Resize(, NoVals).Value
                arrVals = Application.WorksheetFunction.Transpose(arrVals)
                arrVals = Application.WorksheetFunction.Transpose(arrVals)
                Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] , Join(arrVals, ",")
            End If
            
            Set rng = rng.Offset(1)
        Wend
        
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 
    
End Sub


Here is the layout of data in excel. In this example there are only 2 rows of "catalog data" (rows 13&14), however other catalogs will have more

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[TD]P
[/TD]
[TD]Q
[/TD]
[TD]R
[/TD]
[TD]S
[/TD]
[TD]T
[/TD]
[TD]U
[/TD]
[TD]V
[/TD]
[TD]W
[/TD]
[TD]X
[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]CIF_I_V3.0
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]CHARSET:
[/TD]
[TD]UTF-8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]LOADMODE:
[/TD]
[TD]F
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]CODEFORMAT:
[/TD]
[TD]UNSPSC_V13.5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]CURRENCY:
[/TD]
[TD]USD
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]SUPPLIERID_DOMAIN:
[/TD]
[TD]NetworkID
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]ITEMCOUNT:
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]TIMESTAMP:
[/TD]
[TD]8/4/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]UNUOM:
[/TD]
[TD]TRUE
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]COMMENTS:
[/TD]
[TD]992966_8552_VIBRATION ANALYSTS INC_060217
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]FIELDNAMES: Supplier ID
[/TD]
[TD]Supplier Part ID
[/TD]
[TD]Manufacturer Part ID
[/TD]
[TD]Item Description
[/TD]
[TD]SPSC Code
[/TD]
[TD]Unit Price
[/TD]
[TD]Unit of Measure
[/TD]
[TD]Lead Time
[/TD]
[TD]Manufacturer Name
[/TD]
[TD]Supplier URL
[/TD]
[TD]Manufacturer URL
[/TD]
[TD]Market Price
[/TD]
[TD]Supplier Part Auxiliary ID
[/TD]
[TD]Language
[/TD]
[TD]Currency
[/TD]
[TD]Short Name
[/TD]
[TD]Image
[/TD]
[TD]Thumbnail
[/TD]
[TD]UNSPSC
[/TD]
[TD]MinimumQuantity
[/TD]
[TD]QuantityInterval
[/TD]
[TD]SimilarItems
[/TD]
[TD] Effective Date
[/TD]
[TD] Expiration Date
[/TD]
[TD]Keywords
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]DATA
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD][/TD]
[TD]VAI001
[/TD]
[TD]VAI001
[/TD]
[TD]Vibration Analysis
[/TD]
[TD]41113320
[/TD]
[TD]10
[/TD]
[TD]EA
[/TD]
[TD]5
[/TD]
[TD]Vibration Analysts, Inc.
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]USD
[/TD]
[TD]Vibration Analysis
[/TD]
[TD][/TD]
[TD][/TD]
[TD]41113320
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Vibration
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD][/TD]
[TD]VAI002
[/TD]
[TD]VAI002
[/TD]
[TD]Vibration Equipment Fee
[/TD]
[TD]41113320
[/TD]
[TD]81
[/TD]
[TD]EA
[/TD]
[TD]5
[/TD]
[TD]Vibration Analysts, Inc.
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]USD
[/TD]
[TD]Vibration Equipment Fee
[/TD]
[TD][/TD]
[TD][/TD]
[TD]41113320
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Vibration
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]ENDOFDATA
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]























Here is the current output results of code.

Code: 
CIF_I_V3.0
CHARSET:,UTF-8
LOADMODE:,F
CODEFORMAT:,UNSPSC_V13.5
CURRENCY:,USD
SUPPLIERID_DOMAIN:,NetworkID
ITEMCOUNT:,2
TIMESTAMP:,8/4/2017
UNUOM:,True
COMMENTS:,992966_8552_VIBRATION ANALYSTS INC_060217
FIELDNAMES: Supplier ID,Supplier Part ID,Manufacturer Part ID,Item Description,SPSC Code,Unit Price,Unit of Measure,Lead Time,Manufacturer Name,Supplier URL,Manufacturer URL,Market Price,Supplier Part Auxiliary ID,Language,Currency,Short Name,Image ,Thumbnail,UNSPSC,MinimumQuantity,QuantityInterval,SimilarItems, Effective Date, Expiration Date,Keywords
DATA

Here is the EXPECTED results
Code: 
CIF_I_V3.0
CHARSET:UTF-8
LOADMODE:F
CODEFORMAT:UNSPSC_V13.5
CURRENCY:USD
SUPPLIERID_DOMAIN:NetworkID
ITEMCOUNT:2
TIMESTAMP:8/4/2017
UNUOM:TRUE
COMMENTS:992966_8552_VIBRATION ANALYSTS INC_060217
FIELDNAMES: Supplier ID,Supplier Part ID,Manufacturer Part ID,Item Description,SPSC Code,Unit Price,Unit of Measure,Lead Time,Manufacturer Name,Supplier URL,Manufacturer URL,Market Price,Supplier Part Auxiliary ID,Language,Currency,Short Name,Image ,Thumbnail,UNSPSC,MinimumQuantity,QuantityInterval,SimilarItems, Effective Date, Expiration Date,Keywords
DATA
,VAI001,VAI001,Vibration Analysis,41113320,29,EA,5,"Vibration Analysts, Inc.",,,,,,USD,Vibration Analysis,Image Url,,41113320,1,1,,,,Vibration
,VAI002,VAI002,Vibration Equipment Fee,41113320,81.91,EA,5,"Vibration Analysts, Inc.",,,,,,USD,Vibration Equipment Fee,,,41113320,1,1,,,,Vibration
ENDOFDATA

Need code to also remove the leading comma right after each of the header fields


CHARSET:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]UT[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000][/COLOR]F-8
LOADMODE:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]F
CODEFORMAT:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]U[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000][/COLOR]NSPSC_V13.5
CURRENCY:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]USD
SUPPLIERID_DOMAIN:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]N[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000][/COLOR]etworkID
ITEMCOUNT:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]2
TIMESTAMP:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]8[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000][/COLOR]/4/2017
UNUOM:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]True
COMMENTS:[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF]#FF[/URL] 0000],[/COLOR]92966_8552_VIBRATION ANALYSTS INC_060217

Code is also removing the all data below "DATA" (cell A12 on excel table). Need this to stay.
ENDOFDATA should be the last line of text with no trailing commas.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
You must log in or register to reply here.

Similar threads

S
  • Question Question
help with existing vb code
Replies
0
Views
620
steve2115
S

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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