problem converting .txt to excel

[poolboi]

hi guys,

below is a script that i need yr help on
i got this to print out a csv to excel
i'm trying to modify it to read a .txt file to convert an excel
hm...doesn't seem to work still any idea why it can't run?

is it cos i should not be open my file in the win32::OLE module?

 
 **************************************************************
# Start Excel and create new workbook with 9 sheets
  use Win32::OLE qw(in valof with);
  use Win32::OLE::Const 'Microsoft Excel';
  use Win32::OLE::NLS qw(:DEFAULT :LANG :SUBLANG);
 
  my $lgid = MAKELANGID(LANG_ENGLISH, SUBLANG_DEFAULT);
  $Win32::OLE::LCID = MAKELCID($lgid);
 
  $Win32::OLE::Warn = 3;
 
  my $Excel = Win32::OLE->new('Excel.Application', 'Quit');
  $Excel->{SheetsInNewWorkbook} = 9;
  my $Book = $Excel->Workbooks->Add;
  my $Sheet = $Book->Worksheets(1);
  my $sheet_count = 0;
 
# **************************************************************
# Read each 'selected' file and load them into an array
 
 
  my $txtName;
  my $xlsRange;
 
foreach $file (@txtFiles) {
  open (txtFILE, $file) || die "Cannot open $file $!\n";
  my @rows;
  $txtFile = $file;
  $#rows = -1;
  $#fields = -1;
 
  # Skip the first record (too long) of each file, we'll deal with it later
  my $inBuf;                    
  $sheet_count++;
 
  while ($inBuf = <txtFILE>) {
    my @fields;
    chomp($inBuf);
    if (scalar @fields > $line_limit) {last;}  # Runaway safety net 
 
    $inBuf =~ s/^\"//;                   # Take out any LEADING or
    $inBuf =~ s/\"$//;                   # TRAILING double quotes
 
    # OK, Process this record
    @fields = split(/\,/,$inBuf);
#    print "\nProcessing record ".scalar(@rows)."\n @fields";
    push @rows, [@fields]; 
 
  } # End of while inBuf, or we exceeded our Runaway Safety Net
 
  close csvFILE;
# *******************************************************************
# Build the Spreadsheet from the CSV Array
#
  print "\nLoading Sheet \($sheet_count\) of $xlsFile from $txtFile \n";
  $Sheet = $Book->Worksheets($sheet_count);
  $csvName = substr($txtFile,0,6);       
  $Sheet->{Name} = "$txtName";       
 
 
# Add csv data to spreadsheet
  print "\n\nAdding data from $csvFile to Sheet $xlsFile\\$csvName\n";
  print "\tUsing a range of A4:J50 \n";
  $xlsRange = sprintf("A1:J%d", 2+$#rows);
  $Range = $Sheet->Range("$xlsRange");
  $Range->{Value} = \@rows;         
 
} # End of Foreach csvFile
 
# *******************************************************************
 
# Save workbook to file $xlsFile 
unlink $xlsFile if -f $xlsFile;
$Book->SaveAs("$directory\\$xlsFile");
$Book->Close;
 
print "End of import.\n";
 
 

[nithinpes]

What format are you expecting? I am getting the output from your script, with data from each text file written in each sheet of the excel file.

[poolboi]

What format are you expecting? I am getting the output from your script, with data from each text file written in each sheet of the excel file.

hm..yeah
that's what i want...but there seems to be some error
it's not working the script
i'm not gettng any output

[poolboi]

ok i've got it working thanks

erm..but there a problem

my excel sheet has numbers that are too long
like for example:

526236273282392323

they output in excel :

5.26+E14

any idea how to display it as the original format?
and not as the E14 format?

[poolboi]

hm..okok another better idea of the problem
it outputting as a general type..
i need to format it to a text type for the cell using win32::OLE
anyone know how?

[nithinpes]

hm..okok another better idea of the problem
it outputting as a general type..
i need to format it to a text type for the cell using win32::OLE
anyone know how?

The problem is not with the module or the perl script. It is the property of Excel to truncate large numbers in the exponential format. Only way is to print all the numbers into excel sheet within single/double quotes. You would invariably end up having all numbers printed within quotes.
If that is okay, replace the line:

@fields = split(/\,/,$inBuf);
 

with:

my @tempfields = split(/\,/,$inBuf);
foreach(@tempfields) {
     if($_=~/^\s*\d+\s*$/) {
           push @fields,"\'$_\'"; ##put single-quotes around numbers
                }
      else  { push @fields,$_;}  ## no modification for non-digit string
    }
 

[nithinpes]

If you want, you can refine this further to include quotes only around large numbers(greater than 11 digits).

[poolboi]

right i just place my recent code for yr reference:

let me define it a bit further cos i tried yr method, doesn't seem to work
i got a .txt file containing the following information:


91000000,34526812233930
72788171, 345268812233931
112332332, 34526800812233932
1 rows selected

and i've done some functions in my script to get rid of the 1st column in text file and the "1 row selected"
so basically i'm left with the long number, still unable to get the original format out..
hm..pls help? thanks

# **************************************************************
# Start Excel and create new workbook with 9 sheets
  use Win32::OLE qw(in valof with);
  use Win32::OLE::Const 'Microsoft Excel';
  use Win32::OLE::NLS qw(:DEFAULT :LANG :SUBLANG);
 
  my $lgid = MAKELANGID(LANG_ENGLISH, SUBLANG_DEFAULT);
  $Win32::OLE::LCID = MAKELCID($lgid);
 
  $Win32::OLE::Warn = 3;
 
  my $Excel = Win32::OLE->new('Excel.Application', 'Quit');
  $Excel->{SheetsInNewWorkbook} = 9;
  my $Book = $Excel->Workbooks->Add;
  my $Sheet = $Book->Worksheets(1);
  my $sheet_count = 0;
 
# **************************************************************
# Read each 'selected' file and load them into an array
 
 
  my $csvName;
  my $xlsRange;
 
foreach $file (@csvFiles) {
  open (csvFILE, $file) || die "Cannot open $file $!\n";
  my @rows;
  $csvFile = $file;
  $#rows = -1;
  $#fields = -1;
 
  $#fields = -1;
 
 # Skip the 1st record of each file & deal with it later
  # Declare the input buffer
  my $inBuf; 
 
 
  $sheet_count++;
 
  while ($inBuf = <csvFILE>) {
    my @fields;
    chomp($inBuf);
    if (scalar @fields >= $line_limit) {last;}  # Runaway safety net 
 
    $inBuf =~ s/^\"//;                   # Take out any LEADING or
    $inBuf =~ s/\"$//;                   # TRAILING double quotes
 
    # OK, Process this record
    @fields = split(/\,/,$inBuf);
 
 
    $ind_del=0;
    splice(@fields,$ind_del,1);  #delete one element from that position
    $ind_del=2;
     splice(@fields,$ind_del,1);
    $ind_del=3;
     splice(@fields,$ind_del,1);
    $ind_del=7;
     splice(@fields,$ind_del,1);
 
 
#    print "\nProcessing record ".scalar(@rows)."\n @fields";
    push @rows, [@fields]; 
 
  } # End of while inBuf, or we exceeded our Runaway Safety Net
 
  close csvFILE;
# *******************************************************************
# Build the Spreadsheet from the CSV Array
#
  print "\nLoading Sheet \($sheet_count\) of $xlsFile from $csvFile \n";
  $Sheet = $Book->Worksheets($sheet_count);
  $csvName = substr($csvFile,0,6);       
  $Sheet->{Name} = "$csvName";
 
 
# Add csv data to spreadsheet
  print "\n\nAdding data from $csvFile to Sheet $xlsFile\\$csvName\n";
  print "\tUsing a range of A1:J50 \n";
  $xlsRange = sprintf("A4:A%d", 3+$#rows);
   $Range = $Sheet->Range("$xlsRange");
  $Range->{Value} = \@rows; 
 
 
} # End of Foreach csvFile
 
# *******************************************************************
 
# Save workbook to file $xlsFile 
unlink $xlsFile if -f $xlsFile;
$Book->SaveAs("$directory\\@csvFiles.XLS");
$Book->Close;
 
print "End of import.\n";
 

[poolboi]

ok sorrie
i got it..
hm..but any idea how i can remove the single quotes?
i won't want the single quotes there

[nithinpes]

ok sorrie
i got it..
hm..but any idea how i can remove the single quotes?
i won't want the single quotes there

As I said before, excel uses scientific notation to display large numbers. If you don't want single quotes around large numbers, then you have to bear with this notation.

There is one workaround, that is to change the cell to text format.
In Spreadsheet::WriteExcel , you get methods to change format of cells like:

 my $format = $workbook->add_format();
   $format->set_num_format('text');
 

But, I don't know if Win32::OLE supports it. I haven't explored much on the SetProperty() method available in Win32::OLE.

But, even in this case when you have number formatted as text, you will get a green triangle at left corner of the cell indicating a possible error for putting number in text formatted cell.

So, you have to choose between green triangle next to large numbers and single-quotes. I am stressing again that this is more to do with Excel application and not with your script.

[poolboi]

hm...i think there's some sort like format in win32::OLe
i aren't too sure and i'm trying to find out too

yeah i know about the green triangle thingy
cos when i actually use this script on a csv file it works, but when i use it on a text file it doesn't so i'm pretty puzzle by it
in any case i can't have the quotes 'cos this inputs is gonna be read by another system which apparrently can't read the quotes..haha
but in any case thanks for yr help

[poolboi]

ok done found it..
just in case ppl who want to know how it's done in script using win32::OLE
here's how:

 
 
  $Sheet->Range("A:A")->{NumberFormat} = '@';
    $Sheet->Cells(4,%d)->{NumberFormat} = "\@";