how to convert CSV file to EXCEL file

[poolboi]

hi guys,

i've read a lot of thread of converting excel data into CSV files.
however, i need a perl script to convert CSV file into excel file now, if required modules is needed, it would be preferable if win32::OLE is used..

currently what i know is when i change my extension as .csv file mannually, it get changed to an excel file through Microsoft Excel CSV file

so if i were to use a perl script and convert
the perl script has to open this csv file and transfer it into excel?
hm..i'm not too sure but is it like that?
hope there could be certain place where i can look up info on these

[eWish]

I would suggest that you search CPAN. You will be able to find a module to create your files in the proper format rather than doing it manually.

--Kevin

[poolboi]

hm..ook i found somewhat a module to help me
but i'm totally confused with the installation part

i'm running on windows and activeperl
read website saying to install i have to

1. perl MAKEFILE.pl
2.make
3.make test

i really dunno what this means and i;ve tried like

$ perl -MCPAN -e shell

and nothing works..i'm really very confused about the modules thingy
can anyone help?

[nithinpes]

If you trying to install manually, extract all the .gz files. In the command prompt go to the folder containing extracted files and run the following commands,

>perl makefile.pl

>nmake makefile

>nmake install makefile

If you don't have nmake installed in your system, download NMake from Microsoft Knowledge Base, article 132084 .Run the downloaded executable to extract the files to <drive>:\Perl\bin.

[nithinpes]

If you are using Active state Perl, it's always good to use PPM.
Run ppm in your command window.
If your version of Perl supports GUI-based ppm, select the module and install it from the PPM window.
If it doesn't support GUI-based, then you will get a ppm> prompt.

To search for a module:
ppm> search Win32-ole

To install:

ppm> i win32-ole

Note: ppm is not case-sensitive. Also, look out for '-' used instead of '::'

Regards,
nithin

[poolboi]

so after i type the nmake install
the files that appear in the directory

i just transfer it to <drive>:\Perl\bin?

[nithinpes]

so after i type the nmake install
the files that appear in the directory

i just transfer it to <drive>:\Perl\bin?

The last paragraph in my posting was about procedure to follow if nmake.exe is not available in your machine. It's not after typing nmake install makefile, but before you start running install commands.
From the link provided, you would have downloaded Nmake15.exe. Double-click and run Nmake15.exe, it would extract nmake.exe and few other files. Now, transfer all the extracted files(nmake.exe,Nmake15.exe etc.) to your perl directory (inside bin folder).
After this procedure, go to the path where you have extracted .gz file of module in command prompt and run the three installation commands.

I still suggest you to use PPM, it is easy and also dependent modules will be installed automatically.

[poolboi]

ok so how do i go about using ppm?

like how i run it and stuff like dat?

[nithinpes]

ok so how do i go about using ppm?

like how i run it and stuff like dat?

I have mentioned in detail about that in my previous posting. Just type ppm
in your command window and go ahead.

[poolboi]

hm..i've installed the package
but i'm aren't sure if the package i need is install
it seems it isn't..

[KevinADC]

hm..i've installed the package
but i'm aren't sure if the package i need is install
it seems it isn't..

If you have activestate perl installed, all the documentation is included. Open the activestate folder and then open the documentation folder. There is a ton of information that comes with it, all the perl documentation, all the module documentation, and then a lot of activeperl specfic documentation, it is to your benefit to start reading that documentation.

[poolboi]

hm..all i see are pm files
no sort of documentation files
:)

[KevinADC]

start>>programs>>activestate>>documentation

or however it is for your version of windows

[poolboi]

ok thanks for yr help
but ultimately i'm still trying to figure out how to create a perl program where i can put data from my csv file into an excel sheet so that it's used for reasy referencing
any ideas pls help out? thanks
i'm really stuck on this

[KevinADC]

ok thanks for yr help
but ultimately i'm still trying to figure out how to create a perl program where i can put data from my csv file into an excel sheet so that it's used for reasy referencing
any ideas pls help out? thanks
i'm really stuck on this

