Jump to content
Macro Express Forums

working with arrays, csv, and database - Your advice


Recommended Posts

Hey Everyone,

I am working with arrays, csv excel files, and I have a database that I want to access to do "stuff". I am using the ASCIII file begin/end process to create an array of the various columns in excel and would like to know how I can "search" the arrays for things I want. So for example, I want to search arrays 1-99 to find anything that has "test" in it, and if it does then do this. I've created a counter, created a "get array length", made the counter use the array length to loop until, and search all of them for whatever variable I want. This is great, I can recall data but I am not sure how I can optimally write data.
What is the most optimal way to write to csv files? So if i have a user input "stuff" how do i make it append the csv file without physically "opening" the csv and clicking the retarded amount of "ok" and "yes" window in my office 2016 program because microsoft thinks I am too stupid to understand that csv is limited and wants to annoy the crap out of me. Meaning, is there a more optimal way of managing databses in MEP that allows me to work more efficiently?

My objective:
To have MEP take "stuff" and put it in a database to recall later in order to do other "stuff", this stuff can include filling out forms, perming tasks, sending out emails, sharing with other applications, opening applications, etc. I would like to have MEP as a generalist robot that has many functions and stores information for use whenever. For example, the CSV file feeds a KPI dashboard that displays metrics, so I want MEP to "feed" the csv file that will in-turn feed my metric system.

Keep in mind, I need the CSV to integrate with other programs seperate from MEP. Meaning, if i use a txt file, how can I make it automatically become a csv after? All the other applications I use integrate with CSV so this is important.

Some questions for the community pros;

  1. What is best practice when working with csv databases and MEP?
  2. What are some tips when using MEP to manage my databases?
  3. Are there easier methods? I am open to another program that would integrate and is easy. (without getting into another language/more complex solutions)

 

Thank you!

Cheers,
Ali

Link to comment
Share on other sites

You might want to check out my page on working with tabular data. It sounds like you're' doing it but there might be something in there for you.

 

As for writing CSV accumulate your output in a string variable and then write it all at once into a CSV file. That is do it all internally in MEP. No office apps.

 

What you are describing is 'flat file' work, not really database. If I were you I would drive this off of a SQL server. It's easy to learn the kind of things you want to do. Say you want to get the city of an employee. It's almost English. "Select City from Employees where EmployeeID = 24". In fact I think that would pass a grammar check. Where you are headed will cost you a huge amount of time writing routines to sort and manipulate data. You are reinventing SQL Server. It's all been done before and it's free. And MEP has almost no capability for efficiently processing and using tabular data. All one can do is create a series of arrays which emulate columns of data. Congratulations, you just invented the most basic part of a Db Server, A table.

 

This is also why I thought it would be nice to have a simple SQL addon. for MEP. The built in sqlcmd.exe I think will do this but I've never used it before. Since it's command line one might be able to run it from a file input.

 

I keep recommending thing that you reject because they're different or more complex. True they are different but they are simple and the road you're heading down is rocky and goes over the top of a mountain. There's a nice paved road already built down here in the valley. I have done exactly what you are trying to do and I pushed MEP to become a huge program doing many things for my clients. It made me very knowledgeable in MEP but in retrospect it was a mistake. I should have stopped and learned to program much sooner. It only took me a day to write my first program the replaced a macro that was hundreds of lines of code long and terribly unreliable. I think the subroutine was 7 lines long.

  • Like 1
Link to comment
Share on other sites

You might want to check out my page on working with tabular data. It sounds like you're' doing it but there might be something in there for you.

 

As for writing CSV accumulate your output in a string variable and then write it all at once into a CSV file. That is do it all internally in MEP. No office apps.

 

What you are describing is 'flat file' work, not really database. If I were you I would drive this off of a SQL server. It's easy to learn the kind of things you want to do. Say you want to get the city of an employee. It's almost English. "Select City from Employees where EmployeeID = 24". In fact I think that would pass a grammar check. Where you are headed will cost you a huge amount of time writing routines to sort and manipulate data. You are reinventing SQL Server. It's all been done before and it's free. And MEP has almost no capability for efficiently processing and using tabular data. All one can do is create a series of arrays which emulate columns of data. Congratulations, you just invented the most basic part of a Db Server, A table.

 

This is also why I thought it would be nice to have a simple SQL addon. for MEP. The built in sqlcmd.exe I think will do this but I've never used it before. Since it's command line one might be able to run it from a file input.

 

I keep recommending thing that you reject because they're different or more complex. True they are different but they are simple and the road you're heading down is rocky and goes over the top of a mountain. There's a nice paved road already built down here in the valley. I have done exactly what you are trying to do and I pushed MEP to become a huge program doing many things for my clients. It made me very knowledgeable in MEP but in retrospect it was a mistake. I should have stopped and learned to program much sooner. It only took me a day to write my first program the replaced a macro that was hundreds of lines of code long and terribly unreliable. I think the subroutine was 7 lines long.

Hi Cory,

 

Thank you, what about something like this;

 

http://www.winautomation.com/

 

I heard it's good and much more advanced, is that true?

 

?

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...