How Remove Headers and \r\n from Output Datatable

Help
1

Hi Everyone

I am completely new at all this and learning as i go. I am pulling data from excel using sql query.

“Select * From [Sheet1$A39:R75] Where [F1] = “+Target+”” and storing the above in Datatable

Then i am using “Output Data Table” to put the Datatable in a string and storing in Datatable2

This is the output i get:

“F1,F2,F3,F4,F5,F6,F7,F8,F9,F10\r\n35,29,7,128,12,35,3,26,48,32\r\n”

I wish to get the numbers between the two \r\n

I am not sure why i am getting the Headers and New Line when my sql connection string has HDR=NO

I used the following query

System.Text.RegularExpressions.Regex.Replace(Datatable2,“(?<=F1)(.*)(?=F10)”,“”).ToString.Replace(“\r\n”," ")

But i get the following output:

“F1F10\r\n35,29,7,28,12,35,3,26,0,32\r\n”

Can this be implemented in the select query as i like to minimise the number of actions otherwise please show how i can get the result of the following:

35,29,7,128,12,35,3,26,48,32

Could someone please assist me with this.

Thank you in Advance

2

First off, welcome to the forum!

Below is one solution which will modify the output to give you your desired result.

I used the Replace activity although this can be done via direct function as well. The regular expression used was “(F\d+,*)|(\\r\\n)”, which will replace anything with F + one or more numbers + a comma (optional) as well as \r\n.

I’ve attached an example xaml. Please mark as the answer this if this is a good solution for you!

RegExTest.xaml (4.8 KB)

3

I implemented your Replace Activity after Output Data Table and changed variables accordingly and it came up with the following result:

“\r\n35,29,7,28,12,35,3,26,0,32\r\n”

But thank you to you it is working now when using the Regular Expression “(F\d+,*)|(\r\n)”

Just needed to replace double \ with single.

Another solution i am posting for anyone else after i played around with my expression using Assign Activity:

System.Text.RegularExpressions.Regex.Match(Datatable2,“(?<=\r\n)(.*)(?=\r\n)”).Groups(1).Value

Thank you for your prompt reply.
Appreciate it.

4

I have a variable named NumberCheck which is assigned as integer and has a value of 5.

How do i make a if statement that can compare if the integer exists in the above string?

The query i have doesn’t work:

If CInt(Datatable2).Contains(NumberCheck)

The error stated is Contain is not a member of ‘Integer’.

If i do NumberCheck.ToString then it works but it sees 35 as 5 and gives true when it is not.

5

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.