Automating excel to csv conversion

Lasse

Hi

I have been trying to build a visual process to convert .xlsx-files to .csv-format.

I guess the process is simple as Database Input -> Workspace -> DataPrep Export. 

The problem is that Database Input and Workspace ask for table name. In my case table names may vary. All I want is that the process  would convert .xslx files first table to .csv format.

Would anyone have an idea of how to proceed with this?

Automator version is 15.3.

Thanks,
Lasse

Rebecca_Cronin

Hello Lasse, 

Have you reached out to our Support Team?
They may be able to provide assistance in what you are seeking to accomplish. 

 I did find the following information links regarding Input/Export Elements via Automator Processes with the documentation.

• Input Elements:   Monarch Server v15.3 Automation Edition - Administrator's Guide - Input Elements
• Export Elements: Monarch Server v15.3 Automation Edition - Administrator's Guide - Export Elements
• DataPrep Elements: Monarch Server v15.3 Automation Edition - Administrator's Guide - DataPrep Elements
• Working w/Visual Process: Monarch Server v15.3 Automator Tutorial - Creating a Process with VPD

I think the process you may be looking to create is File Input -> workspace -> Export

-Rebecca

------------------------------
Rebecca Cronin
Customer Success & Community Manager
Datawatch Corporation
Bedford MA
(978) 275-8203
------------------------------
-------------------------------------------
Original Message:
Sent: 05-08-2019 10:17 AM
From: Lasse Kaikko
Subject: Automating excel to csv conversion

Hi

I have been trying to build a visual process to convert .xlsx-files to .csv-format.

I guess the process is simple as Database Input -> Workspace -> DataPrep Export.

The problem is that Database Input and Workspace ask for table name. In my case table names may vary. All I want is that the process  would convert .xslx files first table to .csv format.

Would anyone have an idea of how to proceed with this?

Automator version is 15.3.

Thanks,
Lasse"

CPorthouse

Inside your visual process, create a database input node and connect it to your workspace.

Click on the database input node and for the data source, you can specify the path using a wildcard character (\\server\share\*.xlsx).
You can then click on your workspace, and if you expand the load plans section, you should be able to see your original file name.  Select the drop down button and choose "Database Input" (or whatever the name of the database input node is).

Hope this helps.

------------------------------
Chris Porthouse
Senior Implementation & Integration Engineer
Altair
------------------------------
-------------------------------------------
Original Message:
Sent: 05-08-2019 10:17 AM
From: Lasse Kaikko
Subject: Automating excel to csv conversion

Hi

I have been trying to build a visual process to convert .xlsx-files to .csv-format.

I guess the process is simple as Database Input -> Workspace -> DataPrep Export.

The problem is that Database Input and Workspace ask for table name. In my case table names may vary. All I want is that the process  would convert .xslx files first table to .csv format.

Would anyone have an idea of how to proceed with this?

Automator version is 15.3.

Thanks,
Lasse"

Leonid

Chris Porthouse said:

Inside your visual process, create a database input node and connect it to your workspace.

Click on the database input node and for the data source, you can specify the path using a wildcard character (\\server\share\*.xlsx).
You can then click on your workspace, and if you expand the load plans section, you should be able to see your original file name.  Select the drop down button and choose "Database Input" (or whatever the name of the database input node is).

Hope this helps.

------------------------------
Chris Porthouse
Senior Implementation & Integration Engineer
Altair
------------------------------
-------------------------------------------
Original Message:
Sent: 05-08-2019 10:17 AM
From: Lasse Kaikko
Subject: Automating excel to csv conversion

Hi

I have been trying to build a visual process to convert .xlsx-files to .csv-format.

I guess the process is simple as Database Input -> Workspace -> DataPrep Export.

The problem is that Database Input and Workspace ask for table name. In my case table names may vary. All I want is that the process  would convert .xslx files first table to .csv format.

Would anyone have an idea of how to proceed with this?

Automator version is 15.3.

Thanks,
Lasse"

Hi Chris,

I had a similar question and your response helped a lot. To complicate just a bit - is it possible to define a global variable [&date] in a global script and use it in the Database Input path instead of wildcard, i.e. \\server\share\myfile_[&date].xslx?
If yes, can you please help with a sample. We just purchased Automator and learning on the fly.

Thank you, 
Leonid

------------------------------
Leonid Shteyngardt
App Dev Manager
Canaccord Genuity Inc.
New York NY
(212) 389-8176
------------------------------
-------------------------------------------
Original Message:
Sent: 05-15-2019 07:35 AM
From: Chris Porthouse
Subject: Automating excel to csv conversion

Inside your visual process, create a database input node and connect it to your workspace.

