9

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

Penny
  • 824
  • 1
  • 14
  • 31
  • 2
    Could you define "special characters" or for that matter "ordinary characters?" – Magoo Nov 12 '14 at 06:22
  • I assume he's talking about the standard batch poison characters - parentheses, exclamation points, ampersands, pipes, carets, question marks, and asterisks. – SomethingDark Nov 12 '14 at 06:23
  • 1
    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 Nov 12 '14 at 06:36
  • 1
    This task is better to solve with more high-level language than batch. – Fr0sT Feb 03 '16 at 06:44

2 Answers2

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.

kevmar
  • 808
  • 6
  • 6
  • Can you post the answer using batch? The user, @Penny, is looking for help using a batch file. – Lizz Dec 10 '14 at 04:47
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

Bloodied
  • 1,004
  • 7
  • 20
  • maybe you can use a `for` loop.... `for /f %%p in (^= ^( ^) ^& %%) do set variable=%variable:%%p=%` –  Jan 22 '17 at 04:26