The Origin Forum
File Exchange
Try Origin for Free
The Origin Forum
Home | Profile | Register | Active Topics | Members | Search | FAQ | Send File to Tech support
Username:
Password:
Save Password
Forgot your Password? | Admin Options

 All Forums
 Origin Forum
 Origin Forum
 Cannot import ASCII-file with date/time column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic Lock Topic Edit Topic Delete Topic New Topic Reply to Topic

womisa

Germany
Posts

Posted - 11/20/2003 :  06:18:40 AM  Show Profile  Edit Topic  Reply with Quote  View user's IP address  Delete Topic
Hi all,
I have tried to import an ASCII-File with a single date/time column like this :

Timestamp [YYMMDD HH24:MI:SS]
020612 13:14:54
020612 13:19:48
020612 13:25:04
020612 13:30:04
020612 13:35:04
020612 13:40:04
...
This produces the following error:
The file seems not to contain ASCII.

But importing the same timestamps with other columns (timestamp is e.g. the second column ), it works very fine.
Can anybody help me, please ?
Thanks
Achim

tib

Switzerland
105 Posts

Posted - 02/26/2004 :  4:13:45 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Hi Achim,
maybe in the meantime you found a solution?!
I tried to import your data and got the same error message.
However, in Import ASCII Options check the BOX "support numbers with leading zeros" (at least this check box exists in Origin7)
Then after import I get two columns, 1st the date, 2nd the time.
Hope that's what you were looking for?!
Greetings, Tilman.

Go to Top of Page

sammi.song

China
46 Posts

Posted - 02/26/2004 :  9:42:32 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Hi Achim,

You can import those data by this means that tib told in Origin7 and Origin6.1. If you are using Origin7.5, Import wizard is a handy tool I recommend.

Thanks,
Sammi
Go to Top of Page

mlamothe

Canada
1 Posts

Posted - 03/02/2004 :  10:57:47 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
I have a similar problem.
i have 2004 column 1, 03(month) in comumn 2, 02(day) in column 3, and 13:40:00 in column 4, and 12 columns of data.

Now i am doing long term comparison plots for a year and would like to combine these fist for columns to look like 2004 03 02 13:40:00 to use for my X axis.

Is it possible to combine these easily?

origin 7.5
Go to Top of Page

greg

USA
1379 Posts

Posted - 03/02/2004 :  3:04:04 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
As to the first topic and the fourth ...

Most Origin versions will (without any special ASCII Options) import
yyMMdd hh:mm:ss
data as two columns of Text. To plot as proper Dates, the first column should be converted to Date with the yyMMdd subformat. The second column should be converted to Time with the hh:mm:ss subformat. You can then use Set Column Values on Column 1 with:

col(1)+col(2)
or execute:
col(1)+=col(2)
in the Script Window.

In all these machinations, it will appear as if nothing changes, but make no mistake (tib especially) : just because text looks like a date, does not mean Origin will treat it like a date. Until the Date format is specified, text that looks like numbers will be treated as numbers, otherwise it may be treated as Text. If I change the subformat of column 1 to be yyMMdd hh:mm:ss, I should see that the time information is include in column 1.

User mlamothe has a bit more work to do:
Create a new column to the left of all the date information.
Make sure the Time column is set to Time format using hh:mm:ss subformat.
Set the new column format to Date (any subformat you like) and execute this script in the Script Window:

get col(2) -e end;
loop(ii,1,end) {
year = col(2)[ii];
month = col(3)[ii];
day = col(4)[ii];
time = col(5)[ii];
truedate = date($(month)/$(day)/$(year)) + time;
col(1)[ii] = truedate;
}

The numeric values of the year, month and day are used in the Date function to calculate the true julian date and then the time value is added.

Go to Top of Page

mlilly

USA
Posts

Posted - 10/07/2009 :  11:52:54 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Greg,

I found your note useful, but having a problem adapting it to a different example. A common form of climate data from National Databases is CSV, in the following format;

1946 1 1 2000
1946 1 1 2100
1946 1 1 2200
1946 1 1 2300
1946 1 2 0
1946 1 2 100
1946 1 2 200
1946 1 2 300

