MathGroup Archive 2002

[Date Index] [Thread Index] [Author Index]

Search the Archive

RE: Reading excel files?


Below is a copy of a package I wrote to read & write dat files
of 2D data between Excel and Mathematica.
Save the data only, no row or column titles.
I save the Excel data as "Formatted Text, Space Delimited."

The package also will convert data in a 2D matrix format to triplet format
and back.

The package as written must be stored in a folder named "Personal," which
in turn, is in a directory in Mathematica's path ($Path).  But you can change this
requirement as you like by changing the BeginPackage statement.

John C. Erb


(* Written by John C. Erb, Ph.D.
   June 14, 2002
   Mathematica, Version 4.1 *)

datFileFromExcel::usage="datFileFromExcel[FileName] imports 2D data
stored as dat file (as stored by Excel using the Formatted Text, Space
Delimited option), and converts the data into rowValues, colValues,
and tableValues.\n
For example:\n
{rowData,colData,tableData} =

takes 2D data
in the form of rowData, colData, and tableData, and formats the data
so the result can be exported as a dat file, and read by Excel so that
all the rows and columns are properly aligned.\n
For example:\n

Convert2DToTriplet::usage="Convert2DToTriplet[rowData, colData, tableData]
2D data in the form of rowData, colData, and tableData, and reformata the
into triplet values {x1,y1,data12}, {x1,y2,data12}, ... for use by
Mathematica routines.\n
For example:\n
tripletData = Convert2DToTriplet[rowData,colData,tableData]"

ConvertTripletTo2D::usage="ConvertTripletTo2D[tripletData] takes data in the
triplet values {x1,y1,data12}, {x1,y2,data12}, ... {xm,yn,datamn},
and converts the data into rowData, colData, tableData.\n
For example:\n
{rowData,colData,tableData} = ConvertTripletTo2D[tripletData]"


(* Module for importing and converting a dat file saved from Excel *)
rowValues = Flatten[Drop[Map[Take[#, 1] &, data], 1]];
colValues = data[[1]];
tableValues = Map[Drop[#, 1] &, Drop[data, 1]];

(* Module to convert and save data into a dat file for import into Excel *)
datFileToExcel[FileName_String,rowData_List, colData_List, tableData_List]
    Module[{newcolData, newtableData,outputData},
      newcolData = Prepend[colData, " "];
      newtableData = Transpose[Prepend[Transpose[tableData], rowData]];
      outputData=Prepend[newtableData, newcolData];

(* Module for Converting 2D data in table form to triplet data (by rows) *)
Convert2DToTriplet[rows_, cols_, datatable_] := Module[{data, xxyy},
    data = Flatten[datatable];
    xxyy = Transpose[Flatten[Outer[List, rows, cols], 1]];
    Transpose[{xxyy[[1]], xxyy[[2]], data}]]

(* Module for Converting data in triplet fromat to table form *)
ConvertTripletTo2D[triplet_List] :=
    Module[{numberCols, numberRows, tableData, rowData, colData},
      numberCols = Count[triplet, x_ /; SameQ[x[[1]], triplet[[1, 1]]]];
      numberRows = Length[triplet]/numberCols;
      tableData = Partition[Transpose[triplet][[3]], numberCols];
      colData = Transpose[Take[triplet, numberCols]][[2]];
      rowData =
            Take[triplet, {1, numberRows*numberCols, numberCols}]][[1]];
      {rowData, colData, tableData}];




-----Original Message-----
From: Stefanos Dris [mailto:dris at]
To: mathgroup at
Subject: [mg35562] [mg35542] Reading excel files?

I would like to be able to read from and write to Excel spreadsheets
using Mathematica. In my search I found Mathematica Link which, among
other things, looks like it can do this. Is there any other way of
achieving this?

As it stands, I manually enter a table of data in an Excel spreadsheets,
I save it as a tab-delimited text file, and hence my Mathematica program
can read it. Since my program output is another text file, I need to
pick the right options when importing the data back into Excel,
otherwise the columns and rows may be messed up.

If I could read and right directly to the cells of an Excel spreadsheet,
it would make my life just a little bit easier.

Stefanos Dris

  • Prev by Date: RE: RE: Re: help in generating a gaussian random variable
  • Next by Date: Repost recent messages - Administration
  • Previous by thread: Reading excel files?
  • Next by thread: Re: RE: Reading excel files?