stop Numbers converting text to numbers when importing into text columns

I feel your pain. You are far from the first to complain about how Numbers is too "smart" when importing, to the point of being dumb. It was that way in Numbers '09 also, just different. Here is a script that might help. It creates a new Numbers document, formats all the cells in the table to text, and imports all CSV fields as text.

1. Open the Script Editor app
2. Copy/paste the script from below into Script Editor
3. Use the Play button to run the script.

If you like it,

1. Save it with a short descriptive name. The default location is your user Library/Scripts folder. It needs to go there.
2. Go to Script Editor->Preferences
3. Check the box to show scripts menu in menu bar
4. A scroll icon will appear in the upper right side of the menu at the top of the screen, near bluetooth, wifi, etc.
5. Click on it and you should see your script listed. You can run it from there.

-- Import CSV File into Numbers

-- This script reads a user-selected CSV file, creates a new Numbers document, and
-- imports the contents of the CSV file into the Numbers document with all cells formatted as text.

-- The usual/simple way of separating CSV text into "items" by setting the text item delimiter to a comma
-- doesn't work in all cases because CSV files can contain commas within quoted text.  To get around this,
-- the algorithm first converts the file to a "Unicode character 0000" delimited string as it interprets the file and determines
-- which commas are within quoted text and which are the actual separators. 
-- This means that "Unicode character 0000" is not an unallowable character in the CSV file.
-- Carriage return is not an allowed character in a CSV field, even if surrounded by quotes.
-- The user can choose the CSV delimiter, though that code is commented out at this time

-- Written by Badunit,  reusing some code from others
-- Last updated Feb 4, 2022

set newTID to character id 0 --Unicode character 0000
set oldTID to AppleScript's text item delimiters


set csvFile to (choose file of type "CSV")
set csvText to read csvFile

-- If want user to select comma or semicolon as delimiter, swap the commenting of the next two lines 
set csvDelimiter to ","
--set csvDelimiter to (choose from list {",", ";"} with title "Delimiter" with prompt "Choose a delimiter" default items ",") as text

-- Convert the CSV text to delimited string with the specified delimiter. 
-- First turn it into an array of characters for easy mainipulation.
-- Pad the end with a null for the "quote test" portion of the algorithm.
-- Before converting back to a string, set the Applescript text delimiters to null so that no delimiters are put between characters

set theCharacters to characters of csvText
set end of theCharacters to ""

set i to 1
set openquote to false

repeat while i ≤ length of csvText
	if item i of theCharacters = "\"" then
		if openquote = true then
			if item (i + 1) of theCharacters = "\"" then
				set i to i + 1
			else
				set openquote to false
			end if
		else
			set openquote to true
			
		end if
		
		set item i of theCharacters to ""
	else if item i of theCharacters = csvDelimiter and openquote = false then
		set item i of theCharacters to newTID
	end if
	set i to i + 1
end repeat

set AppleScript's text item delimiters to ""
set newText to theCharacters as string

-- Count paragraphs (rows in spreadsheet) and text items (columns in spreadsheet)
-- Use maximum number of text items to set number of columns, rather than add extra columns on the fly.

set AppleScript's text item delimiters to newTID

set rowCount to (count paragraphs in newText)
set maxColumns to 0
repeat with x from 1 to rowCount
	set columnCount to count text items in paragraph x of newText
	if columnCount > maxColumns then set maxColumns to columnCount
end repeat

--Create new Numbers document with correct sized table
--Set format of all cells to Text

tell application "Numbers"
	activate
	make new document at front
	delay 1
	tell document 1
		tell sheet 1
			delete every table
			make new table at front with properties ¬
				{row count:rowCount, column count:maxColumns, header column count:0, header row count:0}
			set format of every cell of table 1 to text
		end tell
	end tell
end tell

--Loop through paragraphs and delimited items of newText and place them in the correponding 
-- cells in the newly created table

repeat with nextRow from 1 to rowCount
	set columnCount to (count text items in paragraph nextRow of newText)
	repeat with nextColumn from 1 to columnCount
		set nextValue to text item nextColumn of paragraph nextRow of newText
		tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
			set nextCell to cell nextColumn of row nextRow
			set value of nextCell to nextValue
		end tell
	end repeat
