Time & Date Import as a single string

Share icon

Hi, I have looked through the various other posts but haven't found a solution for this particular issue. Probably due to lack of Igor coding knowledge at the moment.

I'm trying to import a csv file through the load waves menu option. I understand about tweaking the data such that the date is formatted correctly but IP doesn't allow for 'time date' only 'date time'.

I can use 'space' as a deliminator to separate the time and date into two waves, doing so requires you to turn 'read wave names' off as there is no 'date' column name.

The data is in the format 'hh:mm:ss dd-mmm-yyyy' the mmm is abbreviated alphabetic i.e. 'Sep'. Ideally I want to read this as a single wave, instead of reading it as two for every dataset then combining it back into one.

I have attached a sample of the dataset (which is too long to open in excel to format the data first).

I tried to follow this post: https://www.wavemetrics.com/code-snippet/create-datetime-wave-text-waves and load both as text waves then create a date&time wave but it just comes up with command errors 'expected wave name....' and I don't know enough to make it work yet. My date format is different also, but I don't get that far.

Any help appreciated

 cut-Archive.csv (849 bytes)

There is nothing built-in in Igor that can interpret "JAN", "FEB", etc, as months so you need to write code to do that.

Here is a solution. If you are running Igor Pro 6, you need to replace "int" and "double" with "Variable".

 

Function MonthStrToMonth(monthStr)
    String monthStr
   
    int which = WhichListItem(monthStr, "JAN;FEB;MAR;APR;MAY;JUN;JUL;AUG;SEP;OCT;NOV;DEC;", ";", 0, 0)
    int month
    if (which < 0)
        month = 1           // Should not happen
    else
        month = which + 1
    endif
    return month
End

Function/WAVE TextAsTextWaveToDateTimeWave(dateAsTextWave)
    WAVE/T dateAsTextWave       // Assumed in DD-MMM-YYYY format where MMM is alphabetic (e.g. JAN)
 
    Variable numPoints = numpnts(dateAsTextWave)
    Make/FREE/D/N=(numPoints) dateTimeWave
    WAVE wOut = dateTimeWave
    SetScale d, 0, 0, "dat", wOut
   
    Variable i
    for(i=0; i<numPoints; i+=1)
        String dateAsText = dateAsTextWave[i]
        Variable day, month, year
        String monthStr
        sscanf dateAsText, "%d-%[^-]-%d", day, monthStr, year   // "[^-]" means "any character except '-'"
        month = MonthStrToMonth(monthStr)
        double dt = Date2Secs(year, month, day)
        wOut[i] = dt    
    endfor  
   
    return wOut
End

Menu "Load Waves"
    "Load Special File...", LoadSpecialFile("", "") // Display Open File dialog and load one file
End

Function LoadSpecialFile(fileName, pathName)
    String pathName             // Name of symbolic path or "" for dialog
    String fileName                 // Name of data file or "" for dialog
   
    Variable refNum

    // First get a valid reference to a file.
    if ((strlen(pathName)==0) || (strlen(fileName)==0))
        // Display dialog looking for file.
        Open/D/R/P=$pathName/M="Select special data file" refNum as fileName
        fileName = S_fileName       // S_fileName is set by Open/D
        if (strlen(fileName) == 0)  // User cancelled?
            return -1
        endif
    endif
   
    String columnInfoStr = "N=DateAndTime,F=7,T=4;N=DateAsText,F=-2;N=Depth;N=Temperature;N=OneMinuteLightLevel;N=SmoothedLightLevel;N=Events;"
   
    LoadWave /P=$pathName /J /A /L={0, 1, 0, 0, 7} /V={", ", "", 0, 0} /B=columnInfoStr /O /Q fileName
    if (V_flag < 7)
        Print "User Cancelled or Error Occurred"
        return -1;
    endif
   
    Wave DateAndTime, Depth, Temperature, OneMinuteLightLevel, SmoothedLightLevel, Events
    WAVE/T DateAsText
    WAVE DateData = TextAsTextWaveToDateTimeWave(DateAsText)    // Free wave
    DateAndTime += DateData               // Add date to time
   
    String title = ParseFilePath(3, S_fileName, ":", 0, 0)      // Remove extension from file name
    Edit /W=(9,47,674,304) DateAndTime, Depth, Temperature, OneMinuteLightLevel, SmoothedLightLevel, Events
    ModifyTable format(DateAndTime)=8, width(DateAndTime)=120