Click on the database input node and for the data source, you can specify the path using a wildcard character (\\server\share\*.xlsx).
You can then click on your workspace, and if you expand the load plans section, you should be able to see your original file name.  Select the drop down button and choose "Database Input" (or whatever the name of the database input node is).

Hope this helps.

------------------------------
Chris Porthouse
Senior Implementation & Integration Engineer
Altair
------------------------------

Original Message:
Sent: 05-08-2019 10:17 AM
From: Lasse Kaikko
Subject: Automating excel to csv conversion

Hi

I have been trying to build a visual process to convert .xlsx-files to .csv-format.

I guess the process is simple as Database Input -> Workspace -> DataPrep Export.

The problem is that Database Input and Workspace ask for table name. In my case table names may vary. All I want is that the process  would convert .xslx files first table to .csv format.

Would anyone have an idea of how to proceed with this?

Automator version is 15.3.

Thanks,
Lasse"

Steve Caiels_21881

Leonid said:

Hi Chris,

I had a similar question and your response helped a lot. To complicate just a bit - is it possible to define a global variable [&date] in a global script and use it in the Database Input path instead of wildcard, i.e. \\server\share\myfile_[&date].xslx?
If yes, can you please help with a sample. We just purchased Automator and learning on the fly.

Thank you, 
Leonid

------------------------------
Leonid Shteyngardt
App Dev Manager
Canaccord Genuity Inc.
New York NY
(212) 389-8176
------------------------------
-------------------------------------------
Original Message:
Sent: 05-15-2019 07:35 AM
From: Chris Porthouse
Subject: Automating excel to csv conversion

Inside your visual process, create a database input node and connect it to your workspace.

Click on the database input node and for the data source, you can specify the path using a wildcard character (\\server\share\*.xlsx).
You can then click on your workspace, and if you expand the load plans section, you should be able to see your original file name.  Select the drop down button and choose "Database Input" (or whatever the name of the database input node is).

Hope this helps.

------------------------------
Chris Porthouse
Senior Implementation & Integration Engineer
Altair
------------------------------

Original Message:
Sent: 05-08-2019 10:17 AM
From: Lasse Kaikko
Subject: Automating excel to csv conversion

Hi

I have been trying to build a visual process to convert .xlsx-files to .csv-format.

I guess the process is simple as Database Input -> Workspace -> DataPrep Export.

The problem is that Database Input and Workspace ask for table name. In my case table names may vary. All I want is that the process  would convert .xslx files first table to .csv format.

Would anyone have an idea of how to proceed with this?

Automator version is 15.3.

Thanks,
Lasse"

Hi Leonid,

It is not possible to use a global variable as part of the input specification, but you could use one to rename you variable file name to a static file that the process would use.

For example, if your variable is called MyDate, system.io.file.copy("\\server\share\myfile_" & MyDate & ".xslx","\\server\share\myfile_Static.xlsx") would allow your workspace to always use the 'myfile_Static' name.  It would be best to do some housekeeping to make sure that an old copy was not used by mistake.

Regards,
Steve.

------------------------------
Steve Caiels
Professional Service
Datawatch Corporation
+44 203 868 0253
------------------------------
-------------------------------------------
Original Message:
Sent: 05-16-2019 05:44 AM
From: Leonid Shteyngardt
Subject: Automating excel to csv conversion

Hi Chris,

I had a similar question and your response helped a lot. To complicate just a bit - is it possible to define a global variable [&date] in a global script and use it in the Database Input path instead of wildcard, i.e. \\server\share\myfile_[&date].xslx?
If yes, can you please help with a sample. We just purchased Automator and learning on the fly.

Thank you,
Leonid

------------------------------
Leonid Shteyngardt
App Dev Manager
Canaccord Genuity Inc.
New York NY
(212) 389-8176
------------------------------

Original Message:
Sent: 05-15-2019 07:35 AM
From: Chris Porthouse
Subject: Automating excel to csv conversion

Inside your visual process, create a database input node and connect it to your workspace.

Click on the database input node and for the data source, you can specify the path using a wildcard character (\\server\share\*.xlsx).
You can then click on your workspace, and if you expand the load plans section, you should be able to see your original file name.  Select the drop down button and choose "Database Input" (or whatever the name of the database input node is).

Hope this helps.

------------------------------
Chris Porthouse
Senior Implementation & Integration Engineer
Altair

Original Message:
Sent: 05-08-2019 10:17 AM
From: Lasse Kaikko
Subject: Automating excel to csv conversion

Hi

I have been trying to build a visual process to convert .xlsx-files to .csv-format.

I guess the process is simple as Database Input -> Workspace -> DataPrep Export.

The problem is that Database Input and Workspace ask for table name. In my case table names may vary. All I want is that the process  would convert .xslx files first table to .csv format.

Would anyone have an idea of how to proceed with this?

Automator version is 15.3.

Thanks,
Lasse"