Import column-arranged plain text into spreadsheet or table

[sadhu]

I have an old, old text table that I'm trying to put into a spreadsheet.

Code: Select all

Description of food                         Fat  Food Energy Carbohydrate Protein Cholesterol Weight Saturated Fat
                                          (Grams) (calories)   (Grams)   (Grams) (Milligrams) (Grams)  (Grams)
 
1000 ISLAND, SALAD DRSNG,LOCAL1 TBSP         2        25           2         0        2       15       0.2
1000 ISLAND, SALAD DRSNG,REGLR1 TBSP         6        60           2         0        4       16        1
100% NATURAL CEREAL           1 OZ           6       135          18         3        0      28.35     4.1
40% BRAN FLAKES, KELLOGG'S    1 OZ           1        90          22         4        0      28.35     0.1
40% BRAN FLAKES, POST         1 OZ           0        90          22         3        0      28.35     0.1
ALFALFA SEEDS, SPROUTED, RAW  1 CUP          0        10           1         1        0       33        0
ALL-BRAN CEREAL               1 OZ           1        70          21         4        0      28.35     0.1
ALMONDS, SLIVERED             1 CUP         70       795          28        27        0       135      6.7

I could of course, open the file in Libreoffice Writer and manually insert commas and quotation marks, and also try to delete the spaces... Then I could import it into LO Calc or writer as a delimited spreadsheet or table.

But I recall from the bad old days that Lotus123 and maybe Excel had some means of delineating cells based on the column number. In the text sample, for example, everything in column 1-30 belong in the first column of the table. Everything in columns 31-41 goes into table cell 2, etc. I haven't been able to figure out how to do it in LO Calc or Writer.

Is there a way?

Thanks
-Sadhu

[Cosmo.]

Simply copy the complete Writer table into the clipboard and paste it in Calc. Works perfectly here (obviously not your table). A matter of seconds.

[sadhu]

Simply copy the complete Writer table into the clipboard and paste it in Calc. Works perfectly here (obviously not your table). A matter of seconds.

what writer table??? It's a plain text file, a copy of a few lines of which are included above. There ain't no 'writer table'. Just text, organized in plain text columns, no delimiters, just column positions, separated by spaces. As in the sample.

When I do what you suggest, every row ends up in cell '1, e.g.,

Code: Select all

APPLE PIE                     1 PIECE       18       405          60         3        0       158      4.6

I need to put "APPLE PIE", "1 PIECE", "18", "405", etc., into separate cells on the same row.

-sadhu

[Cosmo.]

Sorry, I misunderstood.

So if this is a simple txt-file (I hope I've got it this time) and you select in Calc to open this file you should automatically see a settings dialogue for importing (with a preview at the bottom).

[aes2011]

To successfully "import" stuff into Calc there has to be consistency wrt "Separator options". In other words, spaces, or tabs, or commas, etc.

[sadhu]

Like I said earlier, back in the bad old days--pre-internet, when it was the IBM PC fighting it out with CP/M and Microsoft had just come out with MS/DOS--spreadsheets such as Lotus123, Borland's whatever-it-was, all had facilities to import a straight text file that was organized as in the samples above. You have to remember that databases such as DBase had fixed-width text fields. Database output was streamed to a text printer that organized columns by adding spaces. If one captured the output into a text file, the resulting file could be imported into the sptreadsheet by telling the spreadsheet where to break the columns. As I recall, there was some kind of code such as >>> meaning data and # meaning cell break.

Therefore one would enter something like

Code: Select all

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>#>>>>>>>>>>#>>>>>>>>#>>>>>>>>>#>>>>>>>>>>>#>>>>>>>>>#>>>>>>>#>>>>>>>>>#>>>>>>>>>#
APPLE PIE                     1 PIECE       18       405          60         3        0       158      4.6

to indicate column/cell breaks. I don't think this facility exists any more. Some say it can be done in Excel, but I don't have that.

I guess the question reveals my true age...

-Thanks
-