VBA code TO remove extra commas from csv file

Hi All,
does anyone have a vba snippet/function that will remove superfluous commas from inside string fields in a csv?
(e.g. address field has a comma inside the text)
Ideally I would like to iterate through the file using FSO, passing a whole line ('record') to a function, and get the fixed record back
thank you
LVL 7
COACHMAN99Asked:
Who is Participating?
 
RobSampsonCommented:
Without having some example content, see what this Powershell code produces for you.

Regards,

Rob.

$InputCSVFile = Import-CSV C:\Temp\Scripts\Test.csv
$OutputCSVFile = Import-CSV C:\Temp\Scripts\Test2.csv
$ScriptBlock = "`$CSVFile | Select "
$CSVFile | Get-Member -MemberType NoteProperty | Select -ExpandProperty Name | ForEach {
    If ($ScriptBlock.Substring($ScriptBlock.Length -1) -eq " ") {
        $ScriptBlock += "@{n='$_';e={`$_.$_ -replace ',', ''}}"
    } else {
        $ScriptBlock += ",@{n='$_';e={`$_.$_ -replace ',', ''}}"
    }
}
Invoke-Expression $ScriptBlock | Export-CSV $OutputCSVFile -NoTypeInformation

Open in new window

0
 
David Johnson, CD, MVPOwnerCommented:
how is one supposed to differentiate between a needed and a superfluous comma?
0
 
COACHMAN99Author Commented:
by a complex method of counting quotes and commas - hence the need for pre-coded function.
Unfortunately, after closer inspection, these csv files do not enclose the text-fields in quotes, so your question is valid, and leaves me without an answer :-)   (I probably have to cancel the question)
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Jeffrey CoachmanMIS LiasonCommented:
Yes,
Please provide a sample of the text uncorrected, and corrected

In the basic sense, you can remove a comma (and replace it with a space-bar character) with an expression like this:
Replace([txtAddress],","," ")
0
 
COACHMAN99Author Commented:
Rob. Thanks for the input.
I tried it but didn't get meaningful output. Maybe I'm not using powershell properly.
I changed the two file params to my files/paths.
It seems it is trying to add a space after a comma?
Assuming I make a ps1 file and add the above code, is the process to run it via a batch file? and what would the calling line in the batch file be?
cheers
0
 
COACHMAN99Author Commented:
Jeff. an example of the data:
BB2287,03/04/2015,1,2.0,3.4,I,10.40,7.40,7.60,0,V5A3G1,,1,Loomis Ground,C,10.39,0,1.09,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,GS,0,0,11.48,11.48,NET42414623,DRAEGER CANADA LTD,2425 SKYMARK AVE,UNIT 1&2,,MISSISSAUGA,ON,L4W4Y6,"HAZMASTERS, INC.",3103 THUNDERBIRD CRESCENT,,,BURNABY,BC,V5A3G1,,80928027,,,,5312342,23,03/06/2015

the errant comma is in ', INC,'

I will code to find double quotes and then remove any commas until another double-quote.
I only want to process a line if there are too many commas. Anyone know how to get 'count of commas' in a sentence efficiently?
0
 
COACHMAN99Author Commented:
useful input/dialog. no definitive solution.
0
 
IrogSintaCommented:
Anyone know how to get 'count of commas' in a sentence efficiently?
One way is just to loop through each character of the sentence using the MID Function and count the commas:
For i = 1 to Len(sentence)
   If Mid(sentence, i,1) = "," Then iCount = iCount +1
Next

Open in new window


Or you could use the Replace function along with the Len function like this:
iCount = Len(sentence) - Len(Replace(sentence, ",",""))

Open in new window


Ron
0
 
IrogSintaCommented:
I guess you really didn't care to get an answer to your other question, huh.
0
 
COACHMAN99Author Commented:
true. looping to count commas isn't efficient.
I already knew about the replace function but appreciate all input.
good night all
0
 
