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
32 10877
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
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?
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.
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
so after i type the nmake install
the files that appear in the directory
i just transfer it to <drive>:\Perl\bin?
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.
ok so how do i go about using ppm?
like how i run it and stuff like dat?
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.
hm..i've installed the package
but i'm aren't sure if the package i need is install
it seems it isn't..
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.
hm..all i see are pm files
no sort of documentation files
:)
start>>programs>>activestate>>documentation
or however it is for your version of windows
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
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.
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.
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 :)
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;
-
-
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.
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
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.
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";
-
@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.
@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
hm...i got an empty excel sheet this time
haha
Oops!!! I will check :)
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?
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
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
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.
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
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
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.
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:
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: thomson |
last post by:
Hi All,
Is there any way to convert an Excel File to pdf in asp.net
Thanks in Advance
Thomson
|
by: noopathan |
last post by:
Hi experts ,
I have an excel file in the below format
---------------------------------------------------------------------
CodeID CodeName Market Name Date...
|
by: fkulaga |
last post by:
Hi all,
I have a problem with the issue in the subject, i have all data in one
big excel file,
in a denormalized form, and on the other side, i have mysql database
with many tables, which is...
|
by: Steve |
last post by:
Using VB.NET, How do I convert a .txt file extension to a .xls file
extension? The text file is already created and saved in a folder on the
server.
Thanks
Steven
|
by: Satwant |
last post by:
Hi,
How to convert a excel file to .csv format?
Please provide me a basic C program code to do this funcionality.
Thanks
|
by: Anish G |
last post by:
Hi All,
I am getting the below given error while running my application in live server. In my local machine, its working fine. Please help me as it is very urgent for me.
Exception from...
|
by: roswara |
last post by:
Dear all,
Currently, I am working on a project to make a web-based application using ASP 2.0 and C#. This application will ask user to input for an excel file which has graphs in it. Then the...
|
by: pankaj gugale |
last post by:
how do i read a excel file in c#.net
|
by: nikolasapl |
last post by:
Is there a command so that instead of displaying the result table (for example the result of the following query) in the grid tab in the SQL Server Analyzer environment, to export it into an .xls...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
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...
|
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...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
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...
| |