Importing tab delimited data into OpenOffice.org

[Bill Campbell]

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
--
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