• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 542
  • Last Modified:

Greek character substitution in Excell spreadsheets / CSV files

I have a  bunch of comma separated files (CSV), extracted from EXCEL  spreadsheets,  which contain the lowercase Greek character “theta”. I need to change that character to “t”, so that other non-MS programs can process those CSV files. Assume that all files starting with AA and BB in a given directory Z:\WORK   need to be processed.

What is the VB program/macro that can do this job?

  • 2
1 Solution
Jose ParrotGraphics ExpertCommented:

Assuming that the only non-English character is the lower case theta, the following macro will turn all theta to lower "t" in a spreasheet:
Sub MacroTheta()
' MacroTheta Macro
    Cells.Select    ' this selects the entire page
    Selection.Replace What:="?", Replacement:="t", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
End Sub

what you need is to open the files "AA*.csv", run MacroTheta and save "AA*.csv".
Are the files organized as AA01.csv, AA02.csv, etc.? If this is the case, you can read and save the modified files with AA01modified.csv names, by using another macro that reads, calls MacroTheta and saves the files.

If the names can't be called as described above, you can generate a list at the prompt with:
dir AA*.csv /b >list.txt

then open list.txt in excel (it will read into a collumn), create a new collumn with new filenames for the modified files and read first collumn line by line (with a for statement) as names for files to be open, run MacroTheta, and saves by using the names in the second collumn.

Simple, but runs.

eruskovAuthor Commented:
This macro changed all worksheet characters to t !!!
Try using the unix-type command tr, which can be used to change the characters in the cvs file, something like:

tr "\xxx" "t" <ExcelOutput.csv >updatedOutput.csv

where "xxx" is the octal value of the theta character, which varies depending on the encoding you're using.
eruskovAuthor Commented:
Thanks, that worked!

CSV fils are ASCII, so it was possible to massage them on a UNIX computer.

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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