Convert each file in a folder from .txt to .xls and also rename the same files

Khooshbu_Jani1 · June 25, 2022, 2:33pm

We have four/Five text file with .txt extension.

These text files are downloaded from website. So if I download the file then downloaded file will have following name:

KhushiOut0.txt

KhushiOut1.txt

KhushiOut2.txt and so on.

If you download the file then it will have

.txt

.txt and so on

(1) I want to convert these .txt files to .xlsx

(2) Rename the file with

UninsuredOut0.xlsx

UninsuredOut1.xlsx

UninsureOut2.xlsx and so on

Nithinkrishna · June 25, 2022, 4:48pm

Hey @Khooshbu_Jani1

The text files gave some structured data?

Thanks
#nK

Khooshbu_Jani1 · June 25, 2022, 7:14pm

Yes this is the file and it has same structure in all the files.

PAYMENT TYPE	FILE/CHECK NBR	TIN	CUSTOMER NAME	AMOUNT	TOTAL AMOUNT	STATUS	HRSA STATUS	WAVE DESCRIPTION	SPEND PLAN	FY	STATUS/PAID DATE	ISSUE DATE	STATE	CITY	ZIP	IRMS PROCESS DATE
CLAIM_TREATMENT	116520364	123456186	sdfsdfdf sdfsdfdf	115	115	Processed	Processed	Treatment	Treatment	2022	01/15/2022 01:00 AM	18-JAN-22	IL	GENESEO	61254-1061	05/21/2022 03:41 PM
CLAIM_TREATMENT	116520381	234561860	sdfhsjdhd sdkjhsdj	320.11	320.11	Processed	Processed	Treatment	Treatment	2022	01/15/2022 01:00 AM	18-JAN-22	TX	SOUTHLAKE	16062	05/21/2022 03:41 PM
CLAIM_TREATMENT	116520382	345618601	bfnfgbnbg dsufhgh	86.31	86.31	Processed	Processed	Treatment	Treatment	2022	01/15/2022 01:00 AM	18-JAN-22	TX	FRIENDSWOOD	11546-1121	05/21/2022 03:41 PM
CLAIM_TREATMENT	116520316	456186012	shdjshd adkjhfs	316.16	316.16	Processed	Processed	Treatment	Treatment	2022	01/15/2022 01:00 AM	18-JAN-22	SC	GREENVILLE	26615-4235	05/21/2022 03:41 PM
CLAIM_TREATMENT	116520356	561860123	ruyte sabdsd jhs	1351.43	1351.43	Processed	Processed	Treatment	Treatment	2022	01/15/2022 01:00 AM	18-JAN-22	AL	GREENVILLE	36031-3850	05/21/2022 03:41 PM
CLAIM_TREATMENT	116520358	618601234	Dsjsdjh jkhsd jh	14	14	Processed	Processed	Treatment	Treatment	2022	01/15/2022 01:00 AM	18-JAN-22	MN	ALEXANDRIA	56308-5213	05/21/2022 03:41 PM
CLAIM_TREATMENT	116520355	186012345	jshd sdjh skh dd	156.24	156.24	Processed	Processed	Treatment	Treatment	2022	01/15/2022 01:00 AM	18-JAN-22	WY	RIVERTON	82501-2285	05/21/2022 03:41 PM
CLAIM_TREATMENT	116520353	860123456	dffdf sdfdfs gdfd	2166.26	2166.26	Processed	Processed	Treatment	Treatment	2022	01/15/2022 01:00 AM	18-JAN-22	FL	JACKSONVILLE	32216-6262	05/21/2022 03:41 PM
CLAIM_TREATMENT	116520351	601234561	ab xd ss fdsdf fg	2466.51	2466.51	Processed	Processed	Treatment	Treatment	2022	01/15/2022 01:00 AM	18-JAN-22	WA	GIG HARBOR	68335-1106	05/21/2022 03:41 PM
CLAIM_TREATMENT	116516082	012345618	abcdefg UNIVERSITY HOS	1416.31	1416.31	Processed	Processed	Treatment	Treatment	2022	01/15/2022 01:00 AM	18-JAN-22	NJ	VOORHEES	80434-453	05/21/2022 03:41 PM

THIRU_NANI · June 25, 2022, 8:02pm

Hi! Welcome to community,

It’s my bad, I confused a bit…

You’re saying that you’re trying to download the file from site that is .txt format am I right?
The file name should be KhushiOut0.txt like this am i right?
Then you wants to convert this text file to .xlsx am i right?
The name of the xlsx file UninsuredOut0.xlsx like this am i right?

