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?

Expand|Select|Wrap|Line Numbers

  
 **************************************************************

# 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:

Expand|Select|Wrap|Line Numbers

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

with:

Expand|Select|Wrap|Line Numbers

 
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

Expand|Select|Wrap|Line Numbers

 
# **************************************************************

# 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:

Expand|Select|Wrap|Line Numbers

 
 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:

Expand|Select|Wrap|Line Numbers

  
  $Sheet->Range("A:A")->{NumberFormat} = '@';

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

problem converting .txt to excel

Post your reply

Similar topics

Question stats

	Ask Question
Home Questions Articles Browse Topics Latest Top Members FAQ