Save sheet as a .prn, or a space delimited file

jjmancini wrote:
I need to be able to save my numbers file as a prn file, which I also believe to be a space delimited file. Does anyone know how i can do this?

FilExt gives this description for the PRN file type:

The PRN file type is primarily associated with 'Printer Text File'. When you print to a file the program outputs whatever it would have sent to the printer you have attached to your computer to a file instead. The idea is that later you might want to send this file to a similar printer on a different computer. So, the main use for a .PRN file is to copy it to a printer in order to get the output. You can open a .PRN file in any text editor but what you'll likely see is your text surrounded by a whole bunch of "junk" which represents all the various printer codes necessary to set margins, special spacing, graphics, etc., etc

On a Windows machine, this is accomplished through the Print dialogue by checking the "Print to file" checkbox. The closest equivalent on the Mac is to "Print" to a PDF file. This is also done through the Print dialogue, using the PDF button (actually a popup menu) at the lower left and choosing Save as PDF.

Depending on the purpose to which the file is to be put, this may be a satisfactory alternative.

What will be done with the PRN file after it is saved?

Regards,
Barry

There is apparently another PRN file type that is formatted text. I don't know enough about it to give any details. Numbers does not export to that file type. I imagine a CSV is the closest thing to it. Without knowing the details I can't say how to convert a CSV to a PRN. Probably something for an Applescript.

jjmancini wrote:
All it really is, is a space delimited file. Thus it translates column distance as spaces.

If the resulting file is to contain a single space separating data in column A from that in column B, yes, it can be done using Numbers and a Text Editor application.

In Numbers, got Share > Export... and choose CSV and the appropriate text encoding system, then click Export.

Open the resulting .csv file in a text editor, use Find/Replace to replace all the commas with spaces, then save. The result will be a text file with one space separating individual pieces of data, assuming all cells in a row are filled, and a return separating one row from the next.


If, on the other hand, the resulting file is to "translate column distance" in the sense of placing data of varying lengths into straight columns on a printed page by padding the data with spaces, that's a different question with a more complicated solution.

Regards,
Barry

JJ,

This isn't so much a recommendation as a demonstration that you have all the tools you need in iWork. It's not clean, but you may find the workflow acceptable if you the output is that important to you.

Let's say that you have a table of data like this:



That table can be converted to a table of text strings of equal length, padded with spaces, as shown here:


Here's the expression if you want to copy it for use in your own example:

=LEFT(MainTable :: A1&" ", 12)

There are 12 spaces between the quotes - not showing here because of the discussions tool filtering them out, but you can see them in the screen shot of the content of A1.

I exported the Numbers document as CSV, then changed the extension from CSV to TXT so that I could open the file in Pages. Here's what it looks like in Pages, invisibles not showing:



I used the Find and Replace tool to rid the text block of the commas, leaving this (invisibles showing):



Of course I had to change to one of the monospaced fonts to get the alignment. This whole thing takes just a couple of minutes if you have a workflow defined and a pre-built conversion table.

Regards,

Jerry

Message was edited by: Jerrold Green1

I hate to apply different formatting tasks when the machine may do the job for me.

Here is a dedicated AppleScript :

--

