topic Re: Converting Txt file into Excel in Product Forum

Converting Txt file into Excel

krishna.kodimurali9 — Fri, 22 Oct 2021 10:44:49 GMT

Hi Team,

How can we convert the txt file into an Excel file?

Re: Converting Txt file into Excel

EmersonF — Fri, 22 Oct 2021 12:47:54 GMT

Hi Murali, Fine, it's been a while since I came here, but have you considered importing the TXT into the excel file?
Link Guide:Import or export text (.txt or .csv) files (microsoft.com),let me check if i have something ready for this 😄

#Edit
JSFiddle - Code Playground
Just download and save as .bpobject

Re: Converting Txt file into Excel

ewilson — Fri, 22 Oct 2021 14:27:41 GMT

Is the txt file actually a CSV? If so, there is an Import CSV action on the Excel VBO.

If not, the question becomes what is your expectation for what the txt file contents will look like in Excel. It's pretty easy to just read the contents of a text file and then insert the entire contents into a cell in a worksheet.

You could also look at reading the contents of the txt file, push that data into a BP Collection (ex. line by line) and then use the Write Collection action on the Excel VBO to write the contents of the Collection into a worksheet.

I'm sure there are various other methods.

Cheers,

Re: Converting Txt file into Excel

krishna.kodimurali9 — Wed, 27 Oct 2021 14:03:17 GMT

Thanks Emerson for your reply, I was looking if there is any inbuilt solution for that.

Re: Converting Txt file into Excel

krishna.kodimurali9 — Wed, 27 Oct 2021 14:03:48 GMT

HI @ewilson,
It's not CSV, instead its DAT file.

On the process flow, we download it from an external source, and later we need to convert it to excel for further operations.

For now we are doing it like- Open the DAT file with notepad >>Copy complete>>Paste it on a new Excel Wokbook>> Convert text to Columns (to populate correct data in all columns )

Re: Converting Txt file into Excel

EVIPUTI — Sat, 30 Oct 2021 05:51:58 GMT

@krishna.kodimurali9
Someone helped me before with this which is simply changing filetype , not sure if this helps you but worth a try :

Dim objFile As New System.IO.FileInfo("path\filename.dat")
objFile.MoveTo("path\filename.xls")

Re: Converting Txt file into Excel

richieadam — Tue, 02 Nov 2021 17:14:25 GMT

Is the txt file actually a CSV?

If so, there is an Import CSV action on the Excel VBO.

If not, the question becomes what is your expectation for what the txt file contents will look like in Excel.

It's pretty easy to just read the contents of a text file and then insert the entire contents into a cell in a worksheet.

You could also look at reading the contents of the txt file,

push that data into a BP Collection (ex. line by line) and then use the Write Collection action on the Excel VBO to write the contents of the Collection into a worksheet.

I'm sure there are various other methods.
......................................................
Ornament Central

Re: Converting Txt file into Excel

michaeloneil — Wed, 03 Nov 2021 10:54:56 GMT

Hi

This can be done with the excel utility as you would do with any excel file. If you create an instance then use open workbook and set the filename to be your text file e.g. "C:\Users\miconeil\Documents\mytextfile.dat" and the file will open in excel then just use the excel utility to save the file as an excel file.

Hope this helps