Exporting Columns To Text Files Using Excel

B

boyfarrell

Registered
Hi all.

I'm trying to get some of my experiemental data into into a simple column in a text file. For the sake of simplistiy of my 'reading in the data' function. I have (for the time being) only have one column of data per .txt file.

For example they might look like this:

1
200
3.4
0.00001

Number seperated by a return (\n).

When I make a file myself (such as above), the function works perfectly. However, when I save a column from excel as a text file (I have tried Text Tab Delimited and Text Unicode 16) it can only read the very first data point. Also, if I cut and paste into a TextEdit document I get the same result.

Anybody know how to make MS Excel export / save a file the way I need it? Or know of text editor that will might help me.

Strangly when I opened the Tab Delimited file in MS Word and clicked show hidden characters it displays returns at the end of everyline just as I want!? :confused:

I'm all ears!
 
boyfarrell said:
*snip*

When I make a file myself (such as above), the function works perfectly. However, when I save a column from excel as a text file (I have tried Text Tab Delimited and Text Unicode 16) it can only read the very first data point. Also, if I cut and paste into a TextEdit document I get the same result.

*snip*
Click to expand...

This probably means that there is something wrong with the way you're reading in the file :). Show some code and we'll see what's up.
 
Okay, strange things are happening!

There was little tiny bug in the code (Viro was right, AGAIN!) and I can now read in 'excel ripped' .txt files. If your interested in looking here is the code posted by Dave_Sinkula over at cprogramming.com (I have modified it little but don't worry, it works flawlessly!):
Code: 
#include <stdio.h>

int foo(const char *filename, double *array, int size)
{
   int count = 0;
   FILE *file = fopen(filename, "r");
   if ( file )
   {
      double *end = array + size;
      while ( fscanf(file, "%lf", array) == 1 )
      {
         if ( ++array >= end )
         {
            puts("too many inputs");
            break;
         }
         ++count;
      }
      fclose(file);
   }
   else
   {
      perror(filename);
   }
   return count;
}

int main()
{
   double array[2000];
   int i, count;
   count = foo("columndatafile.txt", array, sizeof array / sizeof *array);
   for ( i = 0; i < count; ++i )
   {
      printf("array[%i] = %f\n", (int)i, array[i]);
   }
   return 0;
}

But I still have a related problem. When I ask GNUplot to read the same file, it can't. Again the recurring theme of only loading the first element is appearing. From my GNUplot command line:

Code: 
Terminal type set to 'aqua'
gnuplot> cd '~/documents/xcode/moreio/build/debug'
gnuplot> plot 'Workbook1.txt'
Warning: empty x range [349.97:349.97], adjusting to [346.47:353.47]
Warning: empty y range [351.97:351.97], adjusting to [348.45:355.49]
gnuplot> set autoscale
gnuplot> replot
Warning: empty x range [349.97:349.97], adjusting to [346.47:353.47]
Warning: empty y range [351.97:351.97], adjusting to [348.45:355.49]
gnuplot> set xrange [0:900]
gnuplot> set yrange [0:900]
gnuplot> replot

Please find the attached AquaTerm output (you can see the 1 plotted point) and also the orignal text file holding the data.

Any ideas?
 

Attachments

In Excel, save the file Text (Windows). That makes things work fine. Don't know if this is applicable to the version of Excel you have, but on Excel 2004, that makes the file display correctly.
 
It sounds like a line-endings problem. Maybe Excel flies in the face of platform standards and uses Windows line-endings (\n\r)?
 
Yeah, that works perfectly when you save as Text (Windows).

I found away round the problem. In involved using fscanf with format that I wanted '%lf\n' and then opening a new file and writing the contense just read with the same formatting. Then GNUplot could read it fine too.

Yeah your right when you say MS are messing around with the end of line charaters.

Cheers, again guys.

I'm thinking of writing up a website with all the tips and tricks that I've learn from being on this forum. It would be very helpful for other science types strating out on a Mac.

By the way I now have a PowerBook!! No more PearPC for me!
 
So I have finally done what i sent out to do!

Here is a lovely absorption spectra, plotted from inside Xcode, using gnuplot_i and the fscanf function above. Why don't you set it as you desktop picture to remind yourself of all the blood, sweat and tears I have put you through over the last week or so! Cheers.

Daniel.
 

Attachments

If it weren't for that small hump on the left of the graph, I would have sworn it was a Gaussian plot :).
 
Strange you should say that, if you zoom into the small hump you'll see that it is a little guassian too. And in fact it's a better guassian than the bug hump - well, we hope, that's the bit we use in our modelling work!

These are electron resonances in little crystals (quantum dots) ~2nm wide. The bottom axis is the wavelength of light in nm, pumping the electrons. A photon of wavelength ~550nm vibrates the electrons in the material the most but it's actually the second excited state. The important bit for us is the 1st excite state: the little hump.
 
Errr... hang on, reverse the above according to:

wavelength prop 1/Energy

I'm so use to seeing spectra in terms of energy I didn't stop to think. We take the data in wavelength and then I normally convert to energy from there. Well you know what my next thing to do is now:

-(void) wave2energy(Spectra*);

!
 
You must log in or register to reply here.
Back
Top