--[SCRIPT columnsBySpaces]
(*
Enregistrer le script en tant que Script : columnsBySpaces.scpt
déplacer le fichier ainsi créé dans le dossier
<VolumeDeDémarrage>:Users:<votreCompte>:Library:Scripts:Applications:Numbers:
Il vous faudra peut-être créer le dossier Numbers et peut-être même le dossier Applications.
Dans la ligne d'en-têtes la plus haute, indiquer les largeurs de colonnes (en nombre caractères)
En l'absence de valeurs numérique, le script utilisera une largeur de 12 caractères.
Sélectionner les cellules à exporter
menu Scripts > Numbers > columnsBySpaces
Un fichier nommé "columnsBySpaces_aaaammjj-hhmmss.txt" sera créé sur le bureau.
Vous pourrez l'imprimer en utilisant une police de chasse constante.
--=====
L'aide du Finder explique:
L'Utilitaire AppleScript permet d'activer le Menu des scripts :
Ouvrez l'Utilitaire AppleScript situé dans le dossier Applications/AppleScript.
Cochez la case "Afficher le menu des scripts dans la barre de menus".
--=====
Save the script as a Script: columnsBySpaces.scpt
Move the newly created file into the folder:
<startup Volume>:Users:<yourAccount>:Library:Scripts:Applications:Numbers:
Maybe you would have to create the folder Numbers and even the folder Applications by yourself.
In the higher header row, enter the wanted widths of columns (characters numbers).
If there is no numerical value, the script use 12 characters by column.
menu Scripts > Numbers > columnsBySpaces
A file named "columnsBySpaces_aaaammjj-hhmmss.txt" will be created on the Desktop.
You will be able to print it using a monospaced font.
--=====
The Finder's Help explains:
To make the Script menu appear:
Open the AppleScript utility located in Applications/AppleScript.
Select the "Show Script Menu in menu bar" checkbox.
Save this script as a … Script in the "Folder Actions Scripts" folder
<startupVolume>:Library:Scripts:Folder Action Scripts:
--=====
Yvan KOENIG (VALLAURIS, France)
2010/04/29
*)
--=====
on run
 set |largeurParDéfaut| to 12
 my activateGUIscripting()
 set {dName, sName, tName, rName, rowNum1, colNum1, rowNum2, colNum2} to my getSelParams()
 tell application "Numbers" to tell document dName to tell sheet sName to tell table tName
 set lesLargeurs to {}
 tell row 1
 repeat with c from colNum1 to colNum2
 try
 set largeur to (value of cell c) as integer
 if largeur = 0 then set largeur to |largeurParDéfaut|
 on error
 set largeur to |largeurParDéfaut|
 end try
 copy largeur to end of lesLargeurs
 end repeat
 end tell -- row 1
 
 set lesLignes to {}
 repeat with r from rowNum1 to rowNum2
 tell row r
 set uneLigne to {}
 repeat with c from colNum1 to colNum2
 set maybe to (value of cell c) as text
 set largeur to item c of lesLargeurs
 if (count of maybe) < largeur then
 repeat until (count of maybe) = largeur
 set maybe to maybe & space
 end repeat -- until…
 end if
 copy maybe to end of uneLigne
 end repeat -- c
 copy my recolle(uneLigne, "") to end of lesLignes
 end tell
 end repeat -- r
 set lesLignes to my recolle(lesLignes, return)
 end tell
 
 set nomDuRapport to "columnsBySpaces" & (do shell script "date " & quote & "+_%Y%m%d-%H%M%S" & quote) & ".txt"
 set p2d to path to desktop
 set p2r to (p2d as Unicode text) & nomDuRapport
 tell application "System Events"
 if exists (file p2r) then delete (file p2r)
 make new file at end of p2d with properties {name:nomDuRapport}
 end tell
 write lesLignes to (p2r as alias)
end run
--=====
(*
set { dName, sName, tName, rname, rowNum1, colNum1, rowNum2, colNum2} to my getSelParams()
*)
on getSelParams()
 local r_Name, t_Name, s_Name, d_Name, col_Num1, row_Num1, col_Num2, row_Num2
 set {d_Name, s_Name, t_Name, r_Name} to my getSelection()
 
 if r_Name is missing value then
 if my parleAnglais() then
 error "No selected cells"
 else
 error "Il n'y a pas de cellule sélectionnée !"
 end if
 end if
 
 set two_Names to my decoupe(r_Name, ":")
 set {row_Num1, col_Num1} to my decipher(item 1 of two_Names, d_Name, s_Name, t_Name)
 if item 2 of two_Names = item 1 of two_Names then
 set {row_Num2, col_Num2} to {row_Num1, col_Num1}
 else
 set {row_Num2, col_Num2} to my decipher(item 2 of two_Names, d_Name, s_Name, t_Name)
 end if
 return {d_Name, s_Name, t_Name, r_Name, row_Num1, col_Num1, row_Num2, col_Num2}
end getSelParams
--=====
(*
set {rowNumber, columnNumber} to my decipher(cellRef,docName,sheetName,tableName)
apply to named row or named column !
*)
on decipher(n, d, s, t)
 tell application "Numbers" to tell document d to tell sheet s to tell table t to return {address of row of cell n, address of column of cell n}
end decipher
--=====
(*
set { d_Name, s_Name, t_Name, r_Name} to my getSelection()
*)
on getSelection()
 local _, theRange, theTable, theSheet, theDoc, errMsg, errNum
 
 tell application "Numbers" to tell document 1
 repeat with i from 1 to the count of sheets
 tell sheet i
 set x to the count of tables
 if x > 0 then
 repeat with y from 1 to x
 try
 (selection range of table y) as text
 on error errMsg number errNum
 set {_, theRange, _, theTable, _, theSheet, _, theDoc} to my decoupe(errMsg, quote)
 return {theDoc, theSheet, theTable, theRange}
 end try
 end repeat -- y
 end if -- x>0
 end tell -- sheet
 end repeat -- i
 end tell -- document
 return {missing value, missing value, missing value, missing value}
end getSelection
--=====
on parleAnglais()
 local z
 try
 tell application "Numbers" to set z to localized string "Cancel"
 on error
 set z to "Cancel"
 end try
 return (z is not "Annuler")
