469,366 Members | 2,366 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,366 developers. It's quick & easy.

how to convert CSV file to EXCEL file

170 100+
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
Feb 13 '08 #1
32 9997
eWish
971 Expert 512MB
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
Feb 14 '08 #2
poolboi
170 100+
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?
Feb 14 '08 #3
nithinpes
410 Expert 256MB
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.
Feb 14 '08 #4
nithinpes
410 Expert 256MB
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
Feb 14 '08 #5
poolboi
170 100+
so after i type the nmake install
the files that appear in the directory

i just transfer it to <drive>:\Perl\bin?
Feb 14 '08 #6
nithinpes
410 Expert 256MB
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.
Feb 14 '08 #7
poolboi
170 100+
ok so how do i go about using ppm?

like how i run it and stuff like dat?
Feb 14 '08 #8
nithinpes
410 Expert 256MB
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.
Feb 14 '08 #9
poolboi
170 100+
hm..i've installed the package
but i'm aren't sure if the package i need is install
it seems it isn't..
Feb 15 '08 #10
KevinADC
4,059 Expert 2GB
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.
Feb 15 '08 #11
poolboi
170 100+
hm..all i see are pm files
no sort of documentation files
:)
Feb 15 '08 #12
KevinADC
4,059 Expert 2GB
start>>programs>>activestate>>documentation

or however it is for your version of windows
Feb 15 '08 #13
poolboi
170 100+
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
Feb 15 '08 #14
KevinADC
4,059 Expert 2GB
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.
Feb 15 '08 #15
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.
Feb 15 '08 #16
poolboi
170 100+
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 :)
Feb 18 '08 #17
poolboi
170 100+
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. use Win32::OLE;
  3.     use Win32::OLE::Const 'Microsoft Excel';    
  4.  
  5.         # use existing instance if Excel is already running
  6.         eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
  7.         die "Excel not installed" if $@;
  8.         unless (defined $ex) {
  9.             $ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
  10.                     or die "Oops, cannot start Excel";
  11.         }
  12.  
  13.     $book = $ex->Workbooks->Open("C:\\Documents and Settings\\clong\\Desktop\\perl2\\81124578_PID2008-0043.csv");
  14.  
  15.     $book->SaveAs ("C:\\Documents and Settings\\clong\\Desktop\\perl2\\81124578_PID2008-0043.csv", xlcsv ) ;
  16.         undef $book;
  17.         undef $ex;
  18.  
  19.  
Feb 18 '08 #18
nithinpes
410 Expert 256MB
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.
Feb 18 '08 #19
poolboi
170 100+
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
Feb 18 '08 #20
nithinpes
410 Expert 256MB
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.
Feb 18 '08 #21
poolboi
170 100+
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

