Importing tab delimited data into OpenOffice.org

[Mike Reinehr]

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.

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 generally do fixed format importing and have had little difficulty with 
OO.org.

cmr

> --
> 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/
>
> There are three kinds of men. The ones that learn by reading. The few who
> learn by observation.  The rest of them have to pee on the electric fence
> for themselves. -- Will Rogers
> _______________________________________________
> 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
--------