end parleAnglais
--=====
on decoupe(t, d)
 local l
 set AppleScript's text item delimiters to d
 set l to text items of t
 set AppleScript's text item delimiters to ""
 return l
end decoupe
--=====
on recolle(l, d)
 local t
 set AppleScript's text item delimiters to d
 set t to l as text
 set AppleScript's text item delimiters to ""
 return t
end recolle
--=====
(*
replaces every occurences of d1 by d2 in the text t
*)
on remplace(t, d1, d2)
 local l
 set AppleScript's text item delimiters to d1
 set l to text items of t
 set AppleScript's text item delimiters to d2
 set t to l as text
 set AppleScript's text item delimiters to ""
 return t
end remplace
--=====
(*
removes every occurences of d in text t
*)
on supprime(t, d)
 local l
 set AppleScript's text item delimiters to d
 set l to text items of t
 set AppleScript's text item delimiters to ""
 return (l as text)
end supprime
--=====
on activateGUIscripting()
 tell application "System Events"
 if not (UI elements enabled) then set (UI elements enabled) to true (* to be sure than GUI scripting will be active *)
 end tell
end activateGUIscripting
--=====
(*
==== Uses GUIscripting ==== 
*)
(*
This handler may be used to 'type' text, invisible characters if the third parameter is an empty string. 
It may be used to 'type' keyboard shortcuts if the third parameter describe the required modifier keys. 
*)
on shortcut(a, t, d)
 local k
 tell application a to activate
 tell application "System Events" to tell application process a
 set frontmost to true
 try
 t * 1
 if d is "" then
 key code t
 else if d is "c" then
 key code t using {command down}
 else if d is "a" then
 key code t using {option down}
 else if d is "k" then
 key code t using {control down}
 else if d is "s" then
 key code t using {shift down}
 else if d is in {"ac", "ca"} then
 key code t using {command down, option down}
 else if d is in {"as", "sa"} then
 key code t using {shift down, option down}
 else if d is in {"sc", "cs"} then
 key code t using {command down, shift down}
 else if d is in {"kc", "ck"} then
 key code t using {command down, control down}
 else if d is in {"ks", "sk"} then
 key code t using {shift down, control down}
 else if (d contains "c") and (d contains "s") and d contains "k" then
 key code t using {command down, shift down, control down}
 else if (d contains "c") and (d contains "s") and d contains "a" then
 key code t using {command down, shift down, option down}
 end if
 on error
 repeat with k in t
 if d is "" then
 keystroke (k as text)
 else if d is "c" then
 keystroke (k as text) using {command down}
 else if d is "a" then
 keystroke k using {option down}
 else if d is "k" then
 keystroke (k as text) using {control down}
 else if d is "s" then
 keystroke k using {shift down}
 else if d is in {"ac", "ca"} then
 keystroke (k as text) using {command down, option down}
 else if d is in {"as", "sa"} then
 keystroke (k as text) using {shift down, option down}
 else if d is in {"sc", "cs"} then
 keystroke (k as text) using {command down, shift down}
 else if d is in {"kc", "ck"} then
 keystroke (k as text) using {command down, control down}
 else if d is in {"ks", "sk"} then
 keystroke (k as text) using {shift down, control down}
 else if (d contains "c") and (d contains "s") and d contains "k" then
 keystroke (k as text) using {command down, shift down, control down}
 else if (d contains "c") and (d contains "s") and d contains "a" then
 keystroke (k as text) using {command down, shift down, option down}
 end if
 end repeat
 end try
 end tell
end shortcut
--=====
--[/SCRIPT]
--

Of course, as I am lazy, I defined only four columns but you may work with more if you wish.

Yvan KOENIG (VALLAURIS, France) jeudi 29 avril 2010 16:16:05

Because your table is very uniform to start with, you may be able to accomplish what you need with Text Wrangler. The workflow would be as follows:

Select the table
Copy
Paste into Text Wrangler
Text/DeTab
Save

This would not work quite as well if your table wasn't as uniform because numbers will align to the left like text.

Got the file. I have three suggestions.

1) For the formatting of the "price" column to come through as 0.000 versus 0.0, it must be formatted as text just like the "change" column. I don't know if AppleScript is able to tell the complete formatting of a cell. A cell formatted as a number with four decimal places ends up having the extraneous zeros removed so 0.0000 turns into 0.0. As text, all decimal places are retained. Plus you will need to do this for #3 to work as I wrote it.

2) In Yvans script, nine lines after Run, replace +*set largeur to (value of cell c) as integer*+ with +*set largeur to (width of column c) / 5 as integer*+ . This sets the column width but it does not take font size into account. Dividing by five looked good to me but you may want to adjust the divisor.

