Perl_Output_file_VBA

[ozzz]

Hi

I have an urgentt problem .

I get some data from a server using a batch file. The output is a big csv file, I filter and parse the data using Perl and insert it into an Access Database.

The problem occurs at this point. I want to run a query and as a result crreate another table and send it automatically to a specific email address through Lotus Notes. The query is ok and table is created successfully. But for the mailing part I use VBA. I need this to be done every day as a scheduled task so I write the VBA macro in Excel and tell in the code to execute this VBA macro every time the Excel file is open.As a scheduled task ı add to open this Excel file daily. I have written all the mailing code, and it works on an ordinary Excel file.

The problem is it does not execute a Perl script. Okey it does but only simple perl scripts like a "hello" printing on a message box. I can not make VBA to create the output file of my Perl command which is a csv file.

Can someone please direct me ??

Or do u think any other way to do this getting data, parsing it, inserting into database, running a query and emailing process ?


Thanks

Ozz

[orange]

Hi

I have an urgentt problem .

I get some data from a server using a batch file. The output is a big csv file, I filter and parse the data using Perl and insert it into an Access Database.

The problem occurs at this point. I want to run a query and as a result crreate another table and send it automatically to a specific email address through Lotus Notes. The query is ok and table is created successfully. But for the mailing part I use VBA. I need this to be done every day as a scheduled task so I write the VBA macro in Excel and tell in the code to execute this VBA macro every time the Excel file is open.As a scheduled task ı add to open this Excel file daily. I have written all the mailing code, and it works on an ordinary Excel file.

The problem is it does not execute a Perl script. Okey it does but only simple perl scripts like a "hello" printing on a message box. I can not make VBA to create the output file of my Perl command which is a csv file.

Can someone please direct me ??

Or do u think any other way to do this getting data, parsing it, inserting into database, running a query and emailing process ?


Thanks

Ozz
I'm confused.
Perhaps you could put each of the steps - input, software, output into a series of points to help us with context.

You have a big csv file that you process with Perl.
Then you insert it into an Access database??? How? what SW?
The problem area -- you want to run a query (Is that an Access query?)
and create another table (presumably an Access Table?)
Then send the Table via LotusNotes to a specific address.
It does not execute the Perl script??? What is it?


I can not make VBA to create the output file of my Perl command which is a csv file.
Does that mean you know VBA but can't make a csv?
Or, you know how to make a csv in Perl, but can not do the same procedure in VBA?
Or, is there something specific about this csv that "can not be done in VBA"?

Please clarify so we may help.

[ozzz]

Hi,,

You're right , it sounded confusing when I puted in those words.

Let me explain so: The whole procedure : getting the data from server, parsing it with Perl, inserting in Access, running the Access query and sending as mail should be done automatically with VBA.

Batch file: gets the data from server

Perl: Parses the file and inserts the output into Access Database.

In Access I wrote a query, and created another crosstab Table . This table is what I want to send as an email.

Now everything has to be automatized using VBA. My computer has to do it as a scheduled task every day automatically.


So I put my perl script as a line in my batch file . So the batch file gets the data, parses it and inserts into Access.

Now I use "shell" command in Excel Visiual Basic Macro to run the batch file. So "It " refers to Excel VBA Macro . It runs the batch file but does not run the Perl part.

I also tried to run only the Perl script using VBA. It does not write an output csv file. So it does not run.



This is my problem.


If I ll be ab le to get the Perl part run, it means insert the desired data into Access, then I will write a VBA macro , to connect to Access database, run the query and import the file into Excel . this also doesnt look like working but this is a later problem :)
Mailing part is already okey.

Hope I claired my self :( since ı am totally a stranger to VBA I might use some false terms maybe

And need desperately helpp :)

thanklss

[orange]

Hi,,

You're right , it sounded confusing when I puted in those words.

Let me explain so: The whole procedure : getting the data from server, parsing it with Perl, inserting in Access, running the Access query and sending as mail should be done automatically with VBA.

Batch file: gets the data from server

Perl: Parses the file and inserts the output into Access Database.

In Access I wrote a query, and created another crosstab Table . This table is what I want to send as an email.

Now everything has to be automatized using VBA. My computer has to do it as a scheduled task every day automatically.


So I put my perl script as a line in my batch file . So the batch file gets the data, parses it and inserts into Access.

Now I use "shell" command in Excel Visiual Basic Macro to run the batch file. So "It " refers to Excel VBA Macro . It runs the batch file but does not run the Perl part.

I also tried to run only the Perl script using VBA. It does not write an output csv file. So it does not run.



This is my problem.


If I ll be ab le to get the Perl part run, it means insert the desired data into Access, then I will write a VBA macro , to connect to Access database, run the query and import the file into Excel . this also doesnt look like working but this is a later problem :)
Mailing part is already okey.

Hope I claired my self :( since ı am totally a stranger to VBA I might use some false terms maybe

And need desperately helpp :)

thanklss
Wow!
Can you give us a small sample of the csv?
Can you explain what the Perl is doing? I am not a Perl programmer, but I know vba.
I'd like to see the part that "inserts" the data into Access.

VBA will not run a Perl script.

[stanl]

VBA will not run a Perl script.

Technically it can. Just embed the perl via perlscript into a .wsc file and call it via GetObject(wscFile). .02 Stan

[orange]

Technically it can. Just embed the perl via perlscript into a .wsc file and call it via GetObject(wscFile). .02 Stan
Thanks Stan.
What I was trying to say was that you can't just put Perl code in vba and have it run.

