Copying txt file content to Excel sheet columns

i have a text file and i need its content to copy and paste in an excel sheet columns

Please help

You can read a text file by using Read Text File activity this will give you a string output.

Perform string manipulations to get your content and use Write Cell activity to write into excel

i have a txt file of min 500 rows and that is taking too much time to write each cell to excel

Hello @Mz12 , did you get this resolved please? I have same requirement, your inputs will be highly appreciable.

Ashok.

Hi @vankashok,

can you attach the txt file to get idea. If you feel that the file is confidential , you can send it thru message. It is secured.

Regards
Balamurugan.S

Journal%20Data

Hi @balupad14
Thanks for your reply. I have attaached the screen shot how the .txt file looks like. I want to seprate the data as mentioned below.

Line in file looks like :
J95306110000000000000C00000000018630;INS;2018;1600042949;H

How I want it to be separated:
J 95306 1100 0000 000 0 000 C 00000000018630 ;INS;2018;1600042949;H

As a manual process I copy the data into excel and use Text to Columns function–>Fixed Width–>I will click on each place where a separate column is required and finish it. I would see the columns get separated.

I want to achieve the same functionality. Please note that the excel file would have more than 1000 lines in it. The Read text file activity would take good amount of time to separate each row.

It would be highly appreciable if you could put in your thoughts in achieving the requirement.

Regards,
Ashok.

Hi @vankashok ,

Yes we can do it. A separate custom activity is there !

Thanks for the quick response @balupad14.

Can you please advise what is the custome activity and how I can use it ?

Kind Regards,
Ashok.

1 Like

Hi @vankashok,

Rename text file extension as “.csv”.
Create a excel file called “Result.xlsx”.
Configure the package “BalaReva” like below and save it. after that open the sample project.



Here is the result file. Result.xlsx (8.2 KB)

Project file.vankashok.zip (7.7 KB)

Regards
Balamurugan.S

Thank you so much @balupad14

I would provide an update once I am done.

Kind Regards,
Ashok.

Hi @balupad14

I want the line to be separated as mentioned below. May the code need to be corrected to fit the bill. Is it possible to do correct it please ?

J-95306-1100-0000-000-0-000-C-00000000018630 ;INS;2018;1600042949;H

Please note that I have separated the line with ‘-’, I would need them in a separate column in excel.

Kindly advise.

Regards,
Ashok.

Hi @vankashok,

Change the delimiter as “-”. It will split the text by “-” and separated as the columns.

Regards
Balamurugan.S

Hi @balupad14

You have taken it wrongly my friend. Let me put it again.
Actually there is no delimiter, it is a single string “J95306110000000000000C00000000018630” but when I export them into excel I want it to be separated as : 'J | 95306 | 1100 | 0000 | 000 | 0 | 000 | C | 00000000018630| ;INS;2018;1600042949;H| "

The package you have provided is separating the string like ‘J95306110000000000000C00000000018630 | INS | 2018 | 1600042949 | H’

Is there any way we can achieve this.

Regards,
Ashok.

Hello @vankashok,

Here is the output :

image

Sample Project : vankashlok1.zip (2.3 KB)

Regards
Balamurugan.S

Hello @balupad14

I really appreciate all your help. :slight_smile:

Am I allowed to give a call or text you in person please? I have few more doubts, I would be glad if you could advise the means to connect.

Regards,
Ashok.

1 Like

Hello @vankashok,
Thank you.

In our forum community , We have a lot of users (MVP,Leader,regular and all).Technically they know more than me. So post your doubts and problems. Many users (including me) will give the solutions and advises to your problems. And it is good place to discuss about the issues to get many opinions from varies users.

Note:
If you got the solution to this problem.Please mark it as solved(closed) .

Thank you
Balamurugan.S

1 Like

Hello. The second green arrow has blocked the location of the balareva activity. Only C:\Temp\Cate… is visible

Thank you so much for your advise @balupad14. I totally respect your words.

Coming back to my questions.

The solution you have provided to separate the line in text file, I need to use Readtext activity first to read each line in the file and then call the code your have provided to seprate the line ? Please correct me if I am wrong
The custom activity what you have build would not fit for my case ?

Kind Regards,
Ashok.

Hi,

Is there a way that I can copy and paste the text file to excel file or I can say to insert the text file to my excel file.Any shortcut to that.
Suggestion please on this