LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - Desktop (https://www.linuxquestions.org/questions/linux-desktop-74/)
-   -   Import fixed-width column text document in OpenOffice.org Calc (https://www.linuxquestions.org/questions/linux-desktop-74/import-fixed-width-column-text-document-in-openoffice-org-calc-639866/)

Marel 05-04-2008 04:06 PM

Import fixed-width column text document in OpenOffice.org Calc
 
I have a problem, our software produces reports that is formated in fixed width columns, it does not have an option to genereate csv or other delimited format. I didn't find a way to import such a document in OpenOffice.org Calc. MS Excel does this very well and we are using it at the moment, but we are considering to change to Linux. Is there any way?

rlhartmann 05-04-2008 06:05 PM

I don't know how to do it inside of OpenOffice, but here
is a simple shell script that will do it.
Quote:

#!/bin/bash

# Shell script to change fixed width file to
# pipe delimited file.

# data line is like: AAAbbbCCCCddddddddddEEEEEEEEEE

ORIGFILE="$1"
PIPEFILE="${ORIGFILE%%.*}-pipe.dat" # Create output file

# Create a set of separate files based on each fixed column
cut -c1-3 "$ORIGFILE" > F1.out.$$
cut -c4-6 "$ORIGFILE" > F2.out.$$
cut -c7-10 "$ORIGFILE" > F3.out.$$
cut -c11-20 "$ORIGFILE" > F4.out.$$
cut -c21- "$ORIGFILE" > F5.out.$$

# Put the separate files together using pipe to separate
# the pipe is a common delimiter because it seldom appears
# in ordinary data.
paste -d"|" F?.out.$$ > "$PIPEFILE"
rm -f F?.out.$$ # remove temp files
I called the script fixed2pipe.bash
Use chmod +x fixed2pipe.bash
using the original fixed data file called fixedwidth.dat,
the output file will be called fixedwitdh-pipe.dat,

Run the command like this
./fixed2pipe.bash fixedwidth.dat

The original file looks like:
AAAbbbCCCCddddddddddEEEEEEEEEE
aaaBBBccccDDDDDDDDDDEEEEEEEEEE

The new file has:
AAA|bbb|CCCC|dddddddddd|EEEEEEEEEE
aaa|BBB|cccc|DDDDDDDDDD|EEEEEEEEEE

which can load easily into OpenOffice.

ahz 05-04-2008 08:00 PM

Several ways
1. Rename document to .csv and open in OpenOffice.org (which will go to Calc)
2. Copy to clipboard and paste into Calc
3. Use Text to Columns

All three options give you the same dialog which allows fixed or delimited width import.

Marel 05-05-2008 03:23 PM

Number two showed to be most usefull. You just have to click up between columns to separate them in proper way. Thank you for this help!


All times are GMT -5. The time now is 02:03 PM.