Delete txt file row character starting with ! and use "Text to column" with delimiter!

Hello community members,
I am not sure if I described clearly. If no, please kindly see the attachment. There are thousands of rows in my txt file, I just take some as a reference.
change the txt file to the excel file.
From the txt file to excel file, I used excel “text to column” use “!” to have those rows separated to several columns, then deleted first column which is empty.
test.txt (654 Bytes)

Hi @li.yu
Do u need to delete all columns which are empty ?

in excel, just the first column which is empty need to be deleted, or in txt file to delete those lines the first character started with “!”

well @li.yu
I thought of this idea, since u are splitting the text by ! delimiter, and there are !!! and !! in between text data, if we convert all these to ! , would it be fine for your usecase?

hi Nived,
thanks, it seems no need to convert, in excel, if we use text to column, it can be separated with !, no matter how many !, well, yes, it is fine to convert all those !! to ! too.

Hi @li.yu
Then try this way

  1. Read the text file and store in string variable let’s say input

  2. now use the assign activity

input = System.Text.RegularExpressions.Regex.Replace(input,"!{2,}","!")

Now your input will look like this

!AB!adfg!fgh!ADF CN!Lei!Li!M!I!C4011!AB02!8439!2017-09-03!2017-09-02!Withdrawn!C1! !AB!adfg!fgh!ADF CN!Cui!Jin Nan!M!I!C4121!AB02!8467!2017-10-02!2017-10-01!Withdrawn!C1! !AB!adff!fgh!ADF CN!Li!Ya!M!I!C4121!AB02!8419!2017-12-03!2017-12-02!Withdrawn!C1! X0000YN!adfg!ADF CN!AHD!SHN NH!Aitr!Aoert!M!I!HC DI DCC !C4154!CD02!6115!2017-08-01!2017-05-01!Withdrawn!HAC!CL! X000V3U!adfg!ADF CN!ADJ!SHN NH!Bredel!Were Moamd!M!I! CT R&D!C4111!AC02!842224!2019-06-01!2017-06-01!Withdrawn!HAC!CL! X00051Z!adfg!ADF CN!CFG!SHN NH!Cag!Qi In!M!I!AHD DI MEQ !Senior Engineer!C4111!EF02!84539!9999-12-31!2017-04-01!Active!840198!HAC!C1!

I hope this is the way u need, and issue with the blank columns will get resolved

Nived N
Happy Automation

1 Like

The following is my example, I hope it can help you.
Main.xaml (10.3 KB)

Thanks Jidong, yes, this is what we need, but for the result of the excel need to delete the first three rows as these roles the first column is empty.
I know we can use for each row to do this, but if I have thousands of rows, it will take some time to read all rows.

Thanks Nived, yes if we do like this, it can change all !! to !, but the what we want is to separate them and delete those lines first character started with !


You can do,And please pay attention to the place I framed with red lines.

Sorry for misleading you, please choose again according to the picture

Let me know if this works.

1 Like

thanks very much Jidong, yes, we can do like this!