Importing tab delimited data into OpenOffice.org

[Bill Campbell]

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