Convert txt file to Excel file via batch script

Ones_Zeros · Jun 30, 2020

Hello
I have a folder that has txt files added as reports is ran.
By default these reports is saved as a .txt file and no way to change
this default extension.

My question is how is the best way to convert these txt files to excel files?
I was hoping to write some batch script file that automatically converts these newly added txt files to excel files.

The batch would have to change the name of each text file to it’s own
unique name so there is no duplicate names.

Can this be easily accomplished?

Thanks

parky · Jul 1, 2020

First of all is the data comma delimited or some other form.
Unfortunately excel will need some king of way to determine the cells for example This is a line of text, This is another line of text
The comma's determine how excel will split the two messages into different cells. Also each new line will change to another row in excel.
If the file extension is changed to "csv" then excel will try to load it but it does require the delimiters to split it into the relevant cells like ", ;" .
You will not have to change the filename only the extension.
a batch file like this may help

ren *.txt *.csv
If the batch file is in the same directory then you don't need to add the path, this will just change the extension but if you want to keep original you will need to put them in another directory.

Or if you have visual basic for applications installed in excel you could put a button on a sheet to call a VB routine that either does it for you or even better
make the button load a file ocx to select the file and convert it using vba etc. (note: never tried it and would depend on what add on's are enabled.

drbitboy · Jul 1, 2020

Parky summarized the basic ways this can be done; it depends on the environment. Other than renaming .txt to .csv as parky suggested, using only a .BAT file to convert the data to an eXcel file would be difficult if not impossible. Visual Studio, or even Python with the pandas module, makes it a lot simpler, but it depends on the file. Can the file be attached here?

One other option is to star eXcel (or LibreOffice Calc

), then right-click on the .txt file, then click [Open with ...], then select eXcel. IIRC eXcel wil then bring up a dialog asking how to interpret the file, e.g. what separates columns as parky mentioned. Another way to do this is to start eXcel and then choose [File] => [Import ...] (ask The Google about "import text file into excel").

Without seeing the file or knowing anything the environment, it is hard to provide the best solution, or even a workable solution. Also, does this need to happen automatically so .xlsx files are created, or is it more likely to be done on an ad hoc basis, one file at a time manually? For example, if Cygwin was available, I could likely cobble summat like a prototype together in half an hour or less. With enough time and money even making .xlsx files with plots is possible.

I wonder if there are online services, where the files could be posted and eXcel files returned or emailed; to create such a custom server on AWS the individual steps would be straightforward, but the setup more involved.

Ones_Zeros · Jul 1, 2020

Thanks a lot
Once I get the sample text file that I need to
Convert to .csv I will know better on how it’s formatted.

I’m trying to gather info on how this is done, sorry
I didn’t realize there was so many variables or
I would had gotten the txt file first.

I will post back

Thanks again

Toine · Jul 3, 2020

drbitboy said:
using only a .BAT file to convert the data to an eXcel file would be difficult if not impossible. Visual Studio, or even Python with the pandas module, makes it a lot simpler,

Don't forget Powershell. It is available in all current Windows versions and works fine to script Excel. Plenty of examples can be found online. It may require some programming skills to adjust for your particular needs and not every PLC programmer is comfortable with text programming like Powershell.

drbitboy · Jul 3, 2020

Toine said:
Don't forget Powershell. It is available in all current Windows versions and works fine to script Excel. Plenty of examples can be found online. It may require some programming skills to adjust for your particular needs and not every PLC programmer is comfortable with text programming like Powershell.

Ooh, good point. Powershell has BASH, or summat close, right?

It would not be pretty, but this could be done in BASH.

Toine · Jul 3, 2020

drbitboy said:
Ooh, good point. Powershell has BASH, or summat close, right?

It would not be pretty, but this could be done in BASH.

I only know some basic bash from a few Linux boxes that I have running, but I'm far from a Linux guru so I wouldn't know how similar it is to powershell. In this context, I believe the object oriented nature of powershell is quite specific for the windows platform. It allows you to control Excel from a ps script, basically doing just about anything with the Excel object model that you could also do either by clicking it together manually in Excel or through Excel VBA scripting.

In the past I have written a few ps scripts to automatically read csv log files into spreadsheets and do calculations on the data for reporting purposes. It worked fine for my purposes. I cannot share these scripts, don't ask.

As to whether or not it would look pretty.... that depends both on the skills of the programmer and on the eye of the beholder.

drbitboy · Jul 3, 2020

Easy peasy

https://stackoverflow.com/questions/17688468/how-to-export-a-csv-to-excel-using-powershell

Ones_Zeros · Jul 6, 2020

The only way I was able to get the data close to the correct format when converting from .txt to .xls was below. I have to open the .txt into Excel and go through the converting process.

Convert .txt to .xls using Excel to do this..

1. Delimited
2. Set delimiters
- Tab
- Semicolon
- Comma
- Space

3.Data Format
- General

My question is there a simple batch file that I can run that will convert these text files to .xls files that is contained in the folder?
This batch file would have to create separate names for each .xls file when converted from .txt to .xls

thanks,

parky · Jul 6, 2020

That assumes it is already a CSV file, Post one of the files, it may be that even though it does not contain commas, tabs etc. there is a way but need to see the file first to get an idea. Could you post one.

Ones_Zeros · Jul 6, 2020

Thanks for helping
I have attached the .txt file

thanks,

Dravik · Jul 6, 2020

So, depending on what you actually need from that report, you're going to need to write a parser most likely.

*If* you just needed the 'data' lines 12-34, you could strip the first 11 and run a regex to parse the white space into a , and then export as .csv for example.

In the past I would just write a perl or bash script to do this.

YMMV on what you might want to support if anything.

drbitboy · Jul 6, 2020

What Dravik said.

Also note that, if this text format will essentially never change, except for the data i.e. numbers, dates, etc.*, it is not that difficult to have a script that will generate a custom .XLSX file, complete with plots or whatever else is of use. Obviously that would take a lot more effort, and/or cost if it were to be sub-contracted out. Python would be my go-to first choice if that were available, or possibly Javascript if a web-based solution is desired, but if this had to be run on a PC then Visual Studio could get the job done.

* Some variation in the quantity and ordering of components could be handled without too much trouble.

** Perl, as the swiss army knife of programming languages, is the wrong tool for every problem, though perhaps not here.

Lare · Jul 6, 2020

Install autohotkey, then you can simple code allmost everything and compile your code to .exe which you can run from even from command window.

www.autohotkey.com

fast test with autohotkey allmost makes correct csv:

I used F2-function key for testing, but you can run it only when executed then exit.



F2::

TxtFile := "C:\temp\MyFile.txt"

OutputCSV := "C:\temp\MyFile.CSV"

Loop, Read, %TxtFile%, %OutputCSV%

{

    if ( A_Index = 1 )

        App_line := "Title No.,Unique No."

    else

    {



        App_line := StrReplace(A_LoopReadLine, A_Space, "_")        

        



        App_line := StrReplace(App_line, "__", ";")

        App_line := StrReplace(App_line, ";__", ";")

        App_line := StrReplace(App_line, ";_", ";")



        App_line := StrReplace(App_line, ";;;;", ";")

        App_line := StrReplace(App_line, ";;;", ";")

        App_line := StrReplace(App_line, ";;", ";")

    

    }    

    FileAppend, %App_line%`n

}

return

csv-result (semicolon seperated)







Title No.,Unique No.



;Analyzed_By:;

;Meter_ID:;GC_DATA

;Analysis_Time:;07/01/2020_14:38;Sample_Type:;Spot

;Flowing_Temp.:;0;Deg._F;Flowing_Pressure:;0_psig





;Comp;UnNorm;Normal;Liquids;Ideal;Rel._Density

;%;%;(USgal/MCF);(Btu/SCF)_

;-------------------------------------------------------------------------------

;Propane;0.15451;0.16656;0.04596;4.19086;0.00254

;IsoButane;0.01294;0.01395;0.00457;0.45356;0.00028

;Butane;0.01733;0.01868;0.00590;0.60942;0.00037

;NeoPentane;0.00000;0.00000;0.00000;0.00000;0.00000

;IsoPentane;0.00348;0.00375;0.00137;0.15013;0.00009

;Pentane;0.00195;0.00210;0.00076;0.08410;0.00005

;Hexane+;0.01140;0.00000;0.00000;0.00000;0.00000

;Hydrogen;0.00000;0.00000;0.00000;0.00000;0.00000

;Oxygen;1.15096;1.24076;0.11031;0.00000;0.01371

;Nitrogen;9.65930;10.41297;1.14742;0.00000;0.10072

;Carbon_Monoxide;0.00000;0.00000;0.00000;0.00000;0.00000

;Methane;79.02261;85.18839;14.46488;860.40271;0.47186

;Carbon_Dioxide;1.62813;1.75516;0.30001;0.00000;0.02667

;Ethylene;0.00000;0.00000;0.00000;0.00000;0.00000

;Ethane;1.09959;1.18539;0.31752;20.97782;0.01231

;Hexanes;0.00000;0.01228;0.00506;0.58426;0.00037

;Heptanes;0.00000;0.00000;0.00000;0.00000;0.00000

;Octanes;0.00000;0.00000;0.00000;0.00000;0.00000

;Propane+;0.00000;0.00000;0.00000;0.00000;0.00000

;Carbon_Dioxide+;0.00000;0.00000;0.00000;0.00000;0.00000

;Ethane-;0.00000;0.00000;0.00000;0.00000;0.00000

;-------------------------------------------------------------------------------

;Total;92.76218;100.00000;16.40376;887.45282;0.62989



;Inferior_Wobbe;1113.0374_(Btu/SCF);Superior_Wobbe;1122.8274_(Btu/SCF);

;Compressibility;0.9982;Density;0.0482;(lbm/ft3);

;Real_Rel._Density;0.6299;Ideal_CV;887.4528;(Btu/SCF);

;Wet_CV;875.6688;(Btu/SCF);Dry_CV;891.1408;(Btu/SCF);

;Contract_Temp.;60.0000;(deg_F);Contract_Press.;14.7300;(psia);

;Number_of_Cycles;1;Connected_Stream_1

;Atmospheric_Pressure_14.65

Ones_Zeros · Jul 6, 2020

Thanks I’ll give auto hot key a try.
I’ve never used it before. Can you code it
to just make the output .csv name the same
as the what the .txt file is? This would keep things
simple and uniform.

Someone asked the question if the format
of the .txt will always be the same.
Yes, the format will remain the same, just the data will be changing.

Also to note this batch file will be running on a
local PC at the site we’re the GC samples are ran.

Trying to make this as simple as possible for the end user running the
samples, a lot of these guys are not tech savvy and the output
needs to be in .csv format, hopefully without someone having to open
each one and convert them.

Thanks for your help, I do appreciate it

Convert txt file to Excel file via batch script

Ones_Zeros

Member

parky

Member

drbitboy

Lifetime Supporting Member

Ones_Zeros

Member

Toine

Member

drbitboy

Lifetime Supporting Member

Toine

Member

drbitboy

Lifetime Supporting Member

Ones_Zeros

Member

parky

Member

Ones_Zeros

Member

Dravik

Member

drbitboy

Lifetime Supporting Member

Lare

Member

Ones_Zeros

Member

Similar Topics