exodius Posted April 28, 2006 Share Posted April 28, 2006 I found this in an old thread, but no one had a good suggestion for them.. Given the new possibilities with AutoIt, is there a way to convert a .xls file to a .csv file without having to actively go through Excel? Maybe using the Excel UDF some way that I couldn't understand? Thanks. Link to comment Share on other sites More sharing options...
exodius Posted April 29, 2006 Author Share Posted April 29, 2006 (edited) My solution to my problem/need... Was to take a perl script that could convert .xls files to .csv and make it a compiled executable.I'm putting it up at http://www.autoitscript.com/fileman/users/exodius/xls2csv.zip so if anyone wants to try it to solve their own problem then it's available. Sorry for the fact that it's precompiled, but it has to be in order to work on Windows... I'll post the perl script in a subsequent post, but largely I guess you'll have to trust that it's not a virus.exodius Edited April 29, 2006 by exodius Link to comment Share on other sites More sharing options...
randallc Posted April 29, 2006 Share Posted April 29, 2006 (edited) Hi, Or, in ExcelCom.au3; [see syntax in script udf] ; _XLSaveAs($FilePath,$NewPath[,$FileType],$s_i_Visible) ; "csv"or "xls", or "txt" , or "csv"Best, Randall Edited April 29, 2006 by randallc ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
exodius Posted April 29, 2006 Author Share Posted April 29, 2006 expandcollapse popup#!/usr/bin/perl use strict; use 5.006; use Getopt::Std; use Locale::Recode; use Spreadsheet::ParseExcel; use Spreadsheet::ParseExcel::FmtUnicode; use Text::CSV_XS; our $VERSION = '1.06'; =head1 NAME xls2csv - A script that recodes a spreadsheet's charset and saves as CSV. =head1 DESCRIPTION This script will recode a spreadsheet into a different character set and output the recoded data as a csv file. The script came about after many headaches from dealing with Excel spreadsheets from clients that were being received in various character sets. =head1 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) =head1 EXAMPLE 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" =head1 NOTES The spreadsheet's charset (-b) will default to UTF-8 if not set. If the csv's charset (-a) is not set, the CSV file will be created using the same charset as the spreadsheet. =head1 REQUIRED MODULES This script requires the following modules: Locale::Recode Unicode::Map Spreadsheet::ParseExcel Spreadsheet::ParseExcel::FmtUnicode (should be included with Spreadsheet::ParseExcel) Text::CSV_XS =head1 CAVEATS It probably will not work work with spreadsheets that use formulas. A line in the spreadsheet is assumed to be blank if there is nothing in the first column. Some users have reported problems trying to convert a spreadsheet while it was opened in a different application. You should probably make sure that no other programs are working with the spreadsheet while you are converting it. =cut $Getopt::Std::STANDARD_HELP_VERSION = 1; my %O; getopts('x:b:c:a:qshvWw:', \%O); HELP_MESSAGE() if !%O or $O{'h'}; VERSION_MESSAGE() if $O{'v'}; if ($O{'s'}) { print "\nThe following character sets are supported:\n\n"; my $Supported = Locale::Recode->getSupported; foreach my $CharSet (sort @$Supported) { print "$CharSet\n"; } print "\n"; exit; } my $SourceFilename = $O{'x'} || die "The filename of the spreadsheet (-x) is required."; my $SourceCharset = $O{'b'}; $SourceCharset = 'UTF-8' unless $SourceCharset; unless ($O{'q'}) { print "Now reading \"$SourceFilename\" as $SourceCharset.\n"; } my $XLS = new IO::File; $XLS->open("< $SourceFilename") || die "Cannot open spreadsheet: $!"; my $Formatter = Spreadsheet::ParseExcel::FmtUnicode->new(Unicode_Map => $SourceCharset); my $Book = Spreadsheet::ParseExcel::Workbook->Parse($XLS, $Formatter) || die "Can't read spreadsheet!"; if ($O{'W'}) { print "\nThe following " . ($Book->{SheetCount}>1 ? "$Book->{SheetCount} worksheets are" : "worksheet is") . " defined in the spreadsheet:\n\n"; foreach my $Sheet (@{$Book->{Worksheet}}) { print "$Sheet->{Name}\n"; } print "\n"; exit; } my $DestFilename = $O{'c'} || die "The filename to save the csv file as (-c) is required."; my $DestCharset = $O{'a'}; $DestCharset = $SourceCharset unless $DestCharset; my $Sheet; if ($O{'w'}) { $Sheet = $Book->Worksheet($O{'w'}); die "Invalid worksheet" if !defined $Sheet; unless ($O{'q'}) { print qq|Converting the "$Sheet->{Name}" worksheet.\n|; } } else { ($Sheet) = @{$Book->{Worksheet}}; if (!$O{'q'} && $Book->{SheetCount}>1) { print qq|Multiple worksheets found. Will convert the "$Sheet->{Name}" worksheet.\n|; } } open CSV, "> $DestFilename" || die "Cannot create csv file: $!"; binmode CSV; my $Csv = Text::CSV_XS->new({ 'quote_char' => '"', 'escape_char' => '"', 'sep_char' => ',', 'binary' => 1, }); my $Recoder; if ($O{'a'}) { $Recoder = Locale::Recode->new(from=>$SourceCharset, to=>$DestCharset); } for ( my $Row = $Sheet->{MinRow}; defined $Sheet->{MaxRow} && $Row <= $Sheet->{MaxRow}; $Row++ ) { my @Row; for ( my $Col = $Sheet->{MinCol}; defined $Sheet->{MaxCol} && $Col <= $Sheet->{MaxCol}; $Col++ ) { my $Cell = $Sheet->{Cells}[$Row][$Col]; my $Value = ""; if ($Cell) { $Value = $Cell->Value; if ($Value eq 'GENERAL') { # Sometimes numbers are read incorrectly as "GENERAL". # In this case, the correct value should be in ->{Val}. $Value = $Cell->{Val}; } if ($O{'a'}) { $Recoder->recode($Value); } } # We assume the line is blank if there is nothing in the first column. last if $Col == $Sheet->{MinCol} and !$Value; push(@Row, $Value); } next unless @Row; my $Status = $Csv->combine(@Row); if (!$O{'q'} and !defined $Status) { my $Error = $Csv->error_input(); warn "ERROR FOUND!: $Error"; } if (defined $Status) { my $Line = $Csv->string(); print CSV "$Line\n"; } } close CSV; $XLS->close; unless ($O{'q'}) { print "The spreadsheet has been converted to $DestCharset and saved as \"$DestFilename\".\n"; } sub VERSION_MESSAGE { print << "EOF"; This is xls2csv version $VERSION Copyright (C) 2005 Ken Prows. All rights reserved. This script is free software; you can redistribute it and\\or modify it under the same terms as Perl itself. For help, use "xls2csv -h" EOF exit; } sub HELP_MESSAGE { print << "EOF"; xls2csv - Recode a spreadsheet's charset and save as CSV. usage: xls2csv -x spreadsheet.xls [-w worksheet] [-b charset] [-c csvfile.csv] [-a charset] [-qshvW] -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 : this 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) example: xls2csv -x "spreadsheet.xls" -b WINDOWS-1252 -c "csvfile.csv" -a UTF-8 EOF exit; } =head1 AUTHOR Ken Prows (perl@xev.net) =head1 COPYRIGHT Copyright (C) 2005 Ken Prows. All rights reserved. This script is free software; you can redistribute it and/or modify it under the same terms as Perl itself. =cut Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now