| Author |
 Topic  |
|
|
CRTBT-UBT
France
Posts |
 Posted - 02/22/2007 : 09:04:07 AM
|
Origin Version (Select Help-->About Origin): 7.5
Operating System: Win-XP
Hi,
I have data where one column is in the following date-time format:
yyyy-MM-dd HH:mm:ss .
If I just import it, and then transform it to date format using the approbriate custom date format, everything works fine.
Now I wanted to automise it using the Import wizard.
But setting it on time format (on the "data columns" sheet), I succeed getting a column in format DDD:HH:mm:ss, and using the date format I suceed getting a format yyyy-MM-dd, but the time is lost.
Is there a simple way to do what I want, or do I need to add a little script, and if yes how should it look like?
Thanks for help,
Johannes |
|
|
Mike Buess
USA
3037 Posts |
Posted - 02/22/2007 : 2:31:37 PM
|
Hi Johannes,
Probably easiest to import the column as Text+Numeric and correct with the Advanced Filter Options. First you'll need to add the Origin C function at the bottom to Codebuilder's workspace as described here.
Import Wizard settings:
1. Set Custom Date Format to <None> and click Apply on the Data Columns sheet.
2. Enter the command format_date_time_column(iCol, iSubFormat); on the Advanced Filter Options sheet. iCol is the LabTalk index of the date column and iSubFormat is the index of the date/time format on the Display list. Your format looks custom so iSubFormat is probably 19 or 20.
void format_date_time_column(int iCol, int iSubformat)
{
// iCol is LabTalk col #
iCol--; // convert to OC index
Worksheet wks = Project.ActiveLayer();
string str1,str2 = wks.Columns(iCol).GetName();
wks.InsertCol(iCol,"Date",str1);
wks.Columns(iCol).SetFormat(OKCOLTYPE_DATE);
wks.Columns(iCol).SetSubFormat(iSubformat);
Dataset ds0(wks,iCol),ds1(wks,iCol+1);
StringArray sa;
ds1.GetStringArray(sa);
ds0.PutStringArray(sa);
wks.DeleteCol(iCol+1); // delete original text column
wks.Columns(iCol).SetName(str2); // rename new date column
}
Mike Buess
Origin WebRing Member |
 |
|
|
CRTBT-UBT
France
Posts |
Posted - 02/23/2007 : 05:05:25 AM
|
Hi Mike,
first of all thanks for your help, this works fine, and by looking at your code I learnt a bit about Origin C too.
But if I understand correctly, this code relies on the fact that the custom date format is set correctly. Is there a way to make the code indepedent of that, for example by accepting as a second argument not the index of the subFormat, but for example a string with the appropriate format?
By the way, the format yyyy-MM-dd HH:mm:ss is the standard format used by python for datetime objects, so perhaps it's a good idea to include this format as standard format in future versions.
Johannes
|
|
|
|
Mike Buess
USA
3037 Posts |
Posted - 02/23/2007 : 11:51:08 AM
|
Hi Johannes,
This should work. Now iSubformat is any standard or custom format.void format_date_time_column(int iCol, int iSubformat)
{
// iCol is LabTalk col #
iCol--; // convert to OC index
Worksheet wks = Project.ActiveLayer();
string str1,str2 = wks.Columns(iCol).GetName();
wks.InsertCol(iCol,"Date",str1);
wks.Columns(iCol).SetType(wks.Columns(iCol+1).GetType());
wks.Columns(iCol).SetFormat(OKCOLTYPE_DATE);
wks.Columns(iCol).SetSubFormat(iSubformat);
Dataset ds0(wks,iCol),ds1(wks,iCol+1);
StringArray sa;
ds1.GetStringArray(sa);
//ds0.PutStringArray(sa);
ds0.SetSize(ds1.GetSize());
string FormatStr = "yyyy'-'MM'-'dd' 'hh':'mm':'ss";
double db;
for(int i=0;i<ds1.GetSize();i++)
{
if( str_to_date_custom(sa[i], FormatStr, &db) )
ds0[i] = db;
}
wks.DeleteCol(iCol+1); // delete original text column
wks.Columns(iCol).SetName(str2); // rename new date column
}
Mike Buess
Origin WebRing Member |
|
|
| |
Topic |
|
|
|
import wizard: import date time