Save each Excel-spreadsheet-row with header in separate .txt-file (saved as a parameter-sample to be read by simulation programs)

Question

I'm a building energy simulation modeller with an Excel-question to enable automated large-scale simulations using parameter samples (samples generated using Monte Carlo). Now I have the following question in saving my samples:

I want to save each row of an Excel-spreadsheet in a separate .txt-file in a 'special' way to be read by simulation programs.

Let's say, I have the following excel-file with 4 parameters (a,b,c,d) and 20 values underneath:

a    b    c    d
2    3    5    7
6    7    9    1
3    2    6    2  
5    8    7    6
6    2    3    4

Each row of this spreadsheet represents a simulation-parameter-sample. I want to store each row in a separate .txt-file as follows (so 5 '.txt'-files for this spreadsheet):

'1.txt' should contain:

a=2;
b=3;
c=5;
d=7;

'2.txt' should contain:

a=6;
b=7;
c=9;
d=1;

and so on for files '3.txt', '4.txt' and '5.txt'.

So basically matching the header with its corresponding value underneath for each row in a separate .txt-file ('header equals value;').

Is there an Excel add-in that does this or is it better to use some VBA-code? Anybody some idea?

(I'm quit experienced in simulation modelling but not in programming, therefore this rather easy parameter-sample-saving question in Excel. (Solutions in Python are also welcome if that's easier for you people))

Answer 1

my idea would be to use Python along with Pandas as it's one of the most flexible solutions, as your use case might expand in the future.

I'm gonna try making this as simple as possible. Though I'm assuming, that you have Python, that you know how to install packages via pip or conda and are ready to run a python script on whatever system you are using.

First your script needs to import pandas and read the file into a DataFrame:

import pandas as pd

df = pd.read_xlsx('path/to/your/file.xlsx')

(Note that you might need to install the xlrd package, in addition to pandas)

Now you have a powerful data structure, that you can manipulate in plenty of ways. I guess the most intuitive one, would be to loop over all items. Use string formatting, which is best explained over here and put the strings together the way you need them:

outputs = {}

for row in df.index:
    s = ""
    for col in df.columns:
        s += "{}={};\n".format(col, df[col][row])
    print(s)

now you just need to write to a file using python's io method open. I'll just name the files by the index of the row, but this solution will overwrite older text files, created by earlier runs of this script. You might wonna add something unique like the date and time or the name of the file you read to it or increment the file name further with multiple runs of the script, for example like this.

All together we get:

import pandas as pd

df = pd.read_excel('path/to/your/file.xlsx')
file_count = 0

for row in df.index:
    s = ""
    for col in df.columns:
        s += "{}={};\n".format(col, df[col][row])

    file = open('test_{:03}.txt'.format(file_count), "w")
    file.write(s)
    file.close()

    file_count += 1

Note that it's probably not the most elegant way and that there are one liners out there, but since you are not a programmer I thought you might prefer a more intuitive way, that you can tweak yourself easily.

Answer 2

If you can save your Excel spreadsheet as a CSV file then this python script will do what you want.

with open('data.csv') as file:
    data_list = [l.rstrip('\n').split(',') for l in file]

counter = 1

for x in range (1, len (data_list)) :
    output_file_name = str (counter) + '.txt'
    with open (output_file_name, 'w' ) as file :
        for x in range (len (data_list [counter])) :
            print (x)
            output_string = data_list [0] [x] + '=' + data_list [counter] [x] + ';\n'
            file.write (output_string)
    counter += 1

Answer 3

I got this to work in Excel. You can expand the length of the variables x,y and z to match your situation and use LastRow, LastColumn methods to find the dimensions of your data set. I named the original worksheet "Data", as shown below.

Sub TestExportText()

    Dim Hdr(1 To 4) As String
    Dim x As Long
    Dim y As Long
    Dim z As Long

    For x = 1 To 4
        Hdr(x) = Cells(1, x)
    Next x

    x = 1
    For y = 1 To 5

        ThisWorkbook.Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Name = y
        For z = 1 To 4
        With ActiveSheet
                .Cells(z, 1) = Hdr(z) & "=" & Sheets("Data").Cells(x + 1, z) & ";"
        End With
        Next z
        x = x + 1
        ActiveSheet.Move
        ActiveWorkbook.ActiveSheet.SaveAs Filename:="File" & y & ".txt", FileFormat:=xlTextWindows
        ActiveWorkbook.Close SaveChanges:=False
    Next y

End Sub