Sorry, I don't know.

[tjewkes67]

If you haven't figured it out yet, I just yesterday performed the same task. I used the win32::OLE module. If you are already to the point where you have created the .xls file, (or alternately have opened an existing one) all you need to do is call the the saveas procedure like this:

$book->SaveAs ("path/to/file.csv", xlCSV);

thats it. It uses OLE to get Excel to do the conversion.

[poolboi]

hm...ok
let me clarify things

you mention create the .xls file
hm..in my folder i have the csv file already generated from some application, do u mean if i change the extension to .csv, it creates an excel file but in the csv formate with all the commas?

and then by applying the saveAs function it converts the file without the commas into an excel sheet file??

am i right? correct me if i'm wrong..thanks :)

[poolboi]

and there's a problem
i think the above code saveAs only helps convert excel to a csv file
it does't change from csv to excel when i tried

ok this is what my code looks like

 
use Win32::OLE;
    use Win32::OLE::Const 'Microsoft Excel';    
 
        # use existing instance if Excel is already running
        eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
        die "Excel not installed" if $@;
        unless (defined $ex) {
            $ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
                    or die "Oops, cannot start Excel";
        }
 
    $book = $ex->Workbooks->Open("C:\\Documents and Settings\\clong\\Desktop\\perl2\\81124578_PID2008-0043.csv");
 
    $book->SaveAs ("C:\\Documents and Settings\\clong\\Desktop\\perl2\\81124578_PID2008-0043.csv", xlcsv ) ;
        undef $book;
        undef $ex;
 
 

[nithinpes]

Try to save it as excel file(.xls). I feel that should work. Though, I have not used Win32::OLE, I had accomplished a similar task in Python where it was just the matter of opening CSV file and saving it as .xls.
Hope, it is the same case here.

[poolboi]

hm...i tried it doesn't work
anyway that comment given does help change to an excel file, but u have to mannually choose which file to save as

but it's weird
u see opening it in csv file and in xls format what difference does it make?
cos both use excel
i thought saving it xls format eliminates all the commas but nope...the display in both this format is still the same
puzzled over this

[nithinpes]

hm...i tried it doesn't work
anyway that comment given does help change to an excel file, but u have to mannually choose which file to save as

but it's weird
u see opening it in csv file and in xls format what difference does it make?
cos both use excel
i thought saving it xls format eliminates all the commas but nope...the display in both this format is still the same
puzzled over this

No idea !! I had done similar job in Python. There, it had worked pretty well.

I hope 'tjewkes67 ' will get back to you with the solution.

[poolboi]

ok i've managed to solve the problem by transferring data over to excel
now there;s a problem
my csv files has thousand of rows
and each information is different
but when i print it's printing the same information from row 2 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 @rows;
  my @fields;
  my $csvName;
  my $xlsRange;
 
foreach $file (@csvFiles) {
  open (csvFILE, $file) || die "Cannot open $file $!\n";
  $csvFile = $file;
  $#rows = -1;     
  $#fields = -1;
 
  # Skip the first record (too long) of each file, we'll deal with it later
  my $inBuf = <csvFILE>;                    
  $sheet_count++;
 
  while ($inBuf = <csvFILE>) {
    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 $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 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]

@rows array should be local inside the while loop. Since, you are passing reference to the array as in:

$Range->{Value} = \@rows; 
 

This points to the same location throughout the iteration of while, though value referred changes.
Instead of declaring @rows at the beginning of the script, declare it inside the while loop so that @rows is created from scratch with each iteration:

 while ($inBuf = <csvFILE>) {
   my @rows;
   ########
}
 

I think this will solve the problem.

[poolboi]

@rows array should be local inside the while loop. Since, you are passing reference to the array as in:

$Range->{Value} = \@rows; 
 

This points to the same location throughout the iteration of while, though value referred changes.
Instead of declaring @rows at the beginning of the script, declare it inside the while loop so that @rows is created from scratch with each iteration:

 while ($inBuf = <csvFILE>) {
   my @rows;
   ########
}
 

I think this will solve the problem.

hm...i got an empty excel sheet this time
haha

[nithinpes]

hm...i got an empty excel sheet this time
haha

Oops!!! I will check :)

