Fix the phone number transformation to scientific notation on CSV order import
If you use CSV format to import orders, any phone number longer than 11 digits will be converted by the CnD system to scientific notation on import.
For example: 12345678901 will become 1.23457E+11
This is a problem for international numbers as specifying the country code will make it longer than 11 digits.
The workaround is to manually converted the CSV to XLSX and apply number formatting on the phone number data, however this will add an extra step to the import process.
The proper (and simple) fix would be for CnD to correctly type convert the phone number on import. Otherwise there should be an advisory on the CnD import page stating that CSV should not be used for orders with international phone numbers.
Simon Hanna
shared this idea
6 comments
-
Hi, first of all thanks for the feedback. Fortunately this has a work around as discussed below in the comments, we acknowledge this as an issue and will address it in the future however we cannot offer a timescale on this yet.
-
Ics19
commented
Seems like you could be on to something - maybe I've just been lucky in the past! I'll take a look next time there's an update required. Thanks for the heads up.
-
Simon Hanna
commented
I've done a bit more testing. Conclusions as follows:
If the value contains a space, whether quoted or unquoted in the CSV, it will import okay. The space appears to force the CnD importer to treat the value as text type.
If the value does not contain a space, quoted or unquoted, and with or without a leading "+", the CnD importer treats the value as numeric type and will convert to scientific notation if more than 11 characters.If you aren't seeing this in your automated import, the issue would appear to be specific to the CnD web UI-based import.
At least I have a reasonable workaround to get on with, which is to add a space after the country code in any international phone number.
-
Ics19
commented
Ahh, I think the difference is that your fields aren't quoted. e.g. my exports would be:
"...,"123456789012",..." or "...,"+123 456789012",..." or "...,"00123456789012",..."The only fields I don't quote are pure numeric ones, such as weight, cost etc
Hopefully your address fields are quoted as you need to cater for things like "1, High Street" or "Main Road, Small Village". I know the C&D documentation isn't great, but the 'standard' rules for CSV formatting seem to be obeyed. e.g an enclosed double quote is duplicated, such that The "Manor" House becomes "The ""Manor"" House".
I can't comment on how the Magneto integration could be modified. I've done my own Zencart integration which exports CSV via Dropbox and pulls back all tracking info. We use 'Print Assist' to automatically print the labels and only go into the C&D to manifest.
-
Simon Hanna
commented
The export format (from Magento) is CSV, and I've checked the files (in text editor) to confirm that the data is clean e.g. "...,123456789012,..."
I can reproduce this every time. When I import a phone number such as above (using CSV import) it will be converted to scientific notation in Click and Drop. The Click and Drop people were also able to reproduce.
Can you tell me what your CSV data looks like for phone numbers?
-
Ics19
commented
I've not had an issue with international number imports myself, the field is defined here:
e.g. numbers with a leading '+' or '00' (which takes the number length over 11 digits) have been carried through as is. This is true with spaces too. We've been importing orders for a couple of months now with customer entered telephone numbers (i.e. a varied bunch of format styles!) and never had error so far.
Is it possible you're just seeing the scientific notation when looking at CSV files in excel? I'd confirm by viewing the CSV in a text editor, like notepad or notepad++.
