How to extract/import data from large .csv files

I have dozens of data files I collect in my lab that are 2-dimensional wavelength (256) vs. time (1000) traces. The files exported by the instrument are large and not in an easily digestible format. I can manually extract and transpose them in excel myself, but this is time consuming and a pain when there are multiple files to work up. Is there an easy way for me to code a macro up in Igor to import specific columns, transpose them, and rename them into individual waves? 

I have attached an example file of the instrument output and the format I usually convert them to before copy/paste importing them into Igor. 

Raw data from instrument Restructured data for import

You can almost certainly write Igor code to do the conversion for you. Whether it is "easy" or not is another issue and depends on your experience writing Igor code.

The LoadWave operation can be used to load data from text files using various formats, including CSV. However your raw data file is not a CSV file in spirt, though it may be one in practice. You probably want to skip the first 25 or so lines that appear to be contain header information, not numeric data. You can use the /L flag to instruct LoadWave to start loading from a line other than the first line of the file. If your data always starts at line 25 (using zero-based line numbers) then you could hard code that value. Otherwise you would need to figure out which line to start at before you call LoadWave.

You will also need to figure out how many lines to load because there is also some information after the array of data in the raw file.

If you load the data into a 2D wave in Igor you can transpose it using MatrixTranspose.

For more information, you can execute:

DisplayHelpTopic "Loading Delimited Text Files"

Note that in some cases it's easier to have Igor treat the text as "general" text instead of delimited text. For the details, execute:

DisplayHelpTopic "Loading General Text Files"
DisplayHelpTopic "Comparison of General Text, Fixed Field and Delimited Text"

You can use the Save operation to save the data back to a text file, or wfprintf if you need more flexibility.


Some ways I would approach the problem.

First is to break apart the data and import things as pieces. The first column contains what looks like a time value. The first row contains the wavelength information and the bulk is the data of interest.  If the wavelength values do not change I would import it once.

I would then start with the loading go a single wave with wavelength and transpose to get a single wave.

Load the timestamp data.

Load the optical data as a matrix and transpose.

Some other things to think about depending on you needs is the ability to embed wave scaling.  The time intervals are not quite constant as are the wavelength intervals.  If you can accommodate some slight error then you can embed the time data and wavelength data with set scale.  That reduces the number of waves you need to keep track of.

The first function loads the wavelength and sets the directory for the rest of the loading.

The second function does the loading for each csv file.

The third function will load the entire directory of csv files.

You may want to tweak the naming convention to suit your needs.

Note: I have not embedded the scaling of the time and wavelengths.

Here is some code as a starting point.


function loadWavelength(string filename)
    newPath/O ProData //set folder with files
    LoadWave/J/M/D/A=wave/K=0/L={0,26,1,1,0} /P=Prodata filename
    wave theWave = $(stringFromList(0,S_wavenames)) //loads the wavelength
    matrixtranspose thewave
    rename thewave, wavelength

function load_single_Data(string filename)

    LoadWave/J/M/D/A=wave/K=0/L={0,27,0,1,0} /P=Prodata filename
    wave theWave = $(stringFromList(0,S_wavenames)) //loads the wavelength
    matrixtranspose thewave
    string newName //build up code to give the imported wave an new name that is meaninful
    newname=removeEnding(filename,".csv") //get rid of the csv extension
    newname =cleanupName(newname,0) //follow strict rules in renaming
    rename thewave, $newname
    //load time stamps
    LoadWave/J/D/A=wave/K=0/L={0,27,0,0,1} filename
    wave timeWave = $(stringFromList(0,S_wavenames)) //loads the wavelength
    rename timeWave,$(newName+"_time")

function load_all_the_data()

    string thelist,therun
    variable index,maxindex
    thelist=indexedfile(ProData,-1,".csv") //get a list of all csv files
    for (index=0;index<maxindex;index+=1)
        therun= stringfromList(index,thelist)