end repeat

-- Clean up. Set the delimiters back to what they were at the start

set AppleScript's text item delimiters to oldTID

-- Import CSV File into Numbers

-- This script reads a user-selected CSV file, creates a new Numbers document, and
-- imports the contents of the CSV file into the Numbers document with all cells formatted as text.

-- The usual/simple way of separating CSV text into "items" by setting the text item delimiter to a comma or semicolon or
-- tab doesn't work in all cases because CSV files can contain the delimiter within quoted text.  To get around this,
-- the algorithm first converts the file to a "Unicode character 0000" delimited string as it interprets the file and determines
-- which "delimiters" are within quoted text and which are the actual delimiters. 
-- This means that "Unicode character 0000" is not an unallowable character in the CSV file.
-- Carriage return is a not an allowed character in a CSV field, even if surrounded by quotes.

-- Written by Badunit, reusing some code from others
-- Feb 4, 2022

set newTID to character id 0 --Unicode character 0000
set oldTID to AppleScript's text item delimiters


set csvFile to {choose file of type {"TXT", "CSV"}}
set csvText to read csvFile

set csvDelimiter to (choose from list {",", ";", "Tab"} with title "Delimiter" with prompt "Choose a delimiter" default items ",") as text
if csvDelimiter = "Tab" then set csvDelimiter to "	"

-- Convert the CSV text to delimited string with the specified delimiter. 
-- First turn it into an array of characters for easy mainipulation.
-- Pad the end with a null for the "quote test" portion of the algorithm.
-- Before converting back to a string, set the Applescript text delimiters to null so that no delimiters are put between characters

set theCharacters to characters of csvText
set end of theCharacters to ""

set i to 1
set openquote to false

repeat while i ≤ length of csvText
	if item i of theCharacters = "\"" then
		if openquote = true then
			if item (i + 1) of theCharacters = "\"" then
				set i to i + 1
			else
				set openquote to false
			end if
		else
			set openquote to true
			
		end if
		
		set item i of theCharacters to ""
	else if item i of theCharacters = csvDelimiter and openquote = false then
		set item i of theCharacters to newTID
	end if
	set i to i + 1
end repeat

set AppleScript's text item delimiters to ""
set newText to theCharacters as string

-- Count paragraphs (rows in spreadsheet) and text items (columns in spreadsheet)
-- Use maximum number of text items to set number of columns, rather than add extra columns on the fly.

set AppleScript's text item delimiters to newTID

set rowCount to (count paragraphs in newText)
set maxColumns to 0
repeat with x from 1 to rowCount
	set columnCount to count text items in paragraph x of newText
	if columnCount > maxColumns then set maxColumns to columnCount
end repeat

--Create new Numbers document with correct sized table
--Set format of all cells to Text

tell application "Numbers"
	activate
	make new document at front
	delay 1
	tell document 1
		tell sheet 1
			delete every table
			make new table at front with properties ¬
				{row count:rowCount, column count:maxColumns, header column count:0, header row count:0}
			set format of every cell of table 1 to text
		end tell
	end tell
end tell

--Loop through paragraphs and delimited items of newText and place them in the correponding 
-- cells in the newly created table

repeat with nextRow from 1 to rowCount
	set columnCount to (count text items in paragraph nextRow of newText)
	repeat with nextColumn from 1 to columnCount
		set nextValue to text item nextColumn of paragraph nextRow of newText
		tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
			set nextCell to cell nextColumn of row nextRow
			set value of nextCell to nextValue
		end tell
	end repeat
end repeat

-- Clean up. Set the delimiters back to what they were at the start

set AppleScript's text item delimiters to oldTID

Hi Robert,

I typed a subset of your data into TextEdit with tab separators and saved as CSV.

I opened the CSV in Pages with Menu > View > Show Invisibles.

Menu > Edit Find > Find... (command F)

Type \t into the Find field. (\t is the code for the tab character).

