Delete special characters from multiple csv files using batch file

Question

I want to delete all the special characters in my csv file using a batch file. My csv file has one column of only keywords to be entered in google

For example 1.Ecommerce 2.dentist Melbourne cbd? 3.dentists Melbourne % 4.best dentist in Melbourne!

Sometimes I can have Aracbic/Chinese Characters as well and so on.

Here When I add these files to GoogleAdwords-Keyword Planner, it shows me an error, on ignoring error i get wrong no. of hits for keyword and to avoid error i need to remove all the special characters from my csv file.

I have Hundreds of csv files and want to save the updated(Without special characters) file to the existing file.

I tried

@echo off
set source_folder=C:\Users\Username\Documents\iMacros\Datasources\a
set target_folder=C:\Users\Username\Documents\iMacros\Datasources\keyfords-csv-file
if not exist %target_folder% mkdir %target_folder%

for /f %%A in ('dir /b %source_folder%\*.csv') do (
    for /f "skip=1 tokens=1,2* delims=," %%B in (%source_folder%\%%A) do (
    echo %%B>>%target_folder%\%%A
    )
)

timeout /t 20

But ended up Deleting all the records from csv file.

Is there anyway by which i can either

1.Accept only Standard Characters which would be from A-Z, a-z, and 0-9.

2.Or Delete all the string where I can put special characters in that string. Like string1="?%!@#$^&*<>"

3.Or is there anyway by which i can mention in csv file to accept only Standard English Characters Is there any way to achieve this using a batch file or any framework?

Thanks

Could you define "special characters" or for that matter "ordinary characters?" — Magoo
– Magoo, Commented Nov 12, 2014 at 6:22
I assume he's talking about the standard batch poison characters - parentheses, exclamation points, ampersands, pipes, carets, question marks, and asterisks. — SomethingDark
– SomethingDark, Commented Nov 12, 2014 at 6:23
I want to upload these csv files to google keyword planner. it shows an error saying "Keywords cannot contain non-standard characters like: ! @ % , * On row 1: !!!!?????? Keywords cannot contain non-standard characters like: ! @ % , * On row 905: Ø³ØªØ§Ø¦Ø± ÙÙØªÙØ±ÙØ©" Not sure how to go about — Penny
– Penny, Commented Nov 12, 2014 at 6:36
This task is better to solve with more high-level language than batch. — Fr0sT
– Fr0sT, Commented Feb 3, 2016 at 6:44

kevmar · Accepted Answer · 2014-11-25 04:54:13Z

1

I think this is much cleaner in Powershell.

$sourceFolder = "C:\Users\Username\Documents\iMacros\Datasources\a"
$targetFolder = "C:\Users\Username\Documents\iMacros\Datasources\keyfords-csv-file"
MkDir $targetFolder -ErrorAction Ignore

$fileList = Dir $sourceFolder -Filter *.csv 

ForEach($file in $fileList)
{
    $file | Get-Content | %{$_ -replace '[^\w\s,\"\.]',''} | Set-Content -Path "$targetFolder\$file"
}

I take every file from the source folder, get the contents, replace any character that is not wanted, and save it to another file. I use a little regex right in the middle '[^\w\s,\"\.]' with the replace command. The carrot ^ is a not match operator. So anything that does not match a word character \w, space character \s, a coma ,, double quote \", or a period \.

Someone may find a better regex for your needs, but I think you get the idea.

answered Nov 25, 2014 at 4:54

kevmar

8086 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lizz Over a year ago

Can you post the answer using batch? The user, @Penny, is looking for help using a batch file.

Bloodied · Accepted Answer · 2015-10-21 17:52:33Z

0

Technically you could have a series of:

set variable=%variable:"=%
set variable=%variable:(=%
set variable=%variable:)=%
set variable=%variable:&=%
set variable=%variable:%=%

And so on. I know this would be an annoyance to write all the special characters..

Seeing there would be less letters in the alphabet than "special characters" a findstr could be done on the file/folder name, if a letter from a-z is found true, write and move to the next character.

_Arescet

answered Oct 21, 2015 at 17:52

Bloodied

9757 silver badges21 bronze badges

1 Comment

user6250760 Over a year ago

maybe you can use a for loop.... for /f %%p in (^= ^( ^) ^& %%) do set variable=%variable:%%p=%

Collectives™ on Stack Overflow

Delete special characters from multiple csv files using batch file

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related