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: Extract Data from MS Excel XLSX File?
Topic Summary:
Created On: 03/15/2012 01:54 PM
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.
Answer This question was answered by RobertCox, on Tuesday, March 20, 2012 2:14 PM

Answer:
Hi Jean

Apologies I have this working now. Thank you for your help!
 03/15/2012 01:54 PM
User is offline View Users Profile Print this message

Author Icon
RobertCox
Apprentice (20-49)

Posts: 41
Joined: 06/29/2005

Hi

I receive regular lists of email addresses contained in an xlsx spreadsheet list.

Does anyone know the easiest way for automate to extract the email address list from the xlsx file?

Thank you for any advice...

 Category Survey
AutoMate 6 version: 6.0.0
Windows version: Windows Vista
 03/15/2012 10:03 PM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

Robert,
I suppose you don't know in advance the length of the list in the xlsx.
Hence the most efficient way would be an SQL query against the spreadsheet, that will put each row in a dataset.
If you attach a sample xlsx file I could help more.

 Category Survey
AutoMate 6 version:
Windows version:


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 03/16/2012 01:34 AM
User is offline View Users Profile Print this message

Author Icon
RobertCox
Apprentice (20-49)

Posts: 41
Joined: 06/29/2005

Hi Jean

Example of list attached.

Thanks Rob

 Category Survey
AutoMate 6 version: 6.0.0
Windows version: Windows Vista
 03/16/2012 02:12 AM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

Assuming you have Excel 2007 installed, and the file is in c:\temp\list.xlsx
you get the lists in ds.[List 63] since
"List 63" is the name of the column, and spans over 2 rows

 Category Survey
AutoMate 6 version:
Windows version:



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 03/16/2012 03:55 AM
User is offline View Users Profile Print this message

Author Icon
RobertCox
Apprentice (20-49)

Posts: 41
Joined: 06/29/2005

Hi Jean

The server running the process doesn't have Excel installed. I will get it installed and try it out.

Many thanks.

 Category Survey
AutoMate 6 version: 6.0.0
Windows version: Windows Vista


Edited: 03/16/2012 at 04:45 AM by RobertCox
 03/20/2012 03:00 AM
User is offline View Users Profile Print this message

Author Icon
RobertCox
Apprentice (20-49)

Posts: 41
Joined: 06/29/2005

Hi Jean

I tried this exactly as you suggested and got "Cannot populate the variable ds" message.

Note, to simplify slightly I will have the list name removed from top cells in future

(Note - This task is running on same PC as my other current AM project you are assisting with).

 Category Survey
AutoMate 6 version: 6.0.0
Windows version: Windows Vista


List.xlsx
List.xlsx  (19 KB)

 03/20/2012 03:35 AM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

Robert,
this works nicely for me with your new headerless file.

Please run it in a brand new Task Builder window. I have seen cases it did not work in an already used task builder window.

 Category Survey
AutoMate 6 version:
Windows version:



Task Steps: (Right click, Select All, Copy to copy to the clipboard. Steps can then be pasted into the AutoMate Task Builder)


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 03/20/2012 04:36 AM
User is offline View Users Profile Print this message

Author Icon
RobertCox
Apprentice (20-49)

Posts: 41
Joined: 06/29/2005

Hi Jean

On the last step it gives error;

Syntax Error EXPECTING ")"

Sorry this is probably simple but I cannot see where the error is...

 Category Survey
AutoMate 6 version: 6.0.0
Windows version: Windows Vista
 03/20/2012 05:45 AM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

That's really unbelievable.
My version of AM6 is 6.2.0.8
When i copy my code (Select All + Copy) from the code box above and paste it into AM6 Task Builder it works nicely as shown in this picture.
I don't know what I could do more.
Please check that what you see in your own task builder is exactly what you see in my picture. (besides the fact your version is English and mine is French, and of course after changing the xlsx file path in the ConnString variable)

 Category Survey
AutoMate 6 version:
Windows version:


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
 03/20/2012 02:14 PM
User is offline View Users Profile Print this message

Author Icon
RobertCox
Apprentice (20-49)

Posts: 41
Joined: 06/29/2005

Answer Answer
Hi Jean

Apologies I have this working now. Thank you for your help!

 Category Survey
AutoMate 6 version: 6.0.0
Windows version: Windows Vista
 03/20/2012 09:53 PM
User is offline View Users Profile Print this message

Author Icon
JeanDelfosse
Supreme Guru (1000+)

Posts: 8883
Joined: 03/30/2005

You're welcome, Robert.

 Category Survey
AutoMate 6 version:
Windows version:


-------------------------
Jean Delfosse - Automate Beta Tester
Win7 Pro 64 bits -Win XP Pro SP3 32 bits - AM8, AM7, BPA10, BPA9, BPA8, BPA7, AM6 Enterprise, AM4
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 2600 guests browsing this forum, which makes a total of 2600 users using this forum.

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

Sitemap Network Automation Software Blog