Data shuttle adding leading Zeros
When uploading data with Data shuttle it has started adding leading zeros to columns that contain numbers so they are being read as strings instead of numbers and can no longer be used in formulas.
Is anyone experiencing this issue or have a solution. The problem started occurring this week and I have not experienced it happening before.
Best Answer
-
Hi @Nick Sor
Thanks for this screen capture! I can see that the one cell you have highlighted is in the proper format, however there are a number of 0's in this column that are "0" and not "0.00" (which would be the correct number format if the entire column is set as a Numerical).
Can you double check that both columns in the CSV have the same setting, across all cells?
Answers
-
I have also tried using a formula to remove the first character in the cell, but instead of removing the ' that was added it instead removes the first digit of the number.
-
Hi @Nick Sor
Try using the VALUE function instead, like so:
=VALUE([Current Budget]@row)
This should translate values with a leading apostrophe into being recognized as numerical. 🙂
As a side note, have you tried adjusting the column type in the Mapping section of the workflow? This may help ensure it pulls through as a number:
Cheers,
Genevieve
-
Hi Genevieve,
Thank you for the response. I just updated the one of the datashuttle workflows to specify a the data as a number to pull in; it fixed the issue with the leading apostrohe's/leading zeros, but it only pulled in the numbers before the first comma.
For example the values 3,379,657.97 and 1,280,141.19 were inputted into the sheet as 3 and 1 respectively.
The Value formula seems to be working as you mentioned, but it would be ideal if I could figure out a solution to why the datashuttle started having this error.
These workflows have been running without issue for months and just started running into this error last week.
-
Hi @Nick Sor
I'm unable to replicate what you're seeing, where the values come in only as the first number. However this leads me to believe that Data Shuttle is seeing the first comma as breaking the value in your CSV file - that's the point when it stops being a number.
Is it possible that your CSV formatting has shifted, recently? Can you check to see how the columns are formatted in your source file and check that this is numerical with thousand separators?
-
Hi @Genevieve P.,
Thank you for looking into this, I really appreciate the help.
I looked at previous versions of my source file and it looks like nothing as changed in the formatting. The columns where the issue is occurring are formatted as numbers with thousand separators as well.
-
Hi @Nick Sor
Thanks for this screen capture! I can see that the one cell you have highlighted is in the proper format, however there are a number of 0's in this column that are "0" and not "0.00" (which would be the correct number format if the entire column is set as a Numerical).
Can you double check that both columns in the CSV have the same setting, across all cells?
