[Cloud]

Hi,

I'm continusously having problems uploading UPC Codes. I've tried formating them as Text and also as number but 8 out of 10 times they appear like this “5.08224E+13” instead of: “50822383256325”

Is there a way for this to stop happening? I mean an ability to actually convert it in NS, cause no matter what my CSV file is correct and the right amount of digits and formatted correctly.

like for example how =TEXT OR =NUMBER works in excel.

Also I want this to work in Exports too in item searches. I export a list of 500 items but in excel all of them show 5.08224E+13. Is there a way I can put some =TEXT formula in for it to export correctly?

Thanks
This is a cached copy. Click here to see the original post.

[michoel]

The issue here is that Excel is trying to be helpful and convert what it thinks are long decimal numbers to exponential notation. Once the CSV file has been saved and Excel closed, you cannot accurately convert them back, as the numbers have been rounded off and you have lost the last 9 characters, so you will need to do this in Excel.

In my experience, the best way to do this is to this is to never open a CSV file by double clicking on it. Instead, create a new Excel file, and use the Text Import Wizard (Data > From Text) and ensure you select Text as the import format for the barcode column in the third step. Keep in mind that in Excel there is a difference between changing the Display Format to Text, to actually changing the underlying cell data format to Text – you need to make sure you have done the latter to avoid problems.

[j.j]

One tip that I use is prefix any numeric/date string with ' a single quote – this will make excel mark it as text and it will not convert it to anything else automatically. hope this helps.

[Cloud]

Originally posted by michoel

View Post

The issue here is that Excel is trying to be helpful and convert what it thinks are long decimal numbers to exponential notation. Once the CSV file has been saved and Excel closed, you cannot accurately convert them back, as the numbers have been rounded off and you have lost the last 9 characters, so you will need to do this in Excel.

In my experience, the best way to do this is to this is to never open a CSV file by double clicking on it. Instead, create a new Excel file, and use the Text Import Wizard (Data > From Text) and ensure you select Text as the import format for the barcode column in the third step. Keep in mind that in Excel there is a difference between changing the Display Format to Text, to actually changing the underlying cell data format to Text – you need to make sure you have done the latter to avoid problems.

But how will this help? I save the file as a CSV and it's saved because I use a template I created in Excel (xlxs file) and once completed I copy and paste it into a new excel file and format the UPC codes again and save it. I rarely open the CSV once saved. Even if I do and follow the instructions I will still save the file as CSV so won't it get formatted back? Probably me not understanding correctly.

[Cloud]

Originally posted by j.j

View Post

One tip that I use is prefix any numeric/date string with ' a single quote – this will make excel mark it as text and it will not convert it to anything else automatically. hope this helps.

So let's say you want to upload some items and save the file as CSV is that when you add the ' ? How does NS take it, I mean how do you remove it? Do you also use this in a Search Query inside Date fields and numbers and add ' single quote then export it. Then I assume you remove this after but won't it convert it back?

[Chuan Zhuo]

before import to NS, open your csv file by NOTEPAD, so you see what's the number exactly in this prepared CSV file.

1. make sure your csv file numbers are correct before import. you can try Openoffice, Numbers… prevent Excel auto formatting numbers you can do import CSV to EXCEL, define all columns as TEXT.

for NS exported CSV files, the number inside is good, open it by notepad to check, so donot open it by excel autoformatting()

[michoel]

Originally posted by Cloud

View Post

So let's say you want to upload some items and save the file as CSV is that when you add the ' ? How does NS take it, I mean how do you remove it? Do you also use this in a Search Query inside Date fields and numbers and add ' single quote then export it. Then I assume you remove this after but won't it convert it back?

The place to add this character is in Excel, before you save the file as a CSV. The character won't actually be saved – it's just the way you tell Excel to store the data as text and not as a number.

Attached Files

[HubertJ]

I just wanted to add another solution.

You can set up formatting as you can see on attached picture.

Format Cells -> Custom -> 0

Attached Files

[Author]

Posts