• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 493
  • 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 LissOlder than dirtCommented:
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 LissOlder than dirtCommented:
Instead of a "C" grade you could have asked for more help with whatever problem(s) remained.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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