csv files

[zabros2020]

Hello my fellow scriptors.

As i mentioned in another post, I am really looking for a way to log certain cocurances while my scripts are running, especially user behaviour (things like when shift esc is pressed, or when a user clicks OK or Cancel on a dialog box)

Thus far i have only been able to write these events to a txt file one line at a time. Given that we run the automation 1-2 thousand times a day, it is virtually impossible and greatly time consuming to report on this on a weekly and monthly basis.

Furthermore, i have attempted to write scripts that help with the reporting in excel etc, but to no avail.

I was wondering if MS has the capability to write in csv format, or if there is any other way that i am not aware of to store data as automations are run so that I can then report on it later.

Any advice would be very apreciated. Thanks in advance

[gdyvig]

Hi zabros2020,


Are you running the automation one one machine?
Or many machines and you want the reporting consolidated?
How did you want it organized?

Thus far i have only been able to write these events to a txt file one line at a time. Given that we run the automation 1-2 thousand times a day, it is virtually impossible and greatly time consuming to report on this on a weekly and monthly basis.

You can write to the same file for each WriteLn so you don't have thousands of files. You can change the name of the file based on date.

Furthermore, i have attempted to write scripts that help with the reporting in excel etc, but to no avail.

The easiest and most flexible way to write to Excel files is to use VBEval. I believe there is a tutorial on this and lots of examples in the forums. You can even have multiple worksheets and name the sheets in whatever manner makes sense to you.

See http://www.mjtnet.com/blog/2007/07/02/m ... xcel-data/

And in particular: http://www.mjtnet.com/forum/viewtopic.php?t=1470

I was wondering if MS has the capability to write in csv format, or if there is any other way that i am not aware of to store data as automations are run so that I can then report on it later. This is my preferred method.

Yes it does. Simply make the file extension ".csv" when you write out your file. Of course keep commas out of the columns to keep it simple.

The most powerful option is to write to a database. Again, use VBEval.



Gale

[zabros2020]

Are you running the automation one one machine?Or many machines and you want the reporting consolidated?

The automation is currently being run on multiple machines by multiple users. The current log files that i have are in the format of USERNAME_DATE.txt, so one file per person per day

How did you want it organized?

I don't mind how they are consolidated as long as each user stays seperate, so maybe we can have one file per user, per week/month and we only report monthly or weekly. I am honestly not fussed and will use whatever way you deem simplest.

You can write to the same file for each WriteLn so you don't have thousands of files. You can change the name of the file based on date.

We can, however what we write to the log file can't all be decided at once, i.e we write to the file at various stages of the automation. So i can'e just have a line at the end which says: WriteLn>value1,value2,value3,value4, etc etc...
The values are being determined as the script goes along.

However as i write this, I am getting an idea. Tell me what you think. Store the values in variables as they are determined then have one line that writes them all at once. Doable? Is it the best way?

The easiest and most flexible way to write to Excel files is to use VBEval. I believe there is a tutorial on this and lots of examples in the forums. You can even have multiple worksheets and name the sheets in whatever manner makes sense to you.

I would love to be able to do this. After having looked at the tutprials etc, it seems to me as though you need Excel open to do this, or at the very least a seperate excel file for each user... is this correct? is there a way around it?

The most powerful option is to write to a database. Again, use VBEval.

My database knowledge is close to zero unfortunately . Can it be done without being an expert? if so, i am more than happy to learn some basic databse knowledge.

Sorry for such a long post

[gdyvig]

However as i write this, I am getting an idea. Tell me what you think. Store the values in variables as they are determined then have one line that writes them all at once. Doable? Is it the best way?

Yes using variables is doable as a way to consolidate several lines into one. This is the best way if you are seeking a consise report.

it seems to me as though you need Excel open to do this, or at the very least a seperate excel file for each user... is this correct? is there a way around it?

This is a problem when using files of any kind. You could have all users write to the same file but would risk file locking issues. The alternative is to use AppendFile to link together text files, or use another VBEval to link together the Excel files. For example each User spreadsheet could be put in a separate worksheet in the same xls file.