If yes, please follow the below steps :

→ Create and assign 2 variables- Type is Int32
Var1-> TxtCounter=0
Var2-> XlsxCounter=0

Before downloading the file to your machine make sure you’ve enabled the download setting as ask every time before downloading option from your browser.
1.1 when you hit download button file explorer will pops up. You need to set the path of the file to be download.
1.2 here our magic will works. So set the path like this

Example:- "Desktop\KhushiOut"+TxtCounter.ToString+".txt"

This will download the file to Desktop with the name: KhushiOut0.txt

Note:- In the end increament the counter like this

TxtCounter=TxtCounter+1

->Take one Assign activity-> Folderpath (String)=“Desctop"
->Take one Assign activity-> Files(Array(String)=Directory.GetFiles(Folderpath,”*.txt)

This will fetch .txt files from that folder.

->Take one for each mention the Files

->Take Read the text file using read text file activity pass the item->output ->TxtOutput

To convert this text file to xlsx you need to generate a data table.

Take one generate data table activity pass the TxtOutput as input-> open the wizard. Try seperators like (, or space) to generate the structure data…
Output as->Dt_Final

Take one read range activity pass the Dt_Final as Data table-> FileName pass like this

Example:- "Desktop\UninsuredOut"+XlsxCounter.ToString+".xlsx"

Don’t Forgot to increment the counter here:

XlsxCounter=XlsxCounter+1

Try this and let me know

Regards,
NaNi

Nithinkrishna · June 26, 2022, 3:20am

Hey @Khooshbu_Jani1

Then you can loop through the files and use Generate Data Table activity for converting it to table.

Hope that helps

Thanks
#nK

Rahul_Unnikrishnan · June 26, 2022, 8:46am

Hello @Khooshbu_Jani1

You can look through each txt file in the folder

use read file to read the text file

Use text to datatable activity

Use write range activity to write to excel

Khooshbu_Jani1 · June 26, 2022, 4:54pm

Hi,

I am thankful to you.

I am good with first part but when I am working on second part of converting .txt to .xlsx I am getting following error in Write Range activity.

Job stopped with an unexpected exit code: 0xE0434352

I checked if excel process is running in task manager

THIRU_NANI · June 27, 2022, 2:35am

Hi!

Instead of using Excel activities use workbook activities.

Make sure that you have killed the excel process before going for the process.

Try it and let me know

Regards,
NaNi

Khooshbu_Jani1 · June 27, 2022, 10:41pm

Hi @THIRU_NANI
I am using Workbook activities

and also have killed the process.

Khooshbu_Jani1 · June 28, 2022, 1:24pm

Hi @THIRU_NANI,

Today I tried with couple of things

Since Text files have millions of rows, I converted that first from txt to csv and then from csv to xlsx

But then it is not changing the index from one file to other and hence it has the same file name and asking for overwrite.

THIRU_NANI · June 28, 2022, 1:28pm

Hey!

Just use Generate data table. This will give you the Data table output

You can pass this to Write range.

No need to convert this to Csv and CSV to Xlsx

Regards,
NaNi

postwick · June 28, 2022, 1:35pm

That looks tab delimited.

Read CSV into datatable, Write Range to Excel with the new name.

Khooshbu_Jani1 · June 28, 2022, 3:02pm

@THIRU_NANI

But then I get following error:
Job stopped with an unexpected exit code: 0xE0434352

This is the reason I tried to write data table in csv format and then convert it to .xlsx

Khooshbu_Jani1 · June 29, 2022, 1:20pm

Hi@Thiru_Nani

Thank you so much.
I got it.
You helped me a lot.

THIRU_NANI · June 29, 2022, 1:44pm

Hey!

Mark the appropriate post as solution and close the thread.

Regards,
NaNi

Khooshbu_Jani1 · June 29, 2022, 2:30pm

Sure . I will do that.

But just for information, I have to take txt to csv and then to xlsx.

without converting to csv, it was giving the above error.

Thank you once again

system · July 2, 2022, 2:31pm

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Convert .txt to .xlsx file Help studio	17	3111	November 2, 2019
Converting all the .xls files in a folder to .xlsx and rename them from list of names from a text file Activities excel , activities , question	1	861	August 25, 2022
Rename txt file to excel file Learning Hub	4	985	June 12, 2020
Rename a text file to xls StudioX studiox , question	4	854	August 18, 2022
Rename file name from .xls to .xlsx in Studio 2020.4.1 Learning Hub	5	1176	June 25, 2020

Convert each file in a folder from .txt to .xls and also rename the same files

Related topics