Pipe Separate file in .xls

Hi All,
I want pipe separated file.here share u my file.stmt.xls (7.9 KB)
output should like bellow screenshot:

1 Like

Hi @sayli.

stmt.xlsx should have been a csv file instead of xlsx :slight_smile:

However, we can do a way to make it work :slight_smile:
Follow the instructions below:

  1. Assign strInput = “”

  2. Excel Application Scope
    a. Read Range and store to dtInput

  3. For Each Row in dtInput
    a. Assign strInput = strInput + row(0).ToString + VbCrlf

  4. Write Text File. File Name should be: “input.csv” and the Text is: strInput

  5. Read CSV. The file name is “input.csv” and DataTable is dtOutput

  6. Excel Application Scope. Set the Path as “output.xlsx” and
    a. Write Range. Set DataTable as “dtOutput”

Then it’s good to go :slight_smile:


original file is in .xls

Okay. Please try doing my suggestion :slight_smile:

1 Like

Do You have source code for this.

1 Like

Hi @sayli

May i ask what you tried and what went wrong - are you not able to do anything in particular?

Hi @sayli
Yes. I do have. I created a code for your issue right before I gave an answer.
I just don’t want to provide the finished product so that you can do it by yourself while I am assisting you. :slight_smile:


the original file is in .xls i tried macro for same file and its working when file is open but due to its .xls changes are gone when i close the particular excel.so macro is not working.so i need a code which will separete “|” and give me formatted Output like screenshot