You can make a difference in the Apple Support Community!
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
I use square for my business and started using SKU #s for it. My SKUs start with a zero, so every time I export my spreadsheets from Square to Numbers, the zero disappears. I know how to change the format to manually add them back, but I don’t want to keep having to do that every time. How do I change my Numbers settings to allow the zero to just automatically stay when I export a new spreadsheet?
According to Square’s Import Items Online support document, they recommend exporting using an Excel (.xlsx) file format. This makes sense since the data is formatted to text thus eliminating the need to deal with the issue of spreadsheet tools removing leading zeros on import. This is fine for exporting the Item Library from the Square Dashboard, but for any other product that might include SKUs, such as an Item Sales Report, the only option available is to export in a CSV file format.
In the same support document mentioned above, Square describes steps to import CSV data in Microsoft Excel and Google Spreadsheets (Sheets) so that data such as SKU numbers are formatted to text on import. Excel offers more control for data formatting on import, while Sheets takes a global all-or-nothing approach. Not mentioned is Apple’s Numbers, likely because Numbers offers no mechanism to change data formatting on import. Since Numbers formats table cells automatically, it will remove leading zeros from numbers.
While it’s possible to manually put back leading zeros after data import into Numbers using various cell functions or a custom data format (on Mac, preserved on iPad), a better approach is to have Numbers recognize numbers with leading zeros as text on import. Numbers will interpret any numbers preceded by an apostrophe as text. You can do this by editing a CSV file or a text file with CSV structure. This is accomplished with text Find and Replace. You don’t need to do this manually, you can create a shortcut with Apple’s Shortcuts app, available on the App Store. A Shortcuts User Guide is also available. Place an icon on Home Screen to launch the shortcut. The following workflow consists of a Select File action, Get Name action, Replace Text action, Save File action, and Open File action:
Running the shortcut on a CSV file is straightforward. Tap the shortcut’s icon. Search for the intended CSV file and select it. Numbers will open a spreadsheet populated with the processed CSV data. Numbers with leading zeros will be preceded by an apostrophe. To dismiss the apostrophes, tap Edit, select the appropriate column (i.e. SKU), tap the paintbrush icon to open the format panel, tap Format, then tap Automatic though it is already selected. Tap the paintbrush icon to dismiss the format panel. Tap anywhere in the spreadsheet to deselect the column.
When finished with the spreadsheet you’ll want to move it to the Numbers folder. Tap the filename in the toolbar at the top of the screen. Tap Move. Find and tap the Numbers folder. Tap Move.
You can create the shortcut yourself or download it from this iCloud link I put up. If it doesn't open the first time you may need to tap the link a second time to display the shortcut for download in the Shortcuts Gallery. You’ll need to open the shortcut in the Shortcuts app to add an icon to Home Screen.
iPad Pro (12.9-inch) (3rd generation), iPadOS 15.5
According to Square’s Import Items Online support document, they recommend exporting using an Excel (.xlsx) file format. This makes sense since the data is formatted to text thus eliminating the need to deal with the issue of spreadsheet tools removing leading zeros on import. This is fine for exporting the Item Library from the Square Dashboard, but for any other product that might include SKUs, such as an Item Sales Report, the only option available is to export in a CSV file format.
In the same support document mentioned above, Square describes steps to import CSV data in Microsoft Excel and Google Spreadsheets (Sheets) so that data such as SKU numbers are formatted to text on import. Excel offers more control for data formatting on import, while Sheets takes a global all-or-nothing approach. Not mentioned is Apple’s Numbers, likely because Numbers offers no mechanism to change data formatting on import. Since Numbers formats table cells automatically, it will remove leading zeros from numbers.
While it’s possible to manually put back leading zeros after data import into Numbers using various cell functions or a custom data format (on Mac, preserved on iPad), a better approach is to have Numbers recognize numbers with leading zeros as text on import. Numbers will interpret any numbers preceded by an apostrophe as text. You can do this by editing a CSV file or a text file with CSV structure. This is accomplished with text Find and Replace. You don’t need to do this manually, you can create a shortcut with Apple’s Shortcuts app, available on the App Store. A Shortcuts User Guide is also available. Place an icon on Home Screen to launch the shortcut. The following workflow consists of a Select File action, Get Name action, Replace Text action, Save File action, and Open File action:
Running the shortcut on a CSV file is straightforward. Tap the shortcut’s icon. Search for the intended CSV file and select it. Numbers will open a spreadsheet populated with the processed CSV data. Numbers with leading zeros will be preceded by an apostrophe. To dismiss the apostrophes, tap Edit, select the appropriate column (i.e. SKU), tap the paintbrush icon to open the format panel, tap Format, then tap Automatic though it is already selected. Tap the paintbrush icon to dismiss the format panel. Tap anywhere in the spreadsheet to deselect the column.
When finished with the spreadsheet you’ll want to move it to the Numbers folder. Tap the filename in the toolbar at the top of the screen. Tap Move. Find and tap the Numbers folder. Tap Move.
You can create the shortcut yourself or download it from this iCloud link I put up. If it doesn't open the first time you may need to tap the link a second time to display the shortcut for download in the Shortcuts Gallery. You’ll need to open the shortcut in the Shortcuts app to add an icon to Home Screen.
iPad Pro (12.9-inch) (3rd generation), iPadOS 15.5
The initial Leading Zero shortcut in my previous comment assumes the user selects a CSV file and not another file type, then the processed data from the created text file is imported into a Numbers spreadsheet. If you want to ensure that only a CSV file can be selected, add a Get Details of Files action to get the selected file's extension along with a conditional If statement logical block. When run, if the selected file's extension is 'csv' then the shortcut continues with text replacement (,'0 for ,0) and importing. Otherwise, an alert displays and the user is directed to select a CSV file from the file picker in an emulated infinite loop* until either a CSV file is selected or the user cancels.
The workflow:
The alert:
You can download the shortcut here: Leading Zero
*From 'Use Repeat actions in Shortcuts on iPhone or iPad' in the Shortcuts User Guide:
Create infinite loops
Although Shortcuts doesn’t offer an action to repeatedly run a group of actions forever, you can emulate this behavior by using the Run Shortcut action. The Run Shortcut action allows you to run one shortcut inside of another. Configure the Run Shortcut action to run the same shortcut that it’s contained within, and the shortcut runs from start to finish repeatedly until you stop the shortcut.
Roote
This is an excellent write-up. Please keep hold of it - and as soon as you reach ASC Level 5, please publish it as a User Tip for the benefit of the wider Apple Support Communities. This covers both a very specific user-issue - and construction of a very useful Shortcut.
LP
Thanks LotusPilot, much appreciated. As far as publishing as a User Tip, at my current rate of ASC progression that will be in a quarter century :-)
How to keep a zero in numbers?
