How Remove Headers and \r\n from Output Datatable

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

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)

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.

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.

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