Type \t| into the Replace field (| is the "pipe character that does not appear in your CSV).

Click on the right arrow (bottom right) to initiate the Find & Replace dialogue.

Click on Replace All

Dismiss the Find & Replace dialogue. Select All and Copy.

Go to Numbers and create a table that is formatted as Text. No Header Row or Header Column for now.

Click once in the top left cell and Paste.

Duplicate the table.

Column A is correct (no preceding "|")

Formula in B1: RIGHT(Table 1::B1,LEN(Table 1::B1)−1)

Fill down and fill right

The leading zeroes are preserved.

Regards,

Ian.

Here's another take on the insert | character, import into Numbers, then remove | character, using the Shortcuts app.

1 - Open the tabbed data exported by the database in TextEdit or whatever.

2 - Open the Shortcuts app and drag in Replace Text and Copy to Clipboard actions.

3 - Copy-paste an (invisible) tab from the data in TextEdit and paste after 'Replace' and again after 'with'. Add | after the tab after 'with'.

4 - Select all data open in TextEdit, command-c, run the Shortcut.

5 - Click in Numbers, and command-v to paste.

6 - Select cells in Numbers, type command-f, type | in the Search box and leave the Replace box blank. Click 'Replace All'.

Result:

SG

RobertCailliau wrote:

I suspect that what happens is that Numbers does the interpretation before the paste, why else would it go wrong when I ask to match style?

Yes, that is my guess too. A few versions back the behavior when pasting CSV text changed. It seems the idea was to make Numbers smarter and more "helpful." But it in some cases it became too smart and started guessing wrong, e.g. assuming that what looks like a number must be a number.

Do you have any control over what the database exports? If you can get it to use comma as delimiter and surround all values by " " then you can try this workaround.

After pasting, choose click the Adjust Settings box that pops up, go to Advanced Settings and set Text Qualifier to None, giving you something like this.

Then set up new columns with formulas to strip the surrounding quotes.

Not ideal, but it might get the job done.

Another option, if you have to do this often, might be an AppleScript that reads the clipboard and sets the values in Numbers cells.

SG

RobertCailliau wrote:

I could first paste, then sort the table by country code, and then put the zeroes back as per your recipe.

But it is yet more complex: the records also contain telephone numbers, some of which start with one or two zeroes.

You can restore the original lengths, with a different length for each country, with a formula like this. A pain. We shouldn't have to do it. But the native parser is of course much faster than a script, and it then becomes a question of fixing just the columns that have leading 0s. One formula per column. Not as laborious as sorting and such.

=NUMTOBASE(C2,10,XLOOKUP(B2,{"FR","CH","AT"},{5,4,4},"Not found"))&""

Or for regions that use the comma as a decimal separator:

=NUMTOBASE(C2;10;XLOOKUP(B2;{"FR"."CH"."AT"};{5.4.4};"Not found"))&""

The &"" at the end coerces to Text.

A similar approach (I think) can be used for phone numbers with leading zeros though it may not work if they're formatted with spaces in the middle.

Or something like this for phone numbers (not perfect, but close).

SG

To restore the leading zeros:

Select the cells and set the Data Format to Numeral System Base 10 with 5 Places like this:

Then you can set the Data Format to Text.

SG

RobertCailliau wrote:

(2) Open Office misbehaves in exactly the same way.

:-(

You're probably already aware that by default Excel also treats text that looks like Numbers as numbers.

But when you split into columns using the Text to Columns wizard you can define the data format for each column. Here I selected the third column and chose Text.

That preserves the text in Excel. But when you copy-paste from Excel into Numbers the designation as Text is not respected.

SG

Well, you did after all mention dropping leading 0s in your post.😀 Postal code systems vary widely throughout the world. I haven't seen a system where the codes have varying lengths, and didn't realize the two codes were from different countries, so I assumed the goal was to have five characters throughout. Sorry about that.

As illustrated, you can easily set the length of any particular code via the Numeral System Date Format. If you do not want to add leading zeros to Meyrin (and to other codes for the same country) then you can just set Places to 4 for those. So the data you bring in does not result in a "useless" table. Rather it is a table that needs a little further processing as described.

Keep in mind we are your fellow users here, trying to help others use the software as it is now. If the current behavior does not meet your needs, then you should consider giving feedback directly to Apple via Numbers > Provide Numbers Feedback in the menu.

SG