RobSampsonCommented:
Hi, with your test sample, I realise my code had some errors.  I have corrected it, and it now works correctly, EXCEPT for the fact it seems to order the output columns alphabetically.  If you can verify that it works on your files, I'll work out a way to replicate the input order.

Rob.

$InputCSVFile = Import-CSV "C:\Temp\Scripts\Test.csv"
$OutputCSVFile = "C:\Temp\Scripts\Test2.csv"
$ScriptBlock = "`$InputCSVFile | Select "
$InputCSVFile | Get-Member -MemberType NoteProperty | Select -ExpandProperty Name | ForEach {
    If ($ScriptBlock.Substring($ScriptBlock.Length -1) -eq " ") {
        $ScriptBlock += "@{n='$_';e={`$_.$_ -replace ',', ''}}"
    } else {
        $ScriptBlock += ",@{n='$_';e={`$_.$_ -replace ',', ''}}"
    }
}
Invoke-Expression $ScriptBlock | Export-CSV $OutputCSVFile -NoTypeInformation

Open in new window

0
 
Bill PrewCommented:
Just curious, based on the Topics you selected, and the wording of your original question, are you working in Access, using VBA?  If so why not just use Access to Import the CSV into a table, it should be able to break the CSV data apart at the proper places, ignoring commas inside quoted fields.  Then work with the data as a table in Access, removing commas from fields if still needed.

~bp
0
 
COACHMAN99Author Commented:
Access 2010 doesn't appear to consider the double quotes and creates extra fields when importing.
And Access 2013 fails to import because the dates aren't 'date and time'
0
 
COACHMAN99Author Commented:
correction
we are good on the double-quotes point.
2013 has date issues but I have closed this case and moving on.
thanks
0
 
RobSampsonCommented:
OK, so even if you're not going to look at this, I took it as a challenge, and have come up with the following code that will strip all commas from within field values, and output a CSV without double quotes.  Since you wanted commas removed, I assume you also want quotes removed.  If you want to leave double quotes around all fields and values, use the second line in the code for output, rather than the last.

Regards,

Rob.

$InputCSVFile = "C:\Temp\Scripts\Test.csv"
$OutputCSVFile = "C:\Temp\Scripts\Test2.csv"
$CSVFile = Import-CSV $InputCSVFile
$ScriptBlock = "`$CSVFile | Select "
$CSVFile.PSObject.Properties | Where MemberType -eq NoteProperty | Foreach {
    If ($ScriptBlock.Substring($ScriptBlock.Length -1) -eq " ") {
        $ScriptBlock += "@{n='$($_.Name)';e={`$_.$($_.Name) -replace ',', ''}}"
    } else {
        $ScriptBlock += ",@{n='$($_.Name)';e={`$_.$($_.Name) -replace ',', ''}}"
    }
}
#Invoke-Expression $ScriptBlock | Export-CSV $OutputCSVFile -NoTypeInformation
Invoke-Expression $ScriptBlock | ConvertTo-Csv -NoTypeInformation | ForEach { $_ -replace '"', ""} | Out-File $OutputCSVFile

Open in new window

0
 
COACHMAN99Author Commented:
Thanks Rob
the client is using Excel to find/replace all commas so anything further would be purely 'academic'
I am not familiar with Powershell or the usage thereof. Please briefly indicate the steps I would take to test/execute your code. (I know I need to replace your paths with mine).
Thanks
Clive
0
 
RobSampsonCommented:
Hi Clive.  There are a couple of way to run Powershell scripts.

First, save the code into a file, and name it with the .PS1 extension.  For example, C:\Temp\Scripts\ConvertCSVFile.ps1

Method 1
========
Click the Start button in Windows, and run Powershell.exe

Type
& 'C:\Temp\Scripts\ConvertCSVFile.ps1'
and press Enter

Method 2
========
Click the Start button in Windows, and run Powershell_ISE.exe
Open the script, and press F5 to run it.

Method 3
========
In a batch file, use
powershell -ExecutionPolicy RemoteSigned -File "C:\Temp\Scripts\ConvertCSVFile.ps1"


Rob.
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.

All Courses

From novice to tech pro — start learning today.