Importing tab delimited data into OpenOffice.org

[Mike Reinehr]

Bill,

What version of OO.org are you using? I'm using 1.1.2.

To see what would happen, I created the following simple test file (named 
temp.txt) and did a File/Open(type=text/csv):

A1234 02/01/05 "This is a test" 101.99
A1235 02/02/05 "This is another test" 299.97
A1236 02/03/05 "Yet another test" 99.01

(The fields are separated by tabs.) The File/Open dialog automatically 
recognized the file as tab separated with double-quotes surrounding text (or 
defaulted). The first & third fields were imported as text, the second field 
as dates & the last field as numeric. (I used the letter A to precede the 
check numbers since you said alphanumeric. Without the letter A, I'm sure it 
would have been imported as numeric data.)

What is the format of your date field? In my experience, OO.org recognizes the 
usual formats.

cmr

On Monday 07 February 2005 03:27 pm, Bill Campbell wrote:
> On Mon, Feb 07, 2005, Mike Reinehr wrote:
> >On Sunday 06 February 2005 01:17 am, Bill Campbell wrote:
> >> On Sat, Feb 05, 2005, Shawn Tayler wrote:
> >> >On Sat, 05 Feb 2005 18:42:32 -0600 Alma J Wetzker <almaw at ieee.org>
> >> >
> >> >exclaimed:
> >> >> Bill Campbell wrote:
> >> >> > I've been beating my head against what should be a simple problem
> >> >> > using openoffice.org spread sheets.  I want to import tab separated
> >> >> > data into a spreadsheet with very simple data.  There are four
> >> >> > fields checkno, date, description, and amount where the first field
> >> >> > is alphanumeric, second a date, third alphanumeric, and fourth a
> >> >> > decimal. This probably has to be done with text data sources as the
> >> >> > data is to be merged into a spreadsheet without clobbering
> >> >> > everything that's there.
> >> >> >
> >> >> > My problem is getting OO-calc to get the correct data types.  I've
> >> >> > tried several permutation with and without quotes, the most recent
> >> >> > being to put double quotes around the text fields leaving the date
> >> >> > and amount fields unquoted.  I have set the text info in data
> >> >> > sources admin. to use tab delimiters with double quotes on text
> >> >> > data. Unfortunately this now imports the date field as text, and
> >> >> > the amount field as a date.  To make matters worse, it sometimes
> >> >> > works properly.
> >> >>
> >> >> Can you go back to comma delimiters instead of tabs?  Quote, comma
> >> >> delimited  data goes back as far as I can remember in computers and
> >> >> the import help in OO  calls it good.
> >> >
> >> >I've been going the other way and sed has helped alot with it:
> >> >
> >> >cat datafile | sed 's/\t/\,/g' > newdatafile
> >>
> >> Manipulating text data isn't my problem.  Figuring out what OpenOffice
> >> calc needs to force certain types of data conversion.  For some reason,
> >> it's taking numeric data and dates as strings.
> >>
> >> I think it would be easy enough to use DBASE files, but then I've
> >> forgotten the CPAN module that handles them.
> >>
> >> Bill
> >
> > Bill,
> >
> >Just a thought. Make sure that there are no spaces preceding the numeric
> > data when it is imported. OO.org seems to base it's conversion on that
> > first character.
>
> That isn't the problem.  I'm creating the output from a perl script with
> something like ``print join("\n", $checkno, $date, ...);'' where all the
> fields come out with no whitespace other than the tab delimiters.
>
> >Another thought is just to import the data into a blank spreadsheet and
> > then fiddle with the formating. Sometimes it takes a copy & paste to get
> > your formating correct.
>
> I finally broke down, and put the data into a mysql table which I then
> access using jdbc from data sources.  I had wanted to avoid this as the
> machine generating the tables is running SCO OpenServer, and has limited
> mysql capabilities (e.g. my perl DBI mysql module is failing, and I don't
> feel like mucking with it to get it working properly).
>
> Bill
> --
> INTERNET:   bill at Celestial.COM  Bill Campbell; Celestial Software LLC
> UUCP:               camco!bill  PO Box 820; 6641 E. Mercer Way
> FAX:            (206) 232-9186  Mercer Island, WA 98040-0820; (206)
> 236-1676 URL: http://www.celestial.com/
>
> ``The whole aim of practical politics is to keep the populace alarmed (and
> hence clamorous to be led to safety) by an endless series of hobgoblins.''
>    -- H.L. Mencken, 1923
> _______________________________________________
> Linux-users mailing list
> Linux-users at linux-sxs.org
> http://mail.linux-sxs.org/cgi-bin/mailman/listinfo/linux-users
>
> Need to chat further on this subject? Check out #linux-users on
> irc.linux-sxs.org !

-- 
Debian 'Sarge': Registered Linux User #241964
----
"More laws, less justice." -- Marcus Tullius Ciceroca, 42 BC
--------