[nithinpes]

On second observation, I feel the following order for declaring @rows and @fields is right.
@rows local inside foreach

foreach $file (@csvFiles) {
  my @rows;
 

and @fields local inside while.

 while ($inBuf = <csvFILE>) {
 my @fields;
 

Check if this works. Just out of curiosity, where have you defined @csvFiles inside your script?

[poolboi]

ok it's done thanks...
erm my @csvfiles contains thousand of rows of data that is generated from a csv file

for example:

cost time duration place
5 34 23 zoo

and there's thousands of data of rows inside..
now i got to solve 2 more problems with this script

1) first line of data cannot be printed out
2) need to delete a column of useless inform like for example time then the excel file contains only

cost duration place
5 34 zoo

yup..still working on this

[poolboi]

oh sorrie misread yr question
my @csvfiles i define it before this section of the script i show on the forum
there;s still somemore before the win32:::OLE module part

[nithinpes]

ok it's done thanks...
erm my @csvfiles contains thousand of rows of data that is generated from a csv file

for example:

cost time duration place
5 34 23 zoo

and there's thousands of data of rows inside..
now i got to solve 2 more problems with this script

1) first line of data cannot be printed out
2) need to delete a column of useless inform like for example time then the excel file contains only

cost duration place
5 34 zoo

yup..still working on this

Let me take these requirements one by one.
1) first line of data cannot be printed out
-You can use a counter variable and skip the first line read.

my $i=0;  #counter variable
while ($inBuf = <csvFILE>) {
 $i++;
 next if($i==1);  # skip first line
 #######
 

2) need to delete a column of useless inform like for example time
- Hope you will be knowing the column number/ the index of the element which you want to delete. After reading @fields, you can delete that element.

@fields = split(',',$inBuf);
#assuming time is the second element(index 1)
$ind_del=1;
splice(@fields,$ind_del,1); #delete one element from that position
push @rows, [@fields]; 
 

In case you have to delete multiple columns(without knowing index) after searching for patterns(e.g time) , you can parse through array (first line), get the index of the element/s which match the pattern and use this index in splice.

Hope, this is what you need.

[poolboi]

ok about the deleting of rows there's a bit of problem
i outline it here...

my original csv file consists of :

1 2 3 4 5 6 7 8 9 10 I N N N I N

after going through the original code i paste above it becomes

1 2 3 4 5 6 7 8 9 10

i need to delete certain rows, so using yr splice code i deleted 1 and it becomes

1 2 3 4 5 6 7 8 9 I

it's like deleting 10 and adding I,
but i just want 10 deleted..hmm..quite weird
so it's like deleting 2 and adding 2 again back ]

2) hm...about the first line not printed out
i actually wanted it printed out but in the script it skips the first line
tried to amend but can't get the first line printed out

hm..no hurry take yr time
i'm trying as well :)
thanks

[poolboi]

ok i figured out how to use the splicing thanks a lot dude~

hm...now left one more problem
any idea why my problems still doesn't print the first line out?
i still can't spot the mistake

[KevinADC]

ok i figured out how to use the splicing thanks a lot dude~

hm...now left one more problem
any idea why my problems still doesn't print the first line out?
i still can't spot the mistake

Post your most current code.

[poolboi]

its alright guys
i spotted the mistake got it done

just changed from this:

 # Skip the 1st record of each file & deal with it later
  my $inBuf = <csvFILE>; 
 

to this:

my $inBuf
 

i just declare the input buffer so that it doesn't do a read no my csvfile
so when it goes through the while loop
it prints the first line out
thans guys for yr help