Importing CSV into OO/LO

[Volker Kuhlmann]

How can I import the following CSV file into LibreOffice/OpenOffice
3.3.1?

20110601T00:01:16,13.56

The first column is a time stamp in ISO format. It is only read as
general pile of characters.

And am I doing something wrong, or is LO/OO really too stupid to
recognise the input as 2 columns when I set a separator character of ","
instead of "fixed width"? With a comma-separator all input including and
after the comma drops into the void (i.e. disappear from the input
wizzard's screen).

Thanks,

Volker

[Neil Harsant]

Post by Volker Kuhlmann
How can I import the following CSV file into LibreOffice/OpenOffice
3.3.1?
20110601T00:01:16,13.56
The first column is a time stamp in ISO format. It is only read as
general pile of characters.
And am I doing something wrong, or is LO/OO really too stupid to
recognise the input as 2 columns when I set a separator character of ","
instead of "fixed width"? With a comma-separator all input including and
after the comma drops into the void (i.e. disappear from the input
wizzard's screen).
Thanks,
Volker

Tried your input by opening as a file CSV.csv on my OpenSuSE/64 11.4 with
LibreOffice 3.3.1 , OOO330m19 (Build:8), tag libreoffice-3.3.1.2
Selected comma as only separator and got:-

0110601T00:01:16 13.56
so seems to work for me.




_______________________________________________
NZLUG mailing list ***@linux.net.nz
http://www.linux.net.nz/cgi-bin/mailman/listinfo/nzlug

[Reed Wade]

On Fri, Jun 17, 2011 at 9:22 PM, Neil Harsant

Post by Neil Harsant

Post by Volker Kuhlmann
How can I import the following CSV file into LibreOffice/OpenOffice
3.3.1?
   20110601T00:01:16,13.56
The first column is a time stamp in ISO format. It is only read as
general pile of characters.
And am I doing something wrong, or is LO/OO really too stupid to
recognise the input as 2 columns when I set a separator character of ","
instead of "fixed width"? With a comma-separator all input including and
after the comma drops into the void (i.e. disappear from the input
wizzard's screen).
Thanks,
Volker

Tried your input by opening as a file CSV.csv on my OpenSuSE/64 11.4 with
LibreOffice 3.3.1 , OOO330m19 (Build:8), tag libreoffice-3.3.1.2
Selected comma as only separator and got:-
0110601T00:01:16        13.56
so seems to work for me.

Be sure to set the 'Detect special numbers' on the import dialogue.

This format doesn't work for me (doesn't set the cell type to time)--
20110601T00:01:16,13.56

but this does--
2011-01-01 11:40,"cake",23

LibreOffice 3.3.2
OOO330m19 (Build:202)

I'm not seeing the items getting lost problem you mention.

-reed

_______________________________________________
NZLUG mailing list ***@linux.net.nz
http://www.linux.net.nz/cgi-bin/mailman/listinfo/nzlug

[Bruce Kingsbury]

You might need to 'sed' the time into a more recognisable format?

Post by Reed Wade
On Fri, Jun 17, 2011 at 9:22 PM, Neil Harsant

Post by Neil Harsant

Post by Volker Kuhlmann
How can I import the following CSV file into LibreOffice/OpenOffice
3.3.1?
20110601T00:01:16,13.56
The first column is a time stamp in ISO format. It is only read as
general pile of characters.
And am I doing something wrong, or is LO/OO really too stupid to
recognise the input as 2 columns when I set a separator character of ","
instead of "fixed width"? With a comma-separator all input including and
after the comma drops into the void (i.e. disappear from the input
wizzard's screen).
Thanks,
Volker

Tried your input by opening as a file CSV.csv on my OpenSuSE/64 11.4 with
LibreOffice 3.3.1 , OOO330m19 (Build:8), tag libreoffice-3.3.1.2
Selected comma as only separator and got:-
0110601T00:01:16 13.56
so seems to work for me.

Be sure to set the 'Detect special numbers' on the import dialogue.
This format doesn't work for me (doesn't set the cell type to time)--
20110601T00:01:16,13.56
but this does--
2011-01-01 11:40,"cake",23
LibreOffice 3.3.2
OOO330m19 (Build:202)
I'm not seeing the items getting lost problem you mention.
-reed
_______________________________________________
http://www.linux.net.nz/cgi-bin/mailman/listinfo/nzlug

_______________________________________________
NZLUG mailing list ***@linux.net.nz
http://www.linux.net.nz/cgi-bin/mailman/listinfo/nzlug

[Reed Wade]

Post by Reed Wade
but this does--
2011-01-01 11:40,"cake",23

and so does--

2011-01-01T11:40,"cake",23

so, seems like it is ISO_8601'ish but not allowing the shorter format

-reed

_______________________________________________
NZLUG mailing list ***@linux.net.nz
http://www.linux.net.nz/cgi-bin/mailman/listinfo/nzlug

[Volker Kuhlmann]

Post by Reed Wade
so, seems like it is ISO_8601'ish but not allowing the shorter format

Yes, I had found too after some testing that it needs the dashes, and
maybe it works better with a space instead of a "T".

I made a script to massage time stamps into shape:
http://volker.top.geek.nz/soft/script/stamp-iso2oo


The problem with the input field separator is still there.
It defaults to the last-used setting. The whole thing used to work for
me.
And it's a somewhat obscure bug:
Set your separator to fixed width, import anything, quit.
Open another .csv file, the default is now fixed width.
Change to separated by comma, and scores of columns disappear into
oblivion.
Only on the display though, the import works correctly. And the display
problem does not exist when starting with comma-separator. Just don't
switch to fixed width to try something out, because going back to
comma-separator breaks the display again.

libreoffice-3.3.1.2, kernel 2.6.37.6, currently OSS radeon driver (if
that is relevant), openSUSE 11.4.

Can someone verify this? If it's not just me I'll enter a bug report.

Thanks everyone,

Volker

[Bruce Clement]

Post by Volker Kuhlmann
How can I import the following CSV file into LibreOffice/OpenOffice
3.3.1?
20110601T00:01:16,13.56
The first column is a time stamp in ISO format. It is only read as
general pile of characters.

It defaults for me to recognizing the comma as a field separator, but leaves
the timestamp as a text field and doesn't translate it into anything useful.

I tried making T a second separator & setting the first column to date YMD
then reimporting and it at least recognised that, but how to make it import
the time part eludes me.

LibreOffice 3.3.2
OOO330m19 (Build:202)
tag libreoffice-3.3.2.2, Debian package 1:3.3.2-2+b1
--
Bruce Clement

Home: http://www.clement.co.nz/
Twitter: http://twitter.com/Bruce_Clement
Directory: http://www.searchme.co.nz/

"Before attempting to create something new, it is vital to have a good
appreciation of everything that already exists in this field." Mikhail
Kalashnikov
_______________________________________________
NZLUG mailing list ***@linux.net.nz
http://www.linux.net.nz/cgi-bin/mailman/listinfo/nzlug

[John]

On Fri, 17 Jun 2011 21:06:10 +1200

Post by Volker Kuhlmann
How can I import the following CSV file into LibreOffice/OpenOffice
3.3.1?
20110601T00:01:16,13.56
The first column is a time stamp in ISO format. It is only read as
general pile of characters.
And am I doing something wrong, or is LO/OO really too stupid to
recognise the input as 2 columns when I set a separator character of ","
instead of "fixed width"? With a comma-separator all input including and
after the comma drops into the void (i.e. disappear from the input
wizzard's screen).

Probably a bit late :-)

I've often seen this behaviour using comma-separation and found that using
a different separator character usually solved the problem.

In my case a | (pipe) seemed to be best. Stolen, purloined, borrowed ...
from an Informix readme years ago.

Useful?

John

_______________________________________________
NZLUG mailing list ***@linux.net.nz
http://www.linux.net.nz/cgi-bin/mailman/listinfo/nzlug