By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,058 Members | 1,213 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,058 IT Pros & Developers. It's quick & easy.

problem converting .txt to excel

100+
P: 170
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
  1.  
  2.  **************************************************************
  3. # Start Excel and create new workbook with 9 sheets
  4.   use Win32::OLE qw(in valof with);
  5.   use Win32::OLE::Const 'Microsoft Excel';
  6.   use Win32::OLE::NLS qw(:DEFAULT :LANG :SUBLANG);
  7.  
  8.   my $lgid = MAKELANGID(LANG_ENGLISH, SUBLANG_DEFAULT);
  9.   $Win32::OLE::LCID = MAKELCID($lgid);
  10.  
  11.   $Win32::OLE::Warn = 3;
  12.  
  13.   my $Excel = Win32::OLE->new('Excel.Application', 'Quit');
  14.   $Excel->{SheetsInNewWorkbook} = 9;
  15.   my $Book = $Excel->Workbooks->Add;
  16.   my $Sheet = $Book->Worksheets(1);
  17.   my $sheet_count = 0;
  18.  
  19. # **************************************************************
  20. # Read each 'selected' file and load them into an array
  21.  
  22.  
  23.   my $txtName;
  24.   my $xlsRange;
  25.  
  26. foreach $file (@txtFiles) {
  27.   open (txtFILE, $file) || die "Cannot open $file $!\n";
  28.   my @rows;
  29.   $txtFile = $file;
  30.   $#rows = -1;
  31.   $#fields = -1;
  32.  
  33.   # Skip the first record (too long) of each file, we'll deal with it later
  34.   my $inBuf;                    
  35.   $sheet_count++;
  36.  
  37.   while ($inBuf = <txtFILE>) {
  38.     my @fields;
  39.     chomp($inBuf);
  40.     if (scalar @fields > $line_limit) {last;}  # Runaway safety net 
  41.  
  42.     $inBuf =~ s/^\"//;                   # Take out any LEADING or
  43.     $inBuf =~ s/\"$//;                   # TRAILING double quotes
  44.  
  45.     # OK, Process this record
  46.     @fields = split(/\,/,$inBuf);
  47. #    print "\nProcessing record ".scalar(@rows)."\n @fields";
  48.     push @rows, [@fields]; 
  49.  
  50.   } # End of while inBuf, or we exceeded our Runaway Safety Net
  51.  
  52.   close csvFILE;
  53. # *******************************************************************
  54. # Build the Spreadsheet from the CSV Array
  55. #
  56.   print "\nLoading Sheet \($sheet_count\) of $xlsFile from $txtFile \n";
  57.   $Sheet = $Book->Worksheets($sheet_count);
  58.   $csvName = substr($txtFile,0,6);       
  59.   $Sheet->{Name} = "$txtName";       
  60.  
  61.  
  62. # Add csv data to spreadsheet
  63.   print "\n\nAdding data from $csvFile to Sheet $xlsFile\\$csvName\n";
  64.   print "\tUsing a range of A4:J50 \n";
  65.   $xlsRange = sprintf("A1:J%d", 2+$#rows);
  66.   $Range = $Sheet->Range("$xlsRange");
  67.   $Range->{Value} = \@rows;         
  68.  
  69. } # End of Foreach csvFile
  70.  
  71. # *******************************************************************
  72.  
  73. # Save workbook to file $xlsFile 
  74. unlink $xlsFile if -f $xlsFile;
  75. $Book->SaveAs("$directory\\$xlsFile");
  76. $Book->Close;
  77.  
  78. print "End of import.\n";
  79.  
  80.  
Mar 5 '08 #1
Share this Question
Share on Google+
11 Replies


nithinpes
Expert 100+
P: 410
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.
Mar 5 '08 #2

100+
P: 170
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
Mar 5 '08 #3

100+
P: 170
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?
Mar 6 '08 #4

100+
P: 170
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?
Mar 6 '08 #5

nithinpes
Expert 100+
P: 410
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
  1. @fields = split(/\,/,$inBuf);
  2.  
with:

Expand|Select|Wrap|Line Numbers
  1. my @tempfields = split(/\,/,$inBuf);
  2. foreach(@tempfields) {
  3.      if($_=~/^\s*\d+\s*$/) {
  4.            push @fields,"\'$_\'"; ##put single-quotes around numbers
  5.                 }
  6.       else  { push @fields,$_;}  ## no modification for non-digit string
  7.     }
  8.  
Mar 6 '08 #6

nithinpes
Expert 100+
P: 410
If you want, you can refine this further to include quotes only around large numbers(greater than 11 digits).
Mar 6 '08 #7

100+
P: 170
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
  1. # **************************************************************
  2. # Start Excel and create new workbook with 9 sheets
  3.   use Win32::OLE qw(in valof with);
  4.   use Win32::OLE::Const 'Microsoft Excel';
  5.   use Win32::OLE::NLS qw(:DEFAULT :LANG :SUBLANG);
  6.  
  7.   my $lgid = MAKELANGID(LANG_ENGLISH, SUBLANG_DEFAULT);
  8.   $Win32::OLE::LCID = MAKELCID($lgid);
  9.  
  10.   $Win32::OLE::Warn = 3;
  11.  
  12.   my $Excel = Win32::OLE->new('Excel.Application', 'Quit');
  13.   $Excel->{SheetsInNewWorkbook} = 9;
  14.   my $Book = $Excel->Workbooks->Add;
  15.   my $Sheet = $Book->Worksheets(1);
  16.   my $sheet_count = 0;
  17.  
  18. # **************************************************************
  19. # Read each 'selected' file and load them into an array
  20.  
  21.  
  22.   my $csvName;
  23.   my $xlsRange;
  24.  
  25. foreach $file (@csvFiles) {
  26.   open (csvFILE, $file) || die "Cannot open $file $!\n";
  27.   my @rows;
  28.   $csvFile = $file;
  29.   $#rows = -1;
  30.   $#fields = -1;
  31.  
  32.   $#fields = -1;
  33.  
  34.  # Skip the 1st record of each file & deal with it later
  35.   # Declare the input buffer
  36.   my $inBuf; 
  37.  
  38.  
  39.   $sheet_count++;
  40.  
  41.   while ($inBuf = <csvFILE>) {
  42.     my @fields;
  43.     chomp($inBuf);
  44.     if (scalar @fields >= $line_limit) {last;}  # Runaway safety net 
  45.  
  46.     $inBuf =~ s/^\"//;                   # Take out any LEADING or
  47.     $inBuf =~ s/\"$//;                   # TRAILING double quotes
  48.  
  49.     # OK, Process this record
  50.     @fields = split(/\,/,$inBuf);
  51.  
  52.  
  53.     $ind_del=0;
  54.     splice(@fields,$ind_del,1);  #delete one element from that position
  55.     $ind_del=2;
  56.      splice(@fields,$ind_del,1);
  57.     $ind_del=3;
  58.      splice(@fields,$ind_del,1);
  59.     $ind_del=7;
  60.      splice(@fields,$ind_del,1);
  61.  
  62.  
  63. #    print "\nProcessing record ".scalar(@rows)."\n @fields";
  64.     push @rows, [@fields]; 
  65.  
  66.   } # End of while inBuf, or we exceeded our Runaway Safety Net
  67.  
  68.   close csvFILE;
  69. # *******************************************************************
  70. # Build the Spreadsheet from the CSV Array
  71. #
  72.   print "\nLoading Sheet \($sheet_count\) of $xlsFile from $csvFile \n";
  73.   $Sheet = $Book->Worksheets($sheet_count);
  74.   $csvName = substr($csvFile,0,6);       
  75.   $Sheet->{Name} = "$csvName";
  76.  
  77.  
  78. # Add csv data to spreadsheet
  79.   print "\n\nAdding data from $csvFile to Sheet $xlsFile\\$csvName\n";
  80.   print "\tUsing a range of A1:J50 \n";
  81.   $xlsRange = sprintf("A4:A%d", 3+$#rows);
  82.    $Range = $Sheet->Range("$xlsRange");
  83.   $Range->{Value} = \@rows; 
  84.  
  85.  
  86. } # End of Foreach csvFile
  87.  
  88. # *******************************************************************
  89.  
  90. # Save workbook to file $xlsFile 
  91. unlink $xlsFile if -f $xlsFile;
  92. $Book->SaveAs("$directory\\@csvFiles.XLS");
  93. $Book->Close;
  94.  
  95. print "End of import.\n";
  96.  
Mar 7 '08 #8

100+
P: 170
ok sorrie
i got it..
hm..but any idea how i can remove the single quotes?
i won't want the single quotes there
Mar 7 '08 #9

nithinpes
Expert 100+
P: 410
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
  1.  my $format = $workbook->add_format();
  2.    $format->set_num_format('text');
  3.  
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.
Mar 7 '08 #10

100+
P: 170
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
Mar 7 '08 #11

100+
P: 170
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
  1.  
  2.  
  3.   $Sheet->Range("A:A")->{NumberFormat} = '@';
  4.     $Sheet->Cells(4,%d)->{NumberFormat} = "\@";
  5.  
  6.  
Mar 7 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.