I have many cells which I'd like to convert such that the first letter in every cell is capitalized. E.g. cook, chef, fireman becomes Cook, Chef, Fireman.
How can I change the first letter of every cell in the spreadsheet to uppercase?
I happen to do this task. You have to install Spreadsheet::ParseExcel and Spreadsheet::WriteExcel modules.
use strict;
use warnings;
use Spreadsheet::ParseExcel::SaveParser;
my $parser = Spreadsheet::ParseExcel::SaveParser->new();
my $workbook = $parser->Parse('Book1.xls');
if ( !defined $workbook ) {
die $parser->error(), ".\n";
}
for my $worksheet ( $workbook->worksheets() ) {
my ( $row_min, $row_max ) = $worksheet->row_range();
my ( $col_min, $col_max ) = $worksheet->col_range();
for my $row ( $row_min .. $row_max ) {
for my $col ( $col_min .. $col_max ) {
my $cell = $worksheet->get_cell( $row, $col );
next unless $cell;
# "ucfirst lc" make sure that only the first letter is capitalized
# if you dont like that just remove lc
$worksheet->AddCell( $row, $col, ucfirst lc $cell->value() );
}
}
}
# ofcouse save your work
$workbook->SaveAs('Book2.xls');
Maybe you just need to udpate to a more recent version. I'm using LibreOffice 3.4.4 and I see Format -> Change Case -> Sentence case which I'd say does exactly what you need.
Is Perl an option? There's a module in the CPAN archive called Spreadsheet::WriteExcel that can also handle OpenOffice spreadsheets too.
You basically read in the spreadsheet, search the cells, modify the ones you want, and the create a new spreadsheet with the modified data. You cannot directly modify an Excel spreadsheet. I don't know if this applies to OpenOffice documents.
I used this before to read and write Excel spreadsheets, but that was a while ago. However, this module can handle your situation without first having to save the spreadsheet in CSV format.
I have an awk script that will do what you want (I think).
Here is my test intput (test.input):
cook, chef, fireman
cook, chef, fireman
cook, chef, fireman
cook, chef, fireman
house, farm, road
My awk script (up.awk):
# from: http://www.gnu.org/software/gawk/manual/html_node/Join-Function.html
function join(array, start, end, sep, result, i){
if (sep == "")
sep = " "
else if (sep == SUBSEP) # magic value
sep = ""
result = array[start]
for (i = start + 1; i <= end; i++)
result = result sep array[i]
return result
}
BEGIN {
FS="\n";
}
{
# split input on newline
for(i=1;i<=NF;i++) {
# split line on the commas
size = split($i, s, ",")
for(ii=1;ii<=size;ii++) {
# trim whitespace
gsub(/[[:space:]]*/,"",s[ii])
# uppercase first char and glue it back together
s[ii] = toupper(substr(s[ii], 0, 1)) substr(s[ii], 2)
}
# join array back and print it out
print join(s, 1, size, ", ")
}
}
How I run the script:
awk -f up.awk test.input >test.output
The output in my test.output:
Cook, Chef, Fireman
Cook, Chef, Fireman
Cook, Chef, Fireman
Cook, Chef, Fireman
House, Farm, Road
Perl one-liner:
perl -pwe 's/(?:^|,)\s*\K([a-z])/\U$1/g;' file.csv > fileout.csv
You can also use in-place edit with the -i switch. E.g. perl -pi.bak -we ...
Longer version, using Text::CSV_XS. This module will (probably) treat your csv files with more care.
use strict;
use warnings;
use Text::CSV_XS;
my $csv = Text::CSV_XS->new({
binary => 1,
eol => $/,
});
my $file = shift;
open my $fh, '<', $file or die $!;
while (my $row = $csv->getline($fh)) {
s/([a-z])/\U$1/ for @$row;
$csv->print (*STDOUT, $row);
}
Run it with: perl script.pl file.csv > fileout.csv
you could do something pretty dodgy with python or any other language.
#!/usr/bin/env python
lastchar = ""
orig_csv = open("yourfile.csv", "r")
new_csv = open("newfile.csv", "w")
for line in orig_csv:
for char in line:
if lastchar == ",":
new_csv.write(char.upper())
else:
new_csv.write(char)
orig_csv.close()
new_csv.close()
This assumes that none of the fields are enclosed in quotes, because it'll just upcase everything after a comma.
Failing that, you'll want to actually use a csv library.
If you are using, or if you upgrade to, OOo 3.3 there are options for this built in.
Mark all the cells you want to affect, then choose Format -> Change Case -> Sentence case and voila! See if that does the trick for you?
Export it as a csv. Then do something like this.
#psuedo python
out=""
nums = [col numbers to capitalize]
for line in file.read_csv('input.csv'):
for n in nums:
line[n] = line[n].capitalize()
out += ",".join(line) + "\n"
You can write up a small gawk and sed combo to get title case -
gawk -v RS="[,\n]" -v ORS="" '
{a=substr($1,1,1); b=substr($1,2); print toupper(a)b" "; if (NR%3==0) print "\n"}' file |
sed -e 's/ /, /g' -e 's/, *$//'
Test:
[jaypal:~/Temp] cat file
cook, chef, fireman
cook, chef, fireman
cook, chef, fireman
cook, chef, fireman
house, farm, road
[jaypal:~/Temp] gawk -v RS="[,\n]" -v ORS="" '
{a=substr($1,1,1); b=substr($1,2); print toupper(a)b" "; if (NR%3==0) print "\n"}' file |
sed -e 's/ /, /g' -e 's/, *$//'
Cook, Chef, Fireman
Cook, Chef, Fireman
Cook, Chef, Fireman
Cook, Chef, Fireman
House, Farm, Road
Obviously this isn't the perfect solution, you might have to tweak it a little bit depending on how many rows of data you have or want. Changes to NR%3 should be good enough.
This might work for you:
cat <<! | sed 's/\<[^,]*/\u&/g'
> cook, chef, fireman
> head cook, head chef, chief fireman
> house, farm, road
> !
Cook, Chef, Fireman
Head cook, Head chef, Chief fireman
House, Farm, Road
Probably GNU sed specific.