I did some google-ing after your post and did find this:

http://www.perlmonks.org/?node_id=186849

I haven't tried it.
But it seems batch file, excel, perl, Access , email is adding
software for the sake of adding software and I thought ozzz was looking to simplify.

[ozzz]

:( There must be a wayy to run this Perl on VBA :(

the following is how you insert some data into Access :

$dbh = DBI->connect('dbi:ODBC:DSN', 'user', 'password');;

$sql = 'insert into TEST_TABLE ('FIELD1','FIELD2') values ('a', 'b');

its that simple, you just connect to the database giving the location and inserrt the data into a table in that database. But first u have to create a table called TEST_TABLE in the database.

in my script "a" and "b" are the lines of my csv file.I use a foreach loop and foreach line in my file I take lets say the second column and call it as string a, and take the third column, call it as string b. Then I insert string a and string b into Access. Unfortunately ı dont have the exact codes on my laptop, theyre in another PC right now, but here is how ı split

@FILE=<>;

foreach $LINE (@FILE) {
@NEW_FILE=split ';',$LINE ;
$a= $NEW_FILE[2];
$b=$NEW_FILE[3];
$dbh = DBI->connect('dbi:ODBC:DSN', 'user', 'password');;

$sql = 'insert into TEST_TABLE ('FIELD1','FIELD2') values ('$a', '$b');
as long as I remember the code was like that basically.Count part is another string that I insert into Access, using hash method.
the csv file is a file containing thousands of lines of information, listed like rows and columns, I split the columns with semicolon and take the columns I need (lets say second and third), and count how many lines of different information there are.

This count is actually what I need.

If it is impossible to run a Perl script on VBA, then is there somehow anyway to parse a file on VBA, I mean split and take the desired columns and count them , insert into Access??

Maybe ı can just throw Perl part away and do the whole process only with a batch file and Excel macro??

[ozzz]

hii orange,

the link u gavee is a nice one, I already checked it. Actually I chehkedd all the links that comes with the keywords on gooogle :(

The thing is there are several ways to run a perl script on VBA I know. But non of them gives the output file. Ther just run basic Perl scripts and lets say print "hi there!" on the screen.
But ı havent heard abbout GetObject(wscFile) command. So maybe ı ve to check that.

[orange]

:( There must be a wayy to run this Perl on VBA :(

the following is how you insert some data into Access :

$dbh = DBI->connect('dbi:ODBC:DSN', 'user', 'password');;

$sql = 'insert into TEST_TABLE ('FIELD1','FIELD2') values ('a', 'b');

its that simple, you just connect to the database giving the location and inserrt the data into a table in that database. But first u have to create a table called TEST_TABLE in the database.

in my script "a" and "b" are the lines of my csv file.I use a foreach loop and foreach line in my file I take lets say the second column and call it as string a, and take the third column, call it as string b. Then I insert string a and string b into Access. Unfortunately ı dont have the exact codes on my laptop, theyre in another PC right now, but here is how ı split

@FILE=<>;

foreach $LINE (@FILE) {
@NEW_FILE=split ';',$LINE ;
$a= $NEW_FILE[2];
$b=$NEW_FILE[3];
$dbh = DBI->connect('dbi:ODBC:DSN', 'user', 'password');;

$sql = 'insert into TEST_TABLE ('FIELD1','FIELD2') values ('$a', '$b');
as long as I remember the code was like that basically.Count part is another string that I insert into Access, using hash method.
the csv file is a file containing thousands of lines of information, listed like rows and columns, I split the columns with semicolon and take the columns I need (lets say second and third), and count how many lines of different information there are.

This count is actually what I need.

If it is impossible to run a Perl script on VBA, then is there somehow anyway to parse a file on VBA, I mean split and take the desired columns and count them , insert into Access??

Maybe ı can just throw Perl part away and do the whole process only with a batch file and Excel macro??
ozzz, I thought you were trying to get rid of the perl part and use vba.

The database stuff you're showing is ODBC, which Access supports.
The INSERT is simply an SQL query that uses the ODBC connection.
You could INSERT to an existing table, if that applies.

As Stanl points out you can run Perl from Access. It just isn't a simple include the Perl script here sort of thing.

If you're more conversant with Perl, then by all means use what you have to or what you want. I just thought there was more software than necessary in your approach. Hey it's your procedure and you have to run it.

You could put the fully parsed data into Access tables, then query the data in the tables. You could even query a count from Perl, using your example

$ sql = "Select count(field1) from test_table where field1 = 'a';"
or some other specific where clause.
(I'm not sure which quotes you need " ')

I found http://www.codeproject.com/KB/perl/ExecPerlFromVBA.aspx
which uses a CreateObject(...)

GetObject means the object already exists
CreateObject means you're creating a new one

This link may lead you to the method Stanl mentioned.

Good luck

[orange]

Technically it can. Just embed the perl via perlscript into a .wsc file and call it via GetObject(wscFile). .02 Stan


Stan,

Do you have samples that could help ozzz?

[ozzz]

Thank u orange for the link

This script runs the perl command but still I m not able to print the output in a file.
Thanks anyway for the helpp

[orange]

Thank u orange for the link

This script runs the perl command but still I m not able to print the output in a file.
Thanks anyway for the helpp

If you want to do more of the activity/parsing etc in vba, I'm sure there are plenty on the forum who can help.