End

 

November 8, 2020 at 10:31 am - Permalink

Here is an edit of that function to convert your date format to an Igor format.

function ConvertTextToDateTime(string TimeAndDateAsText)
    string ListOfMonths="Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec"
    string strMonth=""
    variable year, month, day, hour, minute, second
    variable dt, timeOfDay
    sscanf TimeAndDateAsText, "%d:%d:%d %d-%[A-Za-z]-%d", hour, minute, second, day, strMonth, year
    month=WhichListItem(strMonth, ListOfMonths)+1
    dt = date2secs(year, month, day)
    timeOfDay = 3600*hour + 60*minute + second
    return dt + timeOfDay
end

To use this, you need to create a double precision wave with the same size as your date/time textwave, then execute

DateTimeWave=ConvertTextToDateTime(textWave)

EDIT: oops, too slow!

November 8, 2020 at 10:38 am - Permalink

Thanks Howard for your input. I can read in the data now and it looks great however the month is not reading correctly.

The text wave 'DateAsText' reads the date correctly, beginning 08/05/2016 and incrementing but the 'DateAndTime' wave begins 08/01/2016 and the month doesn't increment at all.
The day and year along with time import perfectly. The data becomes multiple overlapping traces within the single month of January.

I've attached a longer segment of the archive for your reference.

Another question is regarding quote 'There is nothing built-in in Igor that can interpret "JAN", "FEB", etc, as months so you need to write code to do that'. This confused me as when I was using the load waves/load waves/Tweaks function on IP8 it allows me to do 'Other' date format and customize the month as 'Abbreviated alphabetic'. This reads the date wave and gives me the correct igor format e.g. 08/05/2016 output as a numeric wave.
2-month-Archive.csv (14.45 MB)

November 8, 2020 at 10:18 pm - Permalink

Quote:
The data becomes multiple overlapping traces within the single month of January.

Yes. The problem is that I forgot that WhichListItem is case-sensitive by default. I have changed it in the procedure above.

Quote:
This confused me as when I was using the load waves/load waves/Tweaks function on IP8 it allows me to do 'Other' date format and customize the month as 'Abbreviated alphabetic'.

It is actually I who was confused. I forgot that LoadWave supported alphabetic dates even though I wrote the code (about 25 years ago :)

Here is a revised procedure that uses this feature of LoadWave:

Menu "Load Waves"
    "Load Special File...", LoadSpecialFile("", "") // Display Open File dialog and load one file
End

Function LoadSpecialFile(fileName, pathName)
    String pathName             // Name of symbolic path or "" for dialog
    String fileName                 // Name of data file or "" for dialog
   
    Variable refNum

    // First get a valid reference to a file.
    if ((strlen(pathName)==0) || (strlen(fileName)==0))
        // Display dialog looking for file.
        Open/D/R/P=$pathName/M="Select special data file" refNum as fileName
        fileName = S_fileName       // S_fileName is set by Open/D
        if (strlen(fileName) == 0)  // User cancelled?
            return -1
        endif
    endif
   
    String columnInfoStr = "N=DateAndTime,F=7,T=4;N=DateOnly,F=6,T=4;N=Depth;N=Temperature;N=OneMinuteLightLevel;N=SmoothedLightLevel;N=Events;"
   
    LoadWave /P=$pathName /J /A /L={0, 1, 0, 0, 7} /V={", ", "", 0, 0} /B=columnInfoStr /R={English,2,3,2,2,"DayOfMonth-Month-Year",40} /O /Q fileName
    if (V_flag < 7)
        Print "User Cancelled or Error Occurred"
        return -1;
    endif
   
    Wave DateAndTime, DateOnly, Depth, Temperature, OneMinuteLightLevel, SmoothedLightLevel, Events
    DateAndTime += DateOnly               // Add date to time
    KillWaves/Z DateOnly
   
    String title = ParseFilePath(3, S_fileName, ":", 0, 0)      // Remove extension from file name
    Edit /W=(9,47,674,304) DateAndTime, Depth, Temperature, OneMinuteLightLevel, SmoothedLightLevel, Events
    ModifyTable format(DateAndTime)=8, width(DateAndTime)=120
End

 

November 9, 2020 at 04:44 am - Permalink