Network Automation Software We are currently migrating this forum
over to our HelpSystems domain. Please
post all new threads in our new
HelpSystems Community Portal.
Post to the HelpSystems Forum
You are not currently logged on. You must be logged on in order to post. Log on
Or Create a new account
AutoMate Discussion
Decrease font size
Increase font size
Topic Title: Software to convert multiple files from xlsx to csv
Topic Summary: I am unable to get files converted on an agent using automate BPA 9
Created On: 08/11/2015 08:20 AM
Status: Read Only
Linear : Threading : Single : Branch
Search Topic Search Topic
Topic Tools Topic Tools
View similar topics View similar topics
View topic in raw text format. Print this topic.
 08/11/2015 08:20 AM
User is offline View Users Profile Print this message

Author Icon
MaryWesterman
Journeyman (50-99)

Posts: 89
Joined: 08/18/2014

Does any one know of software I can use/run in automate to convert multiple xlsx file to csv files?  I was trying to convert them interactively in automate, and while I got it working in debug mode and on my local machine when I tried to run on an agent it would not work.  Kept losing focus and could not find things, commands were not sent etc.

 

Need another software to do this on an agent.

Help!!!!



 Category Survey
AutoMate BPA Server 9 version: 9.0.5
Windows version: Windows Server 2012 R2
 08/11/2015 08:57 AM
User is offline View Users Profile Print this message

Author Icon
Leonard Amabile
HelpSystems

Posts: 5454
Joined: 05/15/2007

Mary,

I recommend SQL querying the spreadsheet and populating a dataset. You can then loop the dataset and write the data in a .csv file. This can all be done in the background.




 Category Survey
AutoMate BPA Server 9 version:
Windows version:


-------------------------

Leonard Amabile | Director, Cross Platform Support
AutoMate | InterMapper | Skybot | Divisions of HelpSystems
T: +1.213.738.1700 | F: +1.213.738.7665
 08/11/2015 09:15 AM
User is offline View Users Profile Print this message

Author Icon
MaryWesterman
Journeyman (50-99)

Posts: 89
Joined: 08/18/2014

Do you have an example?  I'm more of a visual person.  Keep in mind, I do not know what the excel speadsheet names will be, and there could be one or 30 to convert.



 Category Survey
AutoMate BPA Server 9 version: 9.0.5
Windows version: Windows Server 2012 R2
 08/11/2015 09:26 AM
User is offline View Users Profile Print this message

Author Icon
Leonard Amabile
HelpSystems

Posts: 5454
Joined: 05/15/2007

Mary,

We have sample tasks in the URC that can assist you:

SQL Query an Excel Spreadsheet
http://www.networkautomation.c...E67-2DCA89A82990F229/

SQL Query a CSV File
http://www.networkautomation.c...AA4-13EB4E34CB9724C8/

Then see my post on the forums with code showing how to create a .csv file without knowing the column names:
http://forums.networkautomatio...&threadid=13237#74003




 Category Survey
AutoMate BPA Server 9 version:
Windows version:


-------------------------

Leonard Amabile | Director, Cross Platform Support
AutoMate | InterMapper | Skybot | Divisions of HelpSystems
T: +1.213.738.1700 | F: +1.213.738.7665
 08/11/2015 09:45 AM
User is offline View Users Profile Print this message

Author Icon
MaryWesterman
Journeyman (50-99)

Posts: 89
Joined: 08/18/2014

Not exactly going to work for us.  This task will run every 5 minutes, and need to create .csv files from the excel spreadsheets.  Right now I'm trying to convert 10 large spreadsheets.  If I have to read each line of a spreadsheet and then write it to .csv it will take too long.  Once you open a spreadsheet you should somehow be able to save that file as a .csv. 

You can do this interactively, but once you try to run on an agent it fails.  My previous post has all the files and code I was using attached to it.  It works in debug on my PC, but not on an agent.

I've read and searched for some way to do this quickly in BPA but could not find anything which is why I posted on the forum.  No one responded to previous post that contained the interactive version so I started looking for software to download and run through BPA.  I have not found that either.

This makes no sense.

 

 



 Category Survey
AutoMate BPA Server 9 version: 9.0.5
Windows version: Windows Server 2012 R2
 08/11/2015 10:12 AM
User is offline View Users Profile Print this message

Author Icon
Leonard Amabile
HelpSystems

Posts: 5454
Joined: 05/15/2007

Mary,

The other thread was posted yesterday:

http://forums.networkautomatio...tid=51&threadid=13480

I understand your frustration with getting a solution in the time frame you desire. However, you also have to understand that posts, tickets, calls, etc. are handled in the order that they are received. I will review the other thread as well. The fact that the task works in Task Builder but not on the remote or local agent points to the issue being a task or workflow property issue. You are eligible and I suggest you open a ticket with the support team and have your issue addressed in a more immediate fashion through a remote session.

 Category Survey
AutoMate BPA Server 9 version:
Windows version:


-------------------------

Leonard Amabile | Director, Cross Platform Support
AutoMate | InterMapper | Skybot | Divisions of HelpSystems
T: +1.213.738.1700 | F: +1.213.738.7665
 08/11/2015 12:12 PM
User is offline View Users Profile Print this message

Author Icon
MaryWesterman
Journeyman (50-99)

Posts: 89
Joined: 08/18/2014

Yes, the other thread was posted yesterday, no one read it or responded.  After a day of frustration I posted this one. 

I tried your suggestions, it did not work on the agent, so I went back into debug, it no longer processes multiple files like it did before and I cannot get it back to that.

I've been trying to get this to work somehow for two days now.