My database knowledge is close to zero unfortunately . Can it be done without being an expert?

If you are simply adding rows to a single table you should not need to be an expert. Someone in the forum or perhaps at your organization could help. We should have some examples here in the forum. The advantage of the database is all users can be writing to it at the same time and the database helps handle the table locking issues. I checked and you do not need to use VBEval, Macro Scheduler has its own DB funtions. But you will need help from your organization to get the DB set up.

See: http://www.mjtnet.com/blog/2006/02/20/a ... databases/


Gale

[Marcus Tettmar]

A CSV file is just a text file. If you don't want/have Excel you can write to CSV files as you would text files using WriteLn. Or use the database approach to connect to a CSV file with DBOpen and then use DBExec to do the write:

See:
http://www.mjtnet.com/blog/2009/03/12/r ... csv-files/

This talks about reading, but it's not much different to write:

Code: Select all

Let>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents;Extended Properties='text;HDR=NO;FMT=Delimited'  
DBConnect>ConStr,dbH  
DBExec>dbH,insert into myfile.csv (field1, field2) values ("hello", 1234),res

[jpuziano]

Hi Marcus and all,

I wanted my file in C:\temp so I changed the Data Source to that and used the following:

Code: Select all

Let>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp;Extended Properties='text;HDR=NO;FMT=Delimited'  
DBConnect>ConStr,dbH  
DBExec>dbH,insert into myfile.csv (field1, field2) values ("hello", 1234),res

It did not work. Then I thought, maybe the file must exist first... so I created myfile.csv in C:\temp but that didn't help. I tried the above in both MS versions 11.1.14 and 10.1.21.

What is wrong with the above code?

[Marcus Tettmar]

Oops. Commas. Try:

Code: Select all

Let>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents;Extended Properties='text;HDR=NO;FMT=Delimited'  
DBConnect>ConStr,dbH  
Let>sql=insert into myfile.csv (field1, field2) values ("hello", 1234)
DBExec>dbH,sql,res

[jpuziano]

OK, tried the above and still no go... I used this:

Code: Select all

Let>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp;Extended Properties='text;HDR=NO;FMT=Delimited'
DBConnect>ConStr,dbH
Let>sql=insert into myfile.csv (field1, field2) values ("hello", 1234)
DBExec>dbH,sql,res

MDL>res

Nothing is inserted and the value of res after this runs is 0.

The Help File says "Specify a result variable to return the number of rows affected by the SQL statement".

This is telling me no rows were affected. Perhaps this would change to 1 if my row was inserted.

In any case, any other ideas on how to make the above work?

The path and filename of the existing file is: C:\temp\myfile.csv

The current contents of the file are as follows:

hey,001

[zabros2020]

I also tried the above and it still didn't work. Then i did some more reading and looked at a website provided by Marcus (connectionstrings.com) and ended up with code like this

Code: Select all

//Let>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Khaled;Extended Properties='text;HDR=NO;FMT=Delimited'
Let>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myfile.xls;Extended Properties="Excel 8.0;HDR=No;IMEX=1"
DBConnect>ConStr,dbH
DBQuery>dbH,select * from test.csv,fields,num_recs,num_fields

The main difference here is the type of file an excel spreadsheet as opposed to a csv file, and the difference in the value of ConStr but i still had no luck.
My spreadsheet/csv file had the following values

hello,123,abc
hey,456,def

My variables had the following values:

dbH=1 (which suggests it connected successfully on both values of ConStr)
Num_Recs=0
Num_fields=0 (which both indicate no data was retrieved with the query
res=0 (which also indicates no data was read)

any help?

[Marcus Tettmar]

You have the data source in the connection string pointing to a specific XLS file, yet your SQL attempts to get data from a CSV file. The connection is probably working as it is connecting to the XLS file, but the query will fail because the CSV file is not part of the XLS file.

If you want to query from an XLS file you need to specify the sheet name in the query.

If you want to query from a CSV file then the datasource parameter should point to the folder that contains it.

[jpuziano]

any help?

Thanks zabros2020... but I don't think using DBQuery is the right approach due to what Marcus has in the Help File:

Use DBQuery to perform a SQL query which returns a recordset, such as a SELECT statement.

Use DBExec to execute SQL statements that do not return a recordset. E.g. DELETE, INSERT and UPDATE queries.

I am trying to INSERT one line into an existing csv file so I want to use DBExec as in the example from Marcus... but I just can't make it work.

Marcus, can you or anyone else tell me what is wrong with the following?

Code: Select all

Let>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp;Extended Properties='text;HDR=NO;FMT=Delimited'
DBConnect>ConStr,dbH
Let>sql=insert into myfile.csv (field1, field2) values ("hello", 1234)
DBExec>dbH,sql,res

The path and filename of the existing file is: C:\temp\myfile.csv

The current contents of the file are as follows:

hey,001

Note that reading from csv files as in the following blog post works fine:

http://www.mjtnet.com/blog/2009/03/12/r ... csv-files/

Writing to them using similar methods would be nice... as I understand this method takes care of commas embedded in the data values.

When this is sorted, maybe another blog post can cover writing-to-csv-files.

Thanks in advance for any help.

[Marcus Tettmar]

Ok.

That has HDR=NO in the connection string. That is telling it you do NOT have a header record. If there is no header record it does not know what the columns are called. Therefore how would the insert statement work? The insert statement refers to two columns called field1 and field2.

This works dandy for me:

Code: Select all

Let>path=C:\test
Let>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%path%;Extended Properties='text;HDR=YES;FMT=Delimited'
DBConnect>ConStr,dbH
Let>sql=insert into myfile.csv (field1, field2) values ("hello", 1234)
DBExec>dbH,sql,res

This works on a csv file which has the following header record:

Code: Select all

field1,field2,field3

Note that FMT is Delimited so you will end up with quotes around strings.

One thing to be careful of - if you have mixed data types - e.g. you have a column which contains both strings in quotes and numbers not in quotes you may have problems since ADO cannot work out what the data type is.

Try the above on the following sample file:

Code: Select all

field1,field2,field3
"bob",9833,"abc"
"fred",1843,"zzz"

You should end up with:

Code: Select all

field1,field2,field3
"bob",9833,"abc"
"fred",1843,"zzz"
"hello",1234,

If your CSV file does not have a header record you can use the following format of SQL:

Code: Select all

insert into myfile.csv values ("hello", 1234, "boo")

But you will need to make sure all fields are included in the correct order.

You can then use HDR=NO in the connection string.

Both work for me.

[jpuziano]

Hi Marcus,

I have no header record so when I removed (field1, field2), it worked fine... thanks for that and your detailed explanation.

Is there a link to a reference somewhere I could read to find out more about the parameters... for instance, FMT is Delimited... any other choices?

Maybe a blog article on writing-to-csv-files would be a good idea. Perhaps it could get into more details on the following point you mentioned:

Note that FMT is Delimited so you will end up with quotes around strings.

One thing to be careful of - if you have mixed data types - e.g. you have a column which contains both strings in quotes and numbers not in quotes you may have problems since ADO cannot work out what the data type is.

Whatever data I put into the csv file, I need to be able to get the exact same data out. If having a header row helps with this, I can add one. Each data item might be any combination of characters including commas, spaces, single or double quotes, etc.

Is this possible or is there some "poison" sequence of characters that these commands will not be able to store and retrieve intact as a data item?

Thanks again and take care

[Marcus Tettmar]

Is there a link to a reference somewhere I could read to find out more about the parameters... for instance, FMT is Delimited... any other choices?

Don't know, sorry. Best place to find out would be to search msdn.microsoft.com

From searching it looks like you might be able to use FMT=Delimited(;) etc to specify a different delimiter but can't find any official documentation to that effect.

[jpuziano]

OK, fair enough.

Thanks again for such great support... much appreciated.

P.S. Please edit your previous reply to "Disable Smilies in this post" as the suggestion you made has been smilie-a-fied.