Solved

VBA remove commas

Posted on 2012-03-14
5
474 Views
Last Modified: 2012-03-30
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
Comment
Question by:aneilg
  • 2
  • 2
5 Comments
 
LVL 45

Accepted Solution

by:
Martin Liss earned 155 total points
ID: 37720628
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
 

Author Comment

by:aneilg
ID: 37720685
thank i'll give it a go.
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 155 total points
ID: 37722400
Normally the comma would be your delimiter in a CSV file.

Is the problem that some of your actual values themselves have commas?
0
 

Author Closing Comment

by:aneilg
ID: 37786889
partly answered
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 37787290
Instead of a "C" grade you could have asked for more help with whatever problem(s) remained.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now