I logged support issue but still looking for other software.

 



 Category Survey
AutoMate BPA Server 9 version: 9.0.5
Windows version: Windows Server 2012 R2
 08/11/2015 02:37 PM
User is offline View Users Profile Print this message

Author Icon
Valentineo McGowan
HelpSystems

Posts: 766
Joined: 09/03/2013

Hello Mary,

Thank you for allowing us to assist you on Support Ticket #55ca2fefc663a pertaining to this issue with converting *.xls files to *.csv.

 Category Survey


-------------------------

Valentineo McGowan | Technical Consultant
AutoMate | Division of HelpSystems
HelpSystems, LLC | T: +1 213.738.6966 | F: +1.213.738.7665
 08/12/2015 09:34 AM
User is offline View Users Profile Print this message

Author Icon
MaryWesterman
Journeyman (50-99)

Posts: 89
Joined: 08/18/2014

Still having issues.  Added task to workflow and scheduled.  The window cannot get focus.  Tried several things, still not working.



 Category Survey
AutoMate BPA Server 9 version: 9.0.5
Windows version: Windows Server 2012 R2
 08/12/2015 09:50 AM
User is offline View Users Profile Print this message

Author Icon
Ricardo Castaneda
HelpSystems

Posts: 3424
Joined: 01/11/2008

Hi Mary,

This forum thread is slightly different than your original converting files from .xlsx to .csv . I want to make sure these issues are addressed correctly. We will work with you through the open ticket and the original forum thread.

The information that was posted earlier in this thread would be the way to go if you would like to run this type of task in the background.

Mary,

We have sample tasks in the URC that can assist you:

SQL Query an Excel Spreadsheet
http://www.networkautomation.c...E67-2DCA89A82990F229/

SQL Query a CSV File
http://www.networkautomation.c...AA4-13EB4E34CB9724C8/

Then see my post on the forums with code showing how to create a .csv file without knowing the column names:
http://forums.networkautomatio...&threadid=13237#74003


We will contact you soon via your ticket.

 Category Survey


-------------------------

Ricardo Castaneda | Principal Support Analyst
AutoMate | Division of HelpSystems
HelpSystems, LLC | T: +1.213.738.1700
 08/13/2015 05:44 AM
User is offline View Users Profile Print this message

Author Icon
MaryWesterman
Journeyman (50-99)

Posts: 89
Joined: 08/18/2014

I've looked at these, and we determined that trying to read each spreadsheet and create a csv file will take too long.  This workflow has to run every 5 minutes, pick up the excel sheets, convert to csv and then run an SSIS package.  If there are 10 large sheets reading and writing is too slow.  I found some software to do this but it is rather expensive and I know that BPA was supposed to be able to handle this.

 

I also cannot even find the last item you listed.  Tried clicking on it in the earlier response and it does not work, so I searched and could not find it.



 Category Survey
AutoMate BPA Server 9 version: 9.0.5
Windows version: Windows Server 2012 R2


Edited: 08/13/2015 at 07:27 AM by MaryWesterman
 08/13/2015 09:42 AM
User is offline View Users Profile Print this message

Author Icon
Leonard Amabile
HelpSystems

Posts: 5454
Joined: 05/15/2007

Mary,

BPA does do exactly what you are looking for. Unfortunately it's not at the speed you want it to be. Running the task through Task Builder will be slower than its execution through an agent. Have you completed a task through the agent to see if the speed is acceptable?

The upcoming release of v10.4 will include a Dataset to CSV activity which will accomplish, in the background, what is already being defined here in the forums for v9.

 Category Survey
AutoMate BPA Server 9 version:
Windows version:


-------------------------

Leonard Amabile | Director, Cross Platform Support
AutoMate | InterMapper | Skybot | Divisions of HelpSystems
T: +1.213.738.1700 | F: +1.213.738.7665
 08/14/2015 05:23 AM
User is offline View Users Profile Print this message

Author Icon
MaryWesterman
Journeyman (50-99)

Posts: 89
Joined: 08/18/2014

The task stops working when running in a workflow.  It loses focus all the time.  Now I can't get it to work in debug either.  It is also much too slow now.  I don't know why it 'slowed' down.  We cannot move to version 10 as I am not finished with converting everything in old automate to bpa 9, then I have to move a remote version of automate to BPA in order to decommission both of these old servers.  This also includes upgrading some additional software so that tasks can run on the new Automate bpa.  THEN I can THINK about moving to version 10.

I have been unable to get BPA to run with the sql queries you suggested.  The .xlsx files reside on a different server from BPA and the SQLServer.

Looking into another software package.

 



 Category Survey
AutoMate BPA Server 9 version: 9.0.5
Windows version: Windows Server 2012 R2
 08/17/2015 01:00 PM
User is offline View Users Profile Print this message

Author Icon
Ricardo Castaneda
HelpSystems

Posts: 3424
Joined: 01/11/2008

We were able to get Marys task up and running by making a few adjustments to the "Wait for Window" title. Marys tasks is now running successfully.

 Category Survey


-------------------------

Ricardo Castaneda | Principal Support Analyst
AutoMate | Division of HelpSystems
HelpSystems, LLC | T: +1.213.738.1700
Statistics
18258 users are registered to the AutoMate Discussion forum.
There are currently 0 users logged in.
The most users ever online was 5551 on 01/08/2018 at 11:11 AM.
There are currently 1988 guests browsing this forum, which makes a total of 1988 users using this forum.

FuseTalk Enterprise Edition v4.0 - © 1999-2020 FuseTalk Inc. All rights reserved.

Sitemap Network Automation Software Blog