Difference between revisions of "Xls2csv"

From Christoph's Personal Wiki
Jump to: navigation, search
(Transpose csv files)
 
(2 intermediate revisions by the same user not shown)
Line 1: Line 1:
{{lowercase|xls2csv}}
 
 
'''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" (should be included with Spreadsheet::ParseExcel)
+
  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     : filename of the source spreadsheet
+
  -x : filename of the source spreadsheet
  -b     : the character set the source spreadsheet is in (before)
+
  -b : the character set the source spreadsheet is in (before)
  -c     : the filename to save the generated csv file as
+
  -c : the filename to save the generated csv file as
  -a     : the character set the csv file should be converted to (after)
+
  -a : the character set the csv file should be converted to (after)
  -q     : quiet mode
+
  -q : quiet mode
  -s     : print a list of supported character sets
+
  -s : print a list of supported character sets
  -h     : print help message
+
  -h : print help message
  -v     : get version information
+
  -v : get version information
  -W     : list worksheets in the spreadsheet specified by -x
+
  -W : list worksheets in the spreadsheet specified by -x
  -w     : specify the worksheet name to convert (defaults to the first worksheet)
+
  -w : specify the worksheet name to convert (defaults to the first worksheet)
  
==Transpose csv files==
+
==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:
+
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

See also

External links