Pipe Separate file in .xls

Help
1

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

OUTPUT1
OUTPUT11020Ă—724 6.94 KB

1 Like
2

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:

2 Likes
3

original file is in .xls

4

Okay. Please try doing my suggestion :slight_smile:

1 Like
5

Do You have source code for this.

1 Like
6

Hi @sayli

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

7

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:

4 Likes
8

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