Converting Txt file into Excel

[krishna.kodimurali9]

Hi Team,

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

[EmersonF]

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​​

[ewilson]

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,

[krishna.kodimurali9]

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

[krishna.kodimurali9]

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 )

[EVIPUTI]

@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")
​

[richieadam]

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

[michaeloneil · Accepted solution]

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