Expand|Select|Wrap|Line Numbers
  1. # Start Excel and create new workbook with 9 sheets
  2.   use Win32::OLE qw(in valof with);
  3.   use Win32::OLE::Const 'Microsoft Excel';
  4.   use Win32::OLE::NLS qw(:DEFAULT :LANG :SUBLANG);
  5.  
  6.   my $lgid = MAKELANGID(LANG_ENGLISH, SUBLANG_DEFAULT);
  7.   $Win32::OLE::LCID = MAKELCID($lgid);
  8.  
  9.   $Win32::OLE::Warn = 3;
  10.  
  11.   my $Excel = Win32::OLE->new('Excel.Application', 'Quit');
  12.   $Excel->{SheetsInNewWorkbook} = 9;
  13.   my $Book = $Excel->Workbooks->Add;
  14.   my $Sheet = $Book->Worksheets(1);
  15.   my $sheet_count = 0;
  16.  
  17. # **************************************************************
  18. # Read each 'selected' file and load them into an array
  19.  
  20.   my @rows;
  21.   my @fields;
  22.   my $csvName;
  23.   my $xlsRange;
  24.  
  25. foreach $file (@csvFiles) {
  26.   open (csvFILE, $file) || die "Cannot open $file $!\n";
  27.   $csvFile = $file;
  28.   $#rows = -1;     
  29.   $#fields = -1;
  30.  
  31.   # Skip the first record (too long) of each file, we'll deal with it later
  32.   my $inBuf = <csvFILE>;                    
  33.   $sheet_count++;
  34.  
  35.   while ($inBuf = <csvFILE>) {
  36.     chomp($inBuf);
  37.     if (scalar @fields > $line_limit) {last;}  # Runaway safety net 
  38.  
  39.     $inBuf =~ s/^\"//;                   # Take out any LEADING or
  40.     $inBuf =~ s/\"$//;                   # TRAILING double quotes
  41.  
  42.     # OK, Process this record
  43.     @fields = split(/\,/,$inBuf);
  44. #    print "\nProcessing record ".scalar(@rows)."\n @fields";
  45.     push @rows, [@fields]; 
  46.  
  47.   } # End of while inBuf, or we exceeded our Runaway Safety Net
  48.  
  49.   close csvFILE;
  50. # *******************************************************************
  51. # Build the Spreadsheet from the CSV Array
  52. #
  53.   print "\nLoading Sheet \($sheet_count\) of $xlsFile from $csvFile \n";
  54.   $Sheet = $Book->Worksheets($sheet_count);
  55.   $csvName = substr($csvFile,0,6);       
  56.   $Sheet->{Name} = "$csvName";       
  57.  
  58. # Add csv data to spreadsheet
  59.   print "\n\nAdding data from $csvFile to Sheet $xlsFile\\$csvName\n";
  60.   print "\tUsing a range of A4:J50 \n";
  61.   $xlsRange = sprintf("A1:J%d", 2+$#rows);
  62.   $Range = $Sheet->Range("$xlsRange");
  63.   $Range->{Value} = \@rows;         
  64.  
  65. } # End of Foreach csvFile
  66.  
  67. # *******************************************************************
  68.  
  69. # Save workbook to file $xlsFile 
  70. unlink $xlsFile if -f $xlsFile;
  71. $Book->SaveAs("$directory\\$xlsFile");
  72. $Book->Close;
  73.  
  74. print "End of import.\n";
  75.  
Feb 19 '08 #22
nithinpes
410 Expert 256MB
@rows array should be local inside the while loop. Since, you are passing reference to the array as in:
Expand|Select|Wrap|Line Numbers
  1. $Range->{Value} = \@rows; 
  2.  
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:
Expand|Select|Wrap|Line Numbers
  1.  while ($inBuf = <csvFILE>) {
  2.    my @rows;
  3.    ########
  4. }
  5.  
I think this will solve the problem.
Feb 19 '08 #23
poolboi
170 100+
@rows array should be local inside the while loop. Since, you are passing reference to the array as in:
Expand|Select|Wrap|Line Numbers
  1. $Range->{Value} = \@rows; 
  2.  
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:
Expand|Select|Wrap|Line Numbers
  1.  while ($inBuf = <csvFILE>) {
  2.    my @rows;
  3.    ########
  4. }
  5.  
I think this will solve the problem.
hm...i got an empty excel sheet this time
haha
Feb 19 '08 #24
nithinpes
410 Expert 256MB
hm...i got an empty excel sheet this time
haha
Oops!!! I will check :)
Feb 19 '08 #25
nithinpes
410 Expert 256MB
On second observation, I feel the following order for declaring @rows and @fields is right.
@rows local inside foreach
Expand|Select|Wrap|Line Numbers
  1. foreach $file (@csvFiles) {
  2.   my @rows;
  3.  
and @fields local inside while.
Expand|Select|Wrap|Line Numbers
  1.  while ($inBuf = <csvFILE>) {
  2.  my @fields;
  3.  
Check if this works. Just out of curiosity, where have you defined @csvFiles inside your script?
Feb 19 '08 #26
poolboi
170 100+
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
Feb 20 '08 #27
poolboi
170 100+
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
Feb 20 '08 #28
nithinpes
410 Expert 256MB
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.
Expand|Select|Wrap|Line Numbers
  1. my $i=0;  #counter variable
  2. while ($inBuf = <csvFILE>) {
  3.  $i++;
  4.  next if($i==1);  # skip first line
  5.  #######
  6.  
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.
Expand|Select|Wrap|Line Numbers
  1. @fields = split(',',$inBuf);
  2. #assuming time is the second element(index 1)
  3. $ind_del=1;
  4. splice(@fields,$ind_del,1); #delete one element from that position
  5. push @rows, [@fields]; 
  6.  
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.
Feb 20 '08 #29
poolboi
170 100+
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
Feb 21 '08 #30
poolboi
170 100+
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
Feb 21 '08 #31
KevinADC
4,059 Expert 2GB
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.
Feb 21 '08 #32
poolboi
170 100+
its alright guys
i spotted the mistake got it done

just changed from this:
Expand|Select|Wrap|Line Numbers
  1.  # Skip the 1st record of each file & deal with it later
  2.   my $inBuf = <csvFILE>; 
  3.  
to this:
Expand|Select|Wrap|Line Numbers
  1. my $inBuf
  2.  
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
Feb 21 '08 #33

Post your reply

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

Similar topics

1 post views Thread by thomson | last post: by
2 posts views Thread by noopathan | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.