How to Import Special with unusual and variable import format

[sandriftParticipant]

Hi,

I’m trying to avoid having to go through Excel to get my data into DataGraph by using the Import Special feature.  My import format is a bit strange, and I’m struggling to figure out how to properly parse the header, which is of variable length depending on the number of columns of data.  Here’s an example:

 

label 3
xaxis
RDH 1
RDH 2
379.9200134 0.8399235606 0.8130952716
381.8500061 0.8480494618 0.7846052051
383.7799988 0.8495468497 0.7871260047
385.7099915 0.8497001529 0.8015860319
387.6400146 0.8506407142 0.8034370542
389.5700073 0.8511590362 0.7977935076
391.5000000 0.8660500646 0.8060970306

The first line tells us that there are 3 labels.  The second through fourth lines are those labels. The fifth – EOF lines are the columnar data, tab-delimited.

Ideally, my import will result in the (in this case) three columns of data, with each labeled according to the text after the first line. (I don’t care about preserving the information in the first line.)

I have some flexibility in terms of adding text at the end of the label list, such that if having a line after “RDH 2” called “endlabel” would help, I can do that.

Thanks in advance,

Vicky

[dgteamModerator]

This is a tricky format.  It would be much easier if you had …

xaxis RDH1 RDH2
379.9200134 0.8399235606 0.8130952716
381.8500061 0.8480494618 0.7846052051
383.7799988 0.8495468497 0.7871260047
385.7099915 0.8497001529 0.8015860319
387.6400146 0.8506407142 0.8034370542
389.5700073 0.8511590362 0.7977935076
391.5000000 0.8660500646 0.8060970306

Notice I removed the spaces from the names of the column headers.  This way you could just drag and drop the file into DG directly, and the default parser would detect the header. No need to use Import Special.

The Import Special is really for the data that is always in the same format.   If you had the word “endlabel” added that would help because you could use that to trigger the beginning of the parser. But it sounds like your files can vary in the number of columns they have.

You could just drag and drop the file as is now and then you would need to rename the column headers. There are some other ways to manipulate this in DG that we could suggest but a first a couple of questions …

Do you have a variable number of columns in each file?  If so, the Import Special is probably not helpful as it is intended for creating import methods for files where the format is non-standard, but is predicable.

How many files are you dealing with?  If it is a few there are some manipulation in DG that could get you there, but would become tedious if you have a lot files.

Where does this file format come from?  Is this a standard format from an instrument for example with a particular file extension?  If so, we might add a method for this file type.

[sandriftParticipant]

Thank you for responding.  Yes, I agree that a different header format would be preferable – it’s just not that easy to do with the software I’m using prior to the DataGraph step; this file format is not standard to anyone else.

As you say, it sounds like Import Special may not be the right solution as my files will have varying numbers of columns on a regular basis. Analysis of this sort of data is my job, so you can assume I have innumerable files of this type.

I’d like to maintain the spaces in the label names because otherwise I’ll need to edit them later to put them back in.

Right now, I’m manually running the original tab-delimited file through Excel to copy and paste (transpose) the labels at the top of the columns, then copy/paste into DataGraph.  If there are similar machinations that could be done in DataGraph, that would at least save me from having to use Excel in the middle.

[dgteamModerator]

See if this helps.

Here is what I would do … importing the headings first and then the data.

Step 1:  Open the datafile in TextEdit and Copy the first three rows. (highlight and command-C)

Step 2: In DataGraph, select to Edit/Paste Special OR type shift-command-v.  

The Paste special options will appear.

Check Transpose & First row is the column name.

The interface should look like this where you see a preview of what you are importing when you select Paste … In this case, you are not importing data only the column headings.

Click Paste.

 

Step 3:  Use the gear menu to the right of your column headers and select Import File.

Step 4:  Highlight the header rows you don’t need and hit Delete.

Should look like this …

 

You could all the manipulation in DG but I think it is easiest to first copy and paste the header only.  Then you can keep the spaces in the names.

What do you think?  Can this work for you?

 

 

[sandriftParticipant]

Hi,

I’ll give this a try, but it looks like a variant of what I’m doing with/in Excel.

I’m working on writing a script for my software environment that will output a file with (only) the header nicely formatted; I should then be able to do a system call that attaches that file to one containing (only) the data.  Then I may be able to open directly in DataGraph.  I’ll reply with the final resolution.  Thanks again for your help.

[sandriftParticipant]

A-ha!

With the help of the first environment’s developer, I was able to find a way to export my data files to a CSV format (but tab-delimited) that DataGraph reads flawlessly.

I thank you very much for your time and effort, and particularly your rapid responses!  Very much appreciated.

[dgteamModerator]

Great — That sounds much easier for you!  Hope it is going well!!

[Author]

Posts