OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

read .DAT files with OO calc
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
BasicGuy
General User
General User


Joined: 30 Jun 2009
Posts: 40

PostPosted: Mon Jul 30, 2012 10:55 am    Post subject: read .DAT files with OO calc Reply with quote

My datalogger creates .DAT files. Excel will read them directly, using the formatting wizard. OO.calc won't. It reads the file into Writer. If I copy and paste the data from Writer to Calc and use the Text to Columns function, it destroys dates.

For example, a data string pasted from Writer into Calc looks like
"2012-07-25 00:05:00",3575,75.7,133.3,134.6,127.8,85.8,0,0,0,0,0,0,0

Shows in the "Text to Columns" wizard as
2012-07-25 | 00:55:00 | 3573 | 75.7....... which is correct

But when I hit OK, the result looks like
07/26/2016 00:05:00 | 3573 | 75.7....... The date and time do not transfer correctly. The date is reversed and corrupted. Date formatting does nothing to help.

This looks like a bug to me.

If I change the extension from .DAT to .csv, OO.calc will read the data correctly using the formatting wizard.

I have lots of files I need to convert, so I would rather have a one/two step process than a 10 step one.

Is there any way to get Calc to read a .DAT file?

Thanks
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Tue Jul 31, 2012 8:28 am    Post subject: Re: read .DAT files with OO calc Reply with quote

BasicGuy wrote:
My datalogger creates .DAT files. Excel will read them directly, using the formatting wizard. OO.calc won't. It reads the file into Writer. If I copy and paste the data from Writer to Calc and use the Text to Columns function, it destroys dates.

For example, a data string pasted from Writer into Calc looks like
"2012-07-25 00:05:00",3575,75.7,133.3,134.6,127.8,85.8,0,0,0,0,0,0,0

Shows in the "Text to Columns" wizard as
2012-07-25 | 00:55:00 | 3573 | 75.7....... which is correct

But when I hit OK, the result looks like
07/26/2016 00:05:00 | 3573 | 75.7....... The date and time do not transfer correctly. The date is reversed and corrupted. Date formatting does nothing to help.

This looks like a bug to me.

If I change the extension from .DAT to .csv, OO.calc will read the data correctly using the formatting wizard.

I have lots of files I need to convert, so I would rather have a one/two step process than a 10 step one.

Is there any way to get Calc to read a .DAT file?

Thanks


This sounds specific enough that we really need an example of the problem if possible. You can upload a file to someplace such as MediaFire for other forum readers to examine.
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Jul 31, 2012 12:31 pm    Post subject: Re: read .DAT files with OO calc Reply with quote

[quote="jrkrideau"]
BasicGuy wrote:
My datalogger creates .DAT files. Excel will read them directly, using the formatting
This sounds specific enough that we really need an example of the problem if possible. You can upload a file to someplace such as MediaFire for other forum readers to examine.


As a matter of course OOo 1.5, LibO 3.3, 3.5 and AOO 3.4 import the data perfectly well with some English locale, comma delimiter, " as text delimiter, quoted text as number=false, detect special numbers=true
In OOo 1.5 and 2 you have to specify the column types explicitly as YMD date and English.

No, Excel does not keep any "formatting" unless you tell it to import the date as a (wrong) text value. As a matter of course you can do the same in any version of Calc.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
BasicGuy
General User
General User


Joined: 30 Jun 2009
Posts: 40

PostPosted: Fri Aug 03, 2012 12:32 pm    Post subject: file uploaded Reply with quote

I have uploaded a file to MediaFire. How do I direct someone to it?
Back to top
View user's profile Send private message
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 400
Location: victoria

PostPosted: Fri Aug 03, 2012 2:06 pm    Post subject: Reply with quote

Hi BasicGuy
Go back into your media file account
right click on the file
click 'copy link'
this will put the link in your clipboard and you can now paste it to the forum message form
and to finish it up highlight it and click the url button above the form
cheers
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
BasicGuy
General User
General User


Joined: 30 Jun 2009
Posts: 40

PostPosted: Fri Aug 03, 2012 5:21 pm    Post subject: Reply with quote

http://www.mediafire.com/download.php?2565b6bld9h77rw
Back to top
View user's profile Send private message
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 400
Location: victoria

PostPosted: Fri Aug 03, 2012 6:19 pm    Post subject: Reply with quote

I had no problems with your file -
Open in notepad
Ctrl + A
Ctrl + C
Open blank spreadsheet
Shift + Ctrl + V
Unformatted text - OK
In this form -
- tick separated by comma
- at the bottom table click the heading 'Standard' (for the date column only)
- go to drop down box @ column type and change to "DATE(YMD)"
Click 'enter'
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
BasicGuy
General User
General User


Joined: 30 Jun 2009
Posts: 40

PostPosted: Fri Aug 03, 2012 7:12 pm    Post subject: solved first problem Reply with quote

Ozzie, That got it Smile. I had been using the Format/date commands from the menu bar, which don't work. Using the commands inside the wizard as you pointed out does work.

That solves the first problem.

With so many files to convert, however, this approach is not efficient.

The real question is why can't OO.calc read a .DAT file directly. I think it can on a PC, but not on a Mac. Why is that?

