Re: Import/Export of data to/from Mma and Excel.

*To*: mathgroup at smc.vnet.net*Subject*: [mg2726] Re: [mg2667] Import/Export of data to/from Mma and Excel.*From*: penny at edu-suu-scf.sc.suu.edu (Des Penny)*Date*: Sat, 9 Dec 1995 01:56:51 -0500

>Please, anybody out there, help me. I just started to have a look at >Mathematica, and it is driving me &%$#@* nuts! What I want to do is >REALLY simple: > >1. Run FORTRAN code, write some data (x,y and z) to a file. Three columns. > >2. Start Mathematica, read my data file from 1. > >3. Make a 3D plot, using the x, y and z values from my data file. > >Simple? That's what I thought, but after 5 hours of trial-and-error, and >futile attempts at reading the manual ("Mathematica, a system for doing >mathematics by computer" by Stephen Wolfram), I'm just about ready to >throw in the towel. This manual is of NO help to me. There are no useful >examples for doing this kind of thing that I've found. In fact, the manual is >not much help for anything. (For the record, this is hardly the first time >I've used a computer related manual.) > >Someone, please give me a hint on what to do. I can feel the mother of >all ulcers forming. FYI, I'm doing this on a VAX/VMS system with DECWindows. > >Roland Larsson larsson at bartol.udel.edu Hi y'all: There have been a few questions relating to the transfer of information in and out of Mma recently. I thought I'd earn my keep and try to give the fruits of my investigations into this matter. Last year I taught a class in Mma and for one project I had the students export data from Mma and then import the data into Excel. I then had them reverse the process - i.e. create data in Excel and import the data into Mma. For verification purposes I had them plot the imported data and do a linear regression, and show that Excel gave the same results as Mma. We did this both on the Mac and IBM. Here is the essence of the notebook I prepared to explain the above process: (* Create data in Mma. Note that the data is a list of {x,y} data: *) list= Table[{x, (1.89 10^-9 + 3.7 10^-11 x)^(-1/3)+Random[Real,{-5,5}] } , {x,0,30}]; ListPlot[list] (* In this section we show how to write the data in "list" onto a floppy both on the Mac and IBM. We then read the data from the floppy back into Mma. *) (* For Mac: *) (* *************************************************************** *) (* write data onto a floppy disk named "DiskName" into a file named "FileName" *) outFile=OpenWrite["DiskName:FileName"]; Do[ WriteString[outFile, list[[i,1]]," ", list [[i,2]],"\n" ], {i,1,Length[list]} ]; Close["DiskName:FileName"]; (* The data has been written as a "space delimited" file. The two columns of data are separated by a printed space. Note the " " in the above code. Verify that the data has been written to the file: *) !!DiskName:FileName (* Output is given by Mma *) (* The data is now in the file as a two column, space delimited, matrix. Any data in this form can be read into Mma by the following: *) data=ReadList["DiskName:FileName", {Number, Number}]; ListPlot[data]; (* note that the data has been read back into a different variable and then plotted. *) (* ******************************************************************* *) (* for the IBM *) (* ******************************************************************* *) (* write data onto a floppy in drive a: onto a file "datafile" with path a:\phsc305/datafile *) outFile=OpenWrite["a:\phsc305/datafile"]; Do[ WriteString[outFile, list[[i,1]]," ", list [[i,2]],"\n" ], {i,1,Length[list]} ]; Close["a:\phsc305/datafile"]; (* The data has been written as a "space delimited" file. The two columns of data are separated by a printed space. Note the " " in the above code. Verify that the data has been written to the file: *) !!a:\phsc305/datafile (* The data is now in the file as a two column, space delimited, matrix. Any data in this form can be read into Mma by the following: *) data=ReadList["a:\phsc305/datafile", {Number, Number}]; ListPlot[data]; (* note that the data has been read back into a different variable and then plotted. *) (* ******************************************************************* *) The data in the files "FileName" (Mac) and "datafile" (IBM) can be imported into Excel/Mac (or any other spreadsheet) as follows: 1. Open up Excel. 2. Choose "Open". 3. Click on "Text". 4. Choose "Column Delimeter: Space". Click "OK". (This is because the columns are separated by a space.) 5. Double click on the file to be opened. This will read the data in "Filename" into two columns into Excel. Moreover, the two columns are recognized as numbers and not as text. Similarly a two (or three) column spreadsheet can be exported from Excel. The command one uses is typically to write a text file. On Excel for the Mac: 1. Create data in Excel. The data must be columns of numbers. Do not have any headers or any other information. 2. Choose "Save As". Type in the name of the file and choose location for file. 3. Click "Options". 4. Choose: File Format:Text 5. Click "Save" This saves all the data in "Tab" delimited form. This behaves the same as "space delimited". This data can now be imported into Mma as described above i.e. with data=ReadList["DiskName:FileName", {Number, Number}]; (* Mac *) data=ReadList["a:\phsc305/datafile", {Number, Number}]; (* IBM *) ***************************************************************************** Roland: I'm sure you can adapt the examples above to your specific case. When you are writing data from Fortran, explicitly write a space (" ") into the file to separate the numbers. Write each set of 3 numbers onto a separate record (i.e. line). The following Mma statement should then read the data without a problem: data=ReadList["DiskName:FileName", {Number, Number, Number}]; (* Mac *) data=ReadList["a:\phsc305/datafile", {Number, Number, Number}]; (* IBM *) I havn't verified this last part with Fortran but it should work. Hope this helps. Cheers, Des Penny ========================== Des Penny Physical Science Dept. Southern Utah University Cedar City, UT 84720 VOICE: (801) 586-7708 FAX: (801) 865-8051 e-mail: penny at suu.edu ==========================