Excel Manipulation

VJR

Well-Known Member
Staff member
#2
Hi KaliRaj1982,

Take a hard look at Post 9 here and Post 2 here and you would be able to achieve the same using a AND condition on filter collection and keeping a running Count of items. Once the final collection is formed it can then be written back to the excel.
 

sivagelli

Active Member
#3
Hi,

You can achieve this by reading Excel as a Database using Data - OLEDB Utility. However, the Column Names should not have Spaces in between. Meaning 'Custom String 1' should be 'CustomString1'

  • Start
  • Set Connection
    • Input- Assuming your file is of type .xlsx, here is the connection string
      • Connection String: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<File Path>;Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
  • Open Connection
  • Get Collection
    • Input
      • SQL Query: Select Title, CustomeString1, CustomeString2, Count(CustomeString1) as Role from [Sheet1$] where Title'Add' AND CustomString2='Email' Group By Title, CustomeString1, CustomeString2
    • Output will be a collection
  • Close connection
  • End
 

VJR

Well-Known Member
Staff member
#4
Hi,

You can achieve this by reading Excel as a Database using Data - OLEDB Utility. However, the Column Names should not have Spaces in between. Meaning 'Custom String 1' should be 'CustomString1'

  • Start
  • Set Connection
    • Input- Assuming your file is of type .xlsx, here is the connection string
      • Connection String: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<File Path>;Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
  • Open Connection
  • Get Collection
    • Input
      • SQL Query: Select Title, CustomeString1, CustomeString2, Count(CustomeString1) as Role from [Sheet1$] where Title'Add' AND CustomString2='Email' Group By Title, CustomeString1, CustomeString2
    • Output will be a collection
  • Close connection
  • End
I guess for names with spaces they are enclosed within square brackets.
[First Name]
 
#6
Thank you all for your inputs. Its works perfectly.. Need to check with Data - OLEDB Utility . Will update you the status for that as well..
 
#7
I am getting below error while accessing the excel using OLEDB
Internal : Could not execute code stage because exception thrown by code stage: External table is not in the expected format.
 

VJR

Well-Known Member
Staff member
#9
YES! It works with Square brackets.
I was thinking of Databases and tried single quotes which has failed, so i suggested to remove spaces.
Great you've checked it. I just didn't wanted the OP (original poster) to avoid using the Oledb approach just because the names may contains spaces.
 
Top