Keep a leading zero in Numbers.

I recommend modifying the input data before you import. The trick is to precede each EAN (SKU, UPC) with a single quote...

So, for instance, if you have a file with this contents:

Index SKU

1 12345

2 01234

3 00345

4 567-8765

5 011-99023

modify the file so each item in the SKU column has a single quote... like this:

Index SKU

1 '12345

2 '01234

3 '00345

4 '567-8765

5 '011-99023

If you have a file with many rows of data where inserting the single quote would be problematic, then I suggest using the text editor "COT Editor" (there are probably others) that have a multiline edit function

Using COTeditor, I can insert multiple cursors before the SKU (by holding the option key, then dragging as needed to insert the cursors):

then type the single quote once:

Then when you import this file Number will treat it the way you want.

Best,

Wayne

Below is a script that will import a CSV into a new Numbers document with all cells formatted as text. This will take care of the problem with leading zero. After import you can reformat the other columns as "automatic" and they will take on the correct properties (dates, actual numbers, etc.).

1. Copy/Paste into the Script Editor app
2. Hit the Run/Play button
3. Pick a CSV file and a delimiter (the comma)

It will create a new document for you with the CSV data in a table

If this works for you, it can be turned into a Shortcut app shortcut so it is easier to use.

I didn't write the whole thing. I don't recall where the code I re-used came from or I would give them credit.

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

If you already imported values that have dropped the missing zeros then a quick fix can be to apply the Numeral System data format. For example if you need 5 digits:

Then, if needed you can select the cells, command-c to copy, and Edit > Paste and Match format into cells formatted as Automatic or Text.

SG