• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 491
  • Last Modified:

VBA remove commas

i am trying to find some code that will remove commas from a csv file.
i am trying to import a csv file using ssis but the commas screw my fields up.
0
aneilg
Asked:
aneilg
  • 2
  • 2
2 Solutions
 
Martin LissRetired ProgrammerCommented:
Dim strBuffer As String
' Define an Integer to hold the file number
Dim FF As Integer

' Find a file number that's not in use
FF = FreeFile

Open "c:\temp\Test.txt" For Input As #FF

' The format of 'Input' is Input(how-many-bytes, file-number) and
' LOF is Length Of File so how-many-bytes is set at length of the file
strBuffer = Input(LOF(FF), FF)
strBuffer = Replace(strBuffer, "," , "|") ' replace the commas with |. You could also use " " or ""
Close #FF
Kill "c:\temp\Test.txt"
Open "c:\temp\Test.txt" For Output As #FF
Print #FF, strBuffer
Close #FF
0
 
aneilgAuthor Commented:
thank i'll give it a go.
0
 
Patrick MatthewsCommented:
Normally the comma would be your delimiter in a CSV file.

Is the problem that some of your actual values themselves have commas?
0
 
aneilgAuthor Commented:
partly answered
0
 
Martin LissRetired ProgrammerCommented:
Instead of a "C" grade you could have asked for more help with whatever problem(s) remained.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now