Convert txt file to Excel file via batch script

It should be possible.
Autohotkey have good forum and examples.


But I think, thaat it would be something.


Timed subprogram, which selects file from folder.




(You don't want run script all the time, as script then reads harddrive all time.

And it is maybe easiest that you move converted txt files to different folder, so that you have only one txt file on folder (easier to select files?)


Then read selected filename to script and replace txt -> csv.
replacing all spaces with underscore
replacing undescores with ";" (don't replace spaces between words as they go then to diffrent colums (you need maybe replace some words before or after ";"



(Carbon Monoxide -> Carbon_Monoxide -> Carbon;Monoxide -> Carbon_Monoxide)


After converting



Move txt file to original txt files folder (or delete)

And save converted file to convrted folder (with new name + csv
 
Why not use VBA in excel, put a button on the excel spreadsheet, open up VBA on button pressed, use file manager add in to select the file and parse it into cells then save the file as an excel file. you will have to enable macro's in Excel and save it as a macro enabled template, when you open it up, press the button, bring up file manager, read in the text file parse it and then save it as an excel spreadsheet. I have done this although it was not a text file it was gathering data from a SQL database for reports but it's the same sort of thing. If you protect the workbook template to stop people from getting into the code you can have the template distributed to those who use it.
 
Here is autohotkey example.
Timed subprogram converts every 70 second txt file -> cvs.


If same csv is allready converted, old converted csv is moved to csv_old folder and txt-file is converted again.
After txt file(s) is converted, original txt file is moved to txt_old-folder.





There is still little error on script.


When there is no new txt files, script copies allready converted .csv files to txt_old folder.

This only happens if there isn't any txt files to convert and move *.txt files copies *.csv file instead. (conveerted csv are copied and orginal are also there, so it is only little error.)


Anyway, with little improvements it should work.



rename file to *.ahk and you have ahk code (autohotkey script)
 
Last edited:
Better version.
Error was coming from looping directories also to filenames.


Also changed first line of conversion, so that 1st line is same than on txt file.


On the end of file it also prints TXT to CSV conversion line.
 
Rather surprised no one brought up Python. With the xlsxwriter library, you can make Excel files directly.

Plus, a nifty pyinstaller library, that can make an .exe file out of the .py file. It bundles everything together so that the end user doesn't have to have anything installed.

Here's what I came up with.

Link to zip, as it was too large to put here.

  • Zip file contains the compiled .exe and a config.ini file, which stores the working directory name and archive directory name to put the .txt files in after they're processed. If the archive directory doesn't exist, it will be created.
  • I was envisioning this as something to be run from the task manager, say, every 30 seconds, for example, so it's capped to run 20 files at a time.

Source code:
Code:
import xlsxwriter, glob, configparser, shutil, os

# Function to check if a string is a float number
# Return original input if not a float
def check_if_float(stringIn):
    try:
        return float(stringIn)
    except ValueError:
        return stringIn

# set work and archive directories from config.ini file
configFile = configparser.ConfigParser()
configFile.read('config.ini')
workDir = configFile['GC Data']['workDir']
archiveDir = configFile['GC Data']['archiveDir']

# Create archive folder if it doesn't exist
if not os.path.exists(archiveDir):
   os.mkdir(archiveDir)

# limit processing to 20 files at a time to control memory usage and process time.
fileList = glob.glob(workDir + '\\*.txt')[:21]

for fileIn in fileList:
    #Notify user which file is processing:
    print('Processing file: '+ fileIn)

    with open(fileIn, 'r') as file:
        # split by newlines and trim whitespace
         dataIn = [[i.strip() for i in row.split('  ') if i] for row in file.read().split('\n')]
        
    # Start up an Excel workbook and as a worksheet     
    workbook = xlsxwriter.Workbook(fileIn.replace('.txt', '.xlsx'))
    worksheet = workbook.add_worksheet()

        # Set pointer for which row to write to. 
    rowIndex = 0
    # enumerate() returns the row and a number that we can use to filter certain lines 
    for row in enumerate(dataIn):
        data = row[1]
        if len(row[1]) > 0:
                    # Example: this row has the second set of headers shifted one column
                    # because of how we are dealing with whitespace. So, we'll insert
                    # a point in to make up for it.
                    if row[0] == 9:
                        data.insert(0,'')
                    # Check if row is full of dashes, if not, then process the row
                    if row[1][0][:3] != '---':
                        colData = enumerate(row[1])
                        for col in colData:
                            # Check if data is a float, otherwise use a string
                            # Write it to the worksheet.
                            worksheet.write(rowIndex, col[0], check_if_float(col[1]))
                        rowIndex += 1

    workbook.close()
    shutil.move(fileIn, fileIn.replace(workDir, archiveDir))
 
Guys it looks like I’m having to convert the .txt
file to .xls and put this data in separate columns in the
.xls in order for the data to be import into a database properly.

I’ve not done any Python programming, is it hard to
learn?

I’d like to post the .txt file and a sample of what
the .xls file needs to look like once the .txt file
is converted.

I appreciate the help and guidance with this.

Thanks
 
Python is one of the easier ones to learn.

I'd be happy to help out, but I'm on vacation away from my laptop (wife's orders). I can take a closer look Sunday evening / Monday morning if you post a example of what the output should look like.
 
yes, we already have the gc txt file; post a .xls or even better .xlsx


FYI: an eXcel file with the .XLSX extension is actually a .ZIP archive (meta-)file, almost every file in the archive is in XML format, and every cell is an XML element e.g. something like "<cell>1.2345</cell>.".


So it is straightforward to generate an .XLSX without things like the xlswriter or pandas modules in Python or Excel::... in Perl or whatever Visual Studio uses; only built-in file-writing capability is needed.* This even opens up the possibility that, if the final eXcel format is available as a template, and all that changes are the numbers, then formatting, colors and even plots are straightforward to include in the final product.


* Of course, some sort of ZIP library may be required to put it all together, although in some cases that can be done manually.
 
Thanks a lot I appreciate the help with learning Python.
I have a copy of what the .xls should look like as
What lines & columns the data needs to be in after
The conversion from txt to xls.

I will post copies of both.
Thanks again
 

Similar Topics

I need to convert my rslogix5 and control logix 5000 programs to a txt format so i can use in a searchable database any ideas..
Replies
8
Views
4,691
Hi all I have this text file of Gsd for Delta VFD drive. when I try to install this gsd file in simatic manager (HW config) I get error that Gsd...
Replies
5
Views
5,253
Hello all, I'm currently working on a servo motor linear positioning system (ball screw). I'm all set up regarding communication between my HMI...
Replies
1
Views
80
I have an application using an incremental encoder and then I convert it to degree (0-360) using calculation program. For a while, the calculation...
Replies
7
Views
232
Hi all. Me again still learning Rockwell. So I'll be polling an INT data array from a Modbus SE power meter with a L82 (with a Modbus ProSoft in...
Replies
56
Views
1,342
Back
Top Bottom