Difference between revisions of "Xls2csv"
From Christoph's Personal Wiki
(→Transpose csv files) |
|||
| (2 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
| − | |||
'''xls2csv''' is a [[:Category:Linux Command Line Tools|command line]] script that recodes a spreadsheet's charset and saves as CSV. It was written by Ken Prows. | '''xls2csv''' is a [[:Category:Linux Command Line Tools|command line]] script that recodes a spreadsheet's charset and saves as CSV. It was written by Ken Prows. | ||
| Line 6: | Line 5: | ||
perl -MCPAN -e "install Unicode::Map" | perl -MCPAN -e "install Unicode::Map" | ||
perl -MCPAN -e "install Spreadsheet::ParseExcel" | perl -MCPAN -e "install Spreadsheet::ParseExcel" | ||
| − | perl -MCPAN -e "install Spreadsheet::ParseExcel::FmtUnicode" | + | perl -MCPAN -e "install Spreadsheet::ParseExcel::FmtUnicode" # should be included with Spreadsheet::ParseExcel |
perl -MCPAN -e "install Text::CSV_XS" | perl -MCPAN -e "install Text::CSV_XS" | ||
wget http://search.cpan.org/CPAN/authors/id/K/KE/KEN/xls2csv-1.06.tar.gz | wget http://search.cpan.org/CPAN/authors/id/K/KE/KEN/xls2csv-1.06.tar.gz | ||
| − | == Basic usage == | + | ==Basic usage== |
The following example will convert a spreadsheet that is in the WINDOWS-1252 character set (WinLatin1) and save it as a csv file in the UTF-8 character set. | The following example will convert a spreadsheet that is in the WINDOWS-1252 character set (WinLatin1) and save it as a csv file in the UTF-8 character set. | ||
| Line 21: | Line 20: | ||
==Options== | ==Options== | ||
| − | -x | + | -x : filename of the source spreadsheet |
| − | -b | + | -b : the character set the source spreadsheet is in (before) |
| − | -c | + | -c : the filename to save the generated csv file as |
| − | -a | + | -a : the character set the csv file should be converted to (after) |
| − | -q | + | -q : quiet mode |
| − | -s | + | -s : print a list of supported character sets |
| − | -h | + | -h : print help message |
| − | -v | + | -v : get version information |
| − | -W | + | -W : list worksheets in the spreadsheet specified by -x |
| − | -w | + | -w : specify the worksheet name to convert (defaults to the first worksheet) |
| − | ==Transpose | + | ==Transpose CSV files== |
| − | If you need to transpose your | + | If you need to transpose your CSV matrix before using it (i.e., rows->columns and columns->rows), try the following little [[Perl]] script: |
# Example CSV file | # Example CSV file | ||
# FROM: | # FROM: | ||
| Line 43: | Line 42: | ||
# a3,b3 | # a3,b3 | ||
| − | perl -e '$unequal=0;$_=<>;s/\r?\n//;@out_rows=split(/\,/, $_);\ | + | perl -e'$unequal=0;$_=<>;s/\r?\n//;@out_rows=split(/\,/, $_);\ |
$num_out_rows=$#out_rows+1;while(<>){s/\r?\n//;@F=split(/\,/,$_);\ | $num_out_rows=$#out_rows+1;while(<>){s/\r?\n//;@F=split(/\,/,$_);\ | ||
| − | foreach $i (0 .. $#F){$out_rows[$i] .="\,$F[$i]";}if($num_out_rows !=$#F+1){\ | + | foreach $i(0 .. $#F){$out_rows[$i] .="\,$F[$i]";}if($num_out_rows !=$#F+1){\ |
$unequal=1;}}END{foreach $row (@out_rows){print"$row\n"}\ | $unequal=1;}}END{foreach $row (@out_rows){print"$row\n"}\ | ||
warn "\nWARNING! Rows in input had different numbers of columns\n" if $unequal;\ | warn "\nWARNING! Rows in input had different numbers of columns\n" if $unequal;\ | ||
| − | warn "\nTransposed table: result has $. columns and $num_out_rows rows\n\n" }' $1 | + | warn "\nTransposed table: result has $. columns and $num_out_rows rows\n\n"}' $1 |
==See also== | ==See also== | ||
Latest revision as of 05:00, 17 May 2007
xls2csv is a command line script that recodes a spreadsheet's charset and saves as CSV. It was written by Ken Prows.
Install
perl -MCPAN -e "install Locale::Recode" perl -MCPAN -e "install Unicode::Map" perl -MCPAN -e "install Spreadsheet::ParseExcel" perl -MCPAN -e "install Spreadsheet::ParseExcel::FmtUnicode" # should be included with Spreadsheet::ParseExcel perl -MCPAN -e "install Text::CSV_XS"
wget http://search.cpan.org/CPAN/authors/id/K/KE/KEN/xls2csv-1.06.tar.gz
Basic usage
The following example will convert a spreadsheet that is in the WINDOWS-1252 character set (WinLatin1) and save it as a csv file in the UTF-8 character set.
xls2csv -x "1252spreadsheet.xls" -b WINDOWS-1252 -c "ut8csvfile.csv" -a UTF-8
This example with convert the worksheet named "Users" in the given spreadsheet.
xls2csv -x "multi_worksheet_spreadsheet.xls" -w "Users" -c "users.csv"
Options
-x : filename of the source spreadsheet -b : the character set the source spreadsheet is in (before) -c : the filename to save the generated csv file as -a : the character set the csv file should be converted to (after) -q : quiet mode -s : print a list of supported character sets -h : print help message -v : get version information -W : list worksheets in the spreadsheet specified by -x -w : specify the worksheet name to convert (defaults to the first worksheet)
Transpose CSV files
If you need to transpose your CSV matrix before using it (i.e., rows->columns and columns->rows), try the following little Perl script:
# Example CSV file # FROM: # a1,a2,a3 # b1,b2,b3 # TO: # a1,b1 # a2,b2 # a3,b3
perl -e'$unequal=0;$_=<>;s/\r?\n//;@out_rows=split(/\,/, $_);\
$num_out_rows=$#out_rows+1;while(<>){s/\r?\n//;@F=split(/\,/,$_);\
foreach $i(0 .. $#F){$out_rows[$i] .="\,$F[$i]";}if($num_out_rows !=$#F+1){\
$unequal=1;}}END{foreach $row (@out_rows){print"$row\n"}\
warn "\nWARNING! Rows in input had different numbers of columns\n" if $unequal;\
warn "\nTransposed table: result has $. columns and $num_out_rows rows\n\n"}' $1