I have a PC as the workstation receiving the data, but use a Mac for the heavy lifting.
Back to top
View user's profile Send private message
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 400
Location: victoria

PostPosted: Fri Aug 03, 2012 7:59 pm    Post subject: Reply with quote

I've no experience with a 'mac' at all so, no answers for you there at all.
I opened the dat file directly from windows explorer to calc with a right click (and browsing to find calc program)
I was led to the table as above and using the same directions it opened looking even better than my other post (none of the ugly quote marks)
As per the second line you could make calc the default program for your dat files but is this really what you want?
I think you may reached the limit of my abilities Crying or Very sad
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Aug 04, 2012 2:19 am    Post subject: Reply with quote

These are database data in plain text. A spreadsheet is neither database nor spreadsheet.
If you only want to view, edit and save such files, then you should use a database program or a specialized text editor such as http://csved.sjfrancke.nl/
Of course you can use such files as raw material for spreadsheet calculations but then your spreadsheet, the imported values, the applied formulas and charts should be in the middle of interest rather than the format of imported data.

Since the dates in the first column are coded in ISO format (Y-M-D H:M:S) they import perfectly regardless of locale settings.
This is how they look like on my system with a German default locale:
Code:
02.08.12 12:00
02.08.12 12:05
02.08.12 12:10
02.08.12 12:15
02.08.12 12:20
02.08.12 12:25

[...]

These data have been imported correctly. I can switch the number format and the locale at will without modifying a single value.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
BasicGuy
General User
General User


Joined: 30 Jun 2009
Posts: 40

PostPosted: Sat Aug 04, 2012 1:35 pm    Post subject: significant dfferences in OO.calc for PC vs Mac Reply with quote

As I understand it, on a PC you can isolate OO.calc as a stand alone app. Click on it and it opens OO.calc. You can't do that on a MAC. The OO coders have made the Mac version a suite only. You have to go through the suite to get to a spreadsheet. Bad. I don't use any of the other parts, so this is a real bother.

So, when you click on a .dat file and select "open with", the only application you can select is OO, not OO.calc. Then, when you double click on the .dat file, OO opens the file in Writer - you can't get to OO.calc. You have to cut and paste into a spreadsheet, then use the text to columns, which will incorrectly read the date unless you use the format inside the wizard (I didn't know it was there).

I think this is the root of the problem.

The coders have decided to make the Mac version less useful than the PC version. Sad
Back to top
View user's profile Send private message
Ranleri
General User
General User


Joined: 23 Aug 2012
Posts: 5

PostPosted: Thu Aug 23, 2012 9:49 pm    Post subject: Reply with quote

ozzie wrote:
Hi BasicGuy led tubes
Go back into your media file account
right click on the file
click 'copy link'
this will put the link in your clipboard and you can now paste it to the forum message form
and to finish it up highlight it and click the url button above the form
cheers


Thanks for explaining the procedure in simple manner
Back to top
View user's profile Send private message
BasicGuy
General User
General User


Joined: 30 Jun 2009
Posts: 40

PostPosted: Tue Oct 29, 2013 7:46 am    Post subject: Best solution so far Reply with quote

It turns out that you can change the .dat extrension into .csv and the file will import into LO.

I wrote an Automator script on the Mac to convert all .dat to .csv in a folder.

This works, but I would rather LO import without all the fuss.
Back to top
View user's profile Send private message
keme
Moderator
Moderator


Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

PostPosted: Tue Oct 29, 2013 3:21 pm    Post subject: Re: Best solution so far Reply with quote

BasicGuy wrote:
It turns out that you can change the .dat extrension into .csv and the file will import into LO.

I wrote an Automator script on the Mac to convert all .dat to .csv in a folder.

This works, but I would rather LO import without all the fuss.

You could register your folder as a text database, to have the data available. However, the file has multiline headings and somewhat messy fieldnames, so I am not sure that you will achieve anything useful by that.
What kind of conversion do you need to perform on your files?
BasicGuy wrote:
...
As I understand it, on a PC you can isolate OO.calc as a stand alone app. Click on it and it opens OO.calc. You can't do that on a MAC. The OO coders have made the Mac version a suite only.
...
I suspect that this is due to the way Apple designed the application interface. The OpenOffice suite (also in other incarnations, such as LO and NeoOffice) is monolithic on every OS/HW platform, but on most platforms you can provide alternate entry points by a wrapper executable (such as the swriter.exe, scalc.exe, etc. in Windows) or by call parameters (soffice -writer ...). Alas, OSX' application folders are rather convoluted, and defaults for file actions do not provide for additional parameters, so neither wrapper nor link with parameters is an option.
IOW, the OS itself limits flexibility. The developers did not change anything much for OSX, but I believe they would need to make major changes to the suite if they were to make the applications separately available.

Then again, I am not your certified mac guru, so I may well be missing something.
Back to top
View user's profile Send private message
BasicGuy
General User
General User


Joined: 30 Jun 2009
Posts: 40

PostPosted: Tue Oct 29, 2013 6:47 pm    Post subject: stand alone Calc file on a Mac Reply with quote

The work around is to simply save a blank Calc file and put it in the dock. Then you click on the dock icon and Calc opens.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group