Year Month Day hour

I could convert the year, month, day to a date format, but could not include the time information. When I format the column to be Time (HHmm) the numbers do not work out.

A second application is doing analysis where we need everything to be in a single julian year (5/10/98 and 5/10/05 would have the same julian day). Ideas on how to approach this?

Thanks....Michael


quote:
Originally posted by greg

As to the first topic and the fourth ...

Most Origin versions will (without any special ASCII Options) import
yyMMdd hh:mm:ss
data as two columns of Text. To plot as proper Dates, the first column should be converted to Date with the yyMMdd subformat. The second column should be converted to Time with the hh:mm:ss subformat. You can then use Set Column Values on Column 1 with:

col(1)+col(2)
or execute:
col(1)+=col(2)
in the Script Window.

In all these machinations, it will appear as if nothing changes, but make no mistake (tib especially) : just because text looks like a date, does not mean Origin will treat it like a date. Until the Date format is specified, text that looks like numbers will be treated as numbers, otherwise it may be treated as Text. If I change the subformat of column 1 to be yyMMdd hh:mm:ss, I should see that the time information is include in column 1.

User mlamothe has a bit more work to do:
Create a new column to the left of all the date information.
Make sure the Time column is set to Time format using hh:mm:ss subformat.
Set the new column format to Date (any subformat you like) and execute this script in the Script Window:

get col(2) -e end;
loop(ii,1,end) {
year = col(2)[ii];
month = col(3)[ii];
day = col(4)[ii];
time = col(5)[ii];
truedate = date($(month)/$(day)/$(year)) + time;
col(1)[ii] = truedate;
}

The numeric values of the year, month and day are used in the Date function to calculate the true julian date and then the time value is added.



Go to Top of Page

mlilly

USA
Posts

Posted - 10/08/2009 :  10:18:20 AM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Hello Greg, others,

I did work out an approach to the first question that I asked, though not sure it is the best. Still working on the second problems. The conversion process to get a single Date formatted column is;

1 - Import data
2 - insert two columns after time data (D(Y1))
3 - right-click on new column next to time, and select
"Select Column Values" menu option
- insert into the equation box;

(col(D)/100)/24

- this will create a column of number converting time
data to decimal days

4 - double-click the header of the second inserted column
and change the Format to "Date" and the Display
Option to "m/d/yyyy hh:mm"

5 - Select Tools: Worksheet Script from pull-down menu
6 - insert the following script into the script box

get col(1) -e end;
loop(ii,1,end) {
year = col(1)[ii];
month = col(2)[ii];
day = col(3)[ii];
time = col(5)[ii];
truedate = date($(month)/$(day)/$(year)) + time;
col(6)[ii] = truedate;
}

- this converts the original 4 columns to a date field
with time included (ie. 1/1/1973 02:00)

Please let me know if there is a better or faster approach, will be converting some files with 30+ years of hourly data...Have a good day
Go to Top of Page

greg

USA
1379 Posts

Posted - 10/08/2009 :  12:09:54 PM  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
Your solution works fine, but as you suggest will be slow for large datasets. The old DATE function was not designed to work with vectors so the line-by-line conversion is needed.
The good news is that Origin 8 (not sure which Service Release .. I am looking at SR6) has overloaded the DATE function and also supports this syntax:
DATE(year,month,day)
where year, month and day can be vectors.

So given four columns : year, month, day, military time ...
the conversion could be accomplished with :

wks.addcol(Date); // Add a column named Date
col(Date) = date(col(1),col(2),col(3)); // Use new date function
col(Date) += col(4)/2400; // Add Time
wks.col$(colnum(Date)).Format = 4; // Format column as Date
wks.col$(colnum(Date)).SubFormat = 10; // Show as m/d/yyyy hh:mm

The vector calculations are much faster than a loop.
Go to Top of Page
  Previous Topic Topic Next Topic Lock Topic Edit Topic Delete Topic New Topic Reply to Topic
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
The Origin Forum © 2020 Originlab Corporation Go To Top Of Page
Snitz Forums 2000