Services & ResourcesWolfram ForumsMathGroup Archive

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

RE: Reading excel files?

• To: mathgroup at smc.vnet.net
• Subject: [mg35562] RE: [mg35542] Reading excel files?
• From: "John C. Erb, Ph.D." <John_C_Erb at prodigy.net>
• Date: Fri, 19 Jul 2002 06:08:14 -0400 (EDT)
• Sender: owner-wri-mathgroup at wolfram.com

Stefanos,

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

Package:
===========================================================================
BeginPackage["Personal`ExcelDatFile`"];

(* 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} =
datFileFromExcel[\"C:\\\\myDirectory\\\\myFile.dat\"]"

datFileToExcel::usage="datFileToExcel[FileName,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
datFileToExcel[\"C:\\\\myDirectory\\\\myFile.dat\",rowData,colData,tableData
]"

Convert2DToTriplet::usage="Convert2DToTriplet[rowData, colData, tableData]
takes
2D data in the form of rowData, colData, and tableData, and reformata the
data
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
form
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]"

Begin["`Private`"];


(* Module for importing and converting a dat file saved from Excel *)
datFileFromExcel[FileName_String]:=Module[{data,rowValues,colValues,tableVal
ues},
data=Import[FileName];
rowValues = Flatten[Drop[Map[Take[#, 1] &, data], 1]];
colValues = data[[1]];
tableValues = Map[Drop[#, 1] &, Drop[data, 1]];
{rowValues,colValues,tableValues}];


(* 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];
Export[FileName,outputData,"Table"]];


(* 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 =
        Transpose[
            Take[triplet, {1, numberRows*numberCols, numberCols}]][[1]];
      {rowData, colData, tableData}];

End[];

Protect[datFileFromExcel,datFileToExcel,Convert2DToTriplet,ConvertTripletTo2
D];

EndPackage[];
==========================================================================

-----Original Message-----
From: Stefanos Dris [mailto:dris at fnal.gov]
To: mathgroup at smc.vnet.net
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.

Thanks
Stefanos Dris