473,388 Members | 1,073 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,388 software developers and data experts.

problem converting .txt to excel

170 100+
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
11 4513
nithinpes
410 Expert 256MB
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
poolboi
170 100+
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
poolboi
170 100+
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
poolboi
170 100+
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
410 Expert 256MB
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
410 Expert 256MB
If you want, you can refine this further to include quotes only around large numbers(greater than 11 digits).
Mar 6 '08 #7
poolboi
170 100+
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
poolboi
170 100+
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
410 Expert 256MB
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
poolboi
170 100+
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
poolboi
170 100+
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

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

Similar topics

4
by: M. Uppal | last post by:
How do i convert fraction to time in c#. I need to convert 0.25 to time. Excel does this by using Selection.NumberFormat = "h:mm:ss;@" of the Cell Format function. thanks, M. Uppal
1
by: Ramakrishnan Nagarajan | last post by:
Hi, I am converting Excel data into a Dataset in C#. There are around 24 columns in the Excel Sheet. First I tried to insert one row with correct values in the Excel sheet. i.e. for text columns...
0
by: dleh | last post by:
I have been tasked with producing a server application that will convert excel files to html. I have no experience of developing under Windows, (but plenty of UNIX/C++ experience) but I picked...
1
by: UKuser | last post by:
Hi Guys, I have a program which converts Excel spreadsheets to Javascript and allows interactivity. However it can't convert it to PHP, which is obviously better for users to view (in case J/S...
2
by: rwiegel | last post by:
I'm trying to read rows from an Excel file and display them in an ASP.NET DataGridview. I am using C# for the code file. I am using OleDb to read from the Excel file. The columns that contain...
1
by: chrspta | last post by:
I am new to Visual basic. I need a program using VB6 that converts txt files to excel file.Description is in the below: The form should have the Drive list, Dir list, file list and cmdConvert...
18
by: Dirk Hagemann | last post by:
Hello, From a zone-file of a Microsoft Active Directory integrated DNS server I get the date/time of the dynamic update entries in a format, which is as far as I know the hours since january 1st...
1
by: PW | last post by:
Hi, When I run the following command, some fields are ending up blank when the clearly have values them in Excel. I have tried converting the columns to general and text. The ones that are...
1
by: =?Utf-8?B?bGF3ODc4Nw==?= | last post by:
i am looking for some MS Excel formula or MS Visal Basic Marco for converting Date to Lunar Date. tks
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.