3) In Yvan's script, right after +*set maybe to (value of cell c) as text*+ add the line +*if maybe = "0.0" then set maybe to ""*+ . Blank cells get turned into 0.0 by the script and I don't know of a robust way to tell a blank cell 0.0 from a real 0.0. This gets rid of the extraneous 0.0's but if you have a real 0.0 that you want to keep, it will be gone too.

This is not a perfect solution and is really designed for your particular document.

Badunit wrote:
Got the file. I have three suggestions.

1) For the formatting of the "price" column to come through as 0.000 versus 0.0, it must be formatted as text just like the "change" column. I don't know if AppleScript is able to tell the complete formatting of a cell. A cell formatted as a number with four decimal places ends up having the extraneous zeros removed so 0.0000 turns into 0.0. As text, all decimal places are retained. Plus you will need to do this for #3 to work as I wrote it.

I may adjust the column width triggering "Resize columns to fit contents" but I didn't do that because from my point of view, there is no interest to open the spaced_file in Numbers :
It will remove the spaces in front of numbers.

2) In Yvans script, nine lines after Run, replace +*set largeur to (value of cell c) as integer*+ with +*set largeur to (width of column c) / 5 as integer*+ . This sets the column width but it does not take font size into account. Dividing by five looked good to me but you may want to adjust the divisor.

3) In Yvan's script, right after +*set maybe to (value of cell c) as text*+ add the line +*if maybe = "0.0" then set maybe to ""*+ . Blank cells get turned into 0.0 by the script and I don't know of a robust way to tell a blank cell 0.0 from a real 0.0. This gets rid of the extraneous 0.0's but if you have a real 0.0 that you want to keep, it will be gone too.

This is not a perfect solution and is really designed for your particular document.

What I saw in the posted screenshot is the result of the default widths setting : 12 characters per column.
I took care to give us the ability to define more efficient width.

The cells in the first column contain 'long strings'. Setting the width of this column to 40 would be required but the OP didn't apply the designed scheme.

I may imagine an alternate scheme which will count the characters in every columns to determine the widths to use.

Yvan KOENIG (VALLAURIS, France) jeudi 6 mai 2010 09:15:47

I rebuilt the OP's spreadsheet.
Given the behaviour of some cells I concluded that cells are formatted as text.
I added one row as top with these formulas:
A1 : =2+LEN(A31)
B1 : =4+LEN(B12)
C1 : =4+LEN(C12)
D1 : =2+LEN(D12)
E1 : =2+LEN(E12)



Using the script as it is explained in the given instructions, I got :



To get rid of the 0 returned by empty cells, I added one instruction

--

repeat with c from colNum1 to colNum2
 set maybe to (value of cell c) as text
 if maybe is in {"", "0.0", "0,0"} then set maybe to "" -- ADDED
 set largeur to item c of lesLargeurs
--

After that, I got :

Of course, it would be useful to remove the first row with character counts.



Yvan KOENIG (VALLAURIS, France) jeudi 6 mai 2010 12:53:22

Yvan,

What was the issue you saw with the change to use the column width to set the number of needed spaces?

Do you know of a way to retrieve the displayed contents of a cell versus the value of the cell? I see no way to avoid 0.0's in blank cells (other than removing all 0.0's) or how to keep trailing zeros in decimal numbers. I have not looked into custom formats but I imagine they are a problem as well.

If you look at the nbchars in cells of column A, you see:
12
3
43
4
0
17
0
31
7
0
27
27
27
23
21
0
0
23
7
0
27
27
27
23
21
23
21
0
0
34
0
7
0
14
14
14
27
27
27
23
21
0
0

So in the spaced document, the 1st char of column B will be at this variable 'distance' of the left edge. When the original cell contains more than 12 chars, the extraneous ones aren't removed.

The result is :


When the nbchars is used, I get :



For the problem of empty cell I have an idea:
don't grab / insert values directly.
grab all the values in a row
scan these values starting from the right.
skip every zeroes until a non-zeroes value
then treat groups of three zeroes as groups of three blank cells.
It will remain only single or double zeroes which will be displayed as zeroes

Another scheme:
grab the status of each cell.
if it's a formula display the value
If it's a constant whose value is 0.0 (0,0 in French) treat it as an empty string.
To apply the second scheme, I must do some tests upon custom formats.

Yvan KOENIG (VALLAURIS, France) jeudi 6 mai 2010 18:21:11

Yvan,

Your script sets a number of spaces for a column then later replaces the leftmost spaces with characters from the cells. It uses the first row of the table, in which the user enters the desired number of spaces for each column, as the way to set the number of spaces for each column. I was suggesting that rather than hardcoding these numbers into the table, the width of the column (not the number of characters in the cell) be used. For example, the width of the first column was something like 216 (it was 2xx, I can't remember exactly from last night). Divided by 5 that becomes 43 which was close to your suggested 36.

But, as this is your script not mine, it is only a suggestion.