Removing lineBreaks in Excel columns names

Help
1

Hi All,

I have columns names as below. Problem here is column names contains line breaks So tool unable to detect the column name. How to remove this line breaks while reading Excel?
Ex:
image
image

Thanks

1 Like
2

Hi @Ven

Use for each row in dtinput

use assign activity asDtinput.Columns(“Name of the employees”).columnName= String.Join(Environment.NewLine,Dtinput.Columns(“Name of the employees”).columnName.Split({Environment.NewLine,vbcrlf,vblf," ",vbtab,vbcr,vbNewLine},StringSplitOptions.RemoveEmptyEntries))

Thanks
Ashwin S

1 Like
3

Hi @AshwinS2,

Have tried your solution.
Facing error in assign activity. Can you please check and help
LineBreaks.zip (18.6 KB)

4

Fine in that case we can use column index instead of column name which usually starts from 0 for the first column
Like this row(0).ToString if inside a for each row loop
Or in a assign activity outside for each row loop activity like this
Yourdatatablename.Rows(rowindex)(columnindex).ToString
So
Yourdatatablename.Rows(0)(0).ToString which means first row and first column

Or

If we want to know the column name and make use of it then
Get the column name with a variable then use that in the above method suggested and to get the column name
Str_columnname = Yourdatatablename.Columns(columnindex).ColumnName.ToString
So for column index mention like this
Str_columnname = Yourdatatablename.Columns(1).ColumnName.ToString

So this will give the column name of second column

Now we can make use of it in the above expressions suggested like this
row(str_columnname).ToString If inside a for each row loop
Or without loop with simple assign activities like this
Yourdatatablename.Rows(1)(str_columnname).ToString
Where 1 is the row index of second row

Hope this would help you
Cheers @Ven

2 Likes
5

So, basically we are assigning columnNames based on indexing. But if sometimes columns might be on different position, these would become a problem correct. Is there any way to remove line breaks instead please @Palaniyappan

6

Hi @Ven
not only index based on the split also we can remove

by based on Split(environment.newline,String.empty)

Check this

Thanks
Ashwin S

1 Like
7

Can you give me a sample one buddy? Or please elaborate a little as I am new to this @AshwinS2

8

Can someone please help?

9

@chenderson @Palaniyappan @Shubham_Varshney @Lahiru.Fernando @lakshman Any idea/solution for @Ven 's issue?

1 Like
10

Hi @Ven

You can apply the same logic given here to replace the line brakes in your headers

Renaming multiple datatable columns at the same time

         myDataTable.Columns(0).ColumnName = myDataTable.Columns(0).ColumnName.Replace(VbCrLf,"_")
2 Likes
11

@Ven

@Lahiru.Fernando 's solution should work.

Also try
DTName.Columns(0).ColumnName=DTName.Columns(0).ColumnName.Replace(vblf,“”)

3 Likes