Reading CSV files into a macro matrix

Import data from other programs into Ebsilon

Comma-Separated Values (CSV) files are quite useful for transferring data between applications. Here’s a sample with P-T-H-M data for a bunch of locations:

PipeName,Pressure,Temperature,Enthalpy,Mass Flow

H2O_DAMPF,200,540,3366.447646,200

H2O_DAMPF_1,120.0001392,506.1261113,3366.447646,200

H2O_DAMPF_2,30,300,2994.349322,0.000001

H2O_DAMPF_3,0.069999999,39.00086303,2182.799196,200

H2O_FLUESSIG,220,40.97266349,190.9344844,200

H2O_FLUESSIG_1,0.069999999,39.00086303,163.3655145,200

H2O_FLUESSIG_2,1.949999999,34.00086333,142.6398619,13804.86628

Gas,2,27,n/a,123.456

Here’s how to import that file into a Specification or Result Matrix in an Ebsilon macro like this:

The sample files

There are two files in the folder “CSV to macro matrix” on my Ebsilon Tips OneDrive. One is an Ebsilon model with a single macro. The other is the sample CSV file I used.

Define the Matrix

Right-click your macro, then double-click the Macro-Interface, and define either a Specification or Result Matrix. Here, I’ve created a Result Matrix named “MYMATRIX”. The process would be the same for a Spec Matrix.

I’ve made these settings:

• This CSV file has a header row, so I’ve checked the “x-Header-Line”.

• The first column — the x values — is text (the pipe names), so I’ve selected the “x-Quantity” as “Text” (the “String” type does the same thing).

• Likewise the first row is text, so “y-Quantity” is set to “Text”.

• The body of the data — the Values — is numeric, but I there’s also a “n/a” in the data, so I’ve set the “Value-Quantity” as “Variant” to allow for mixed data types.

• Some just-to-be-nice things: I added an “x-Description”, “y-Description”, and “Value-Description”. The first two are fairly obvious. The latter is set to “Data is in SI” to make it clear what the data is.

Note that in a Matrix, the “Value-Quantity” must be all the same type (for example, all Pressures, all Unitless, all integers, or whatever). You can’t define a data type for each column. So here, it’s helpful to tell the user it’s “standard Ebsilon SI units”.

The EbsScript code

Put this code wherever needed for your application (stand-alone *.esc file, internally-saved EbsScript, or the macro’s pre- or post-run EbsScript). In my sample model, I have this code in the macro’s Pre-Run EbsScript.

function SplitLine(txt : string) : array of string ;

// Breaks a comma-delimited line of text into an array

var

i : integer ;

a : array of string ;

begin

repeat

i := stringFind(txt, ",") ;

if i = 0 then

append(a, txt) // no more commas on the line. txt is the last chunk of text.

else

begin

append(a, stringLeft(txt, i-1)) ; // Get the text to the left of the comma

txt := stringMid(txt, i+1) ; // Remove the text up to and including the first comma

end ;

until i = 0 ;

SplitLine := a ;

end ;

//-----------------------------------------------------------------------------

var

filename : string ;

i : integer ;

j : integer ;

k : integer ;

lines :array of String;

bOK : boolean ;

M : ebsmatrix ;

a : array of string ;

nCol : integer ;

nRow : integer ;

begin

clrscr ;

filename := "\\\\Mac\\Home\\Documents\\Ebsilon\\example.csv" ;

bOK := readLinesFromFile(filename, lines, 1, -1) ;

if not bOK then

begin

messagebox("Error reading " + filename) ;

exit(1) ;

end;

// Number of lines of data read from the file

nRow := length(lines) ;

// Split the first line so we know how many columns we'll have

a := SplitLine(lines[0]) ;

nCol := length(a) ;

// Set M to the matrix to make code easier to read

M := ::Macro_object.MYMATRIX;

// These 2 lines effectively clear all data from the matrix. We don't want to carry over old data!

M.headerSizeX := 0 ;

M.headerSizeY := 0 ;

// Set the matrix size

M.headerSizeX := nRow ;

M.headerSizeY := nCol ;

// Header text is first row read from file... we grabbed and split that already, as "a"

// Set column headers (note, can't set the top-left corner cell 1,1)

M.data(1,2) := a[1] ;

M.data(1,3) := a[2] ;

M.data(1,4) := a[3] ;

M.data(1,5) := a[4] ;

// Fill the matrix. Row 1 is the header. Data starts at row 2. Remember, "lines" is zero-based... start at element 1

for i := low(lines)+1 to high(lines) do

begin

a := SplitLine(lines[i]) ;

j := i + 1 ; // "lines" array is 0-based, but Matrix data starts at 1, thus the k+1

for k := low(a) to high(a) do

M.data(j,k+1) := a[0] ;

end ;

messagebox("Done") ;

end;

---