Forum Discussion
Barcodes in Exel
Ali_111690 On the Data ribbon, you should have a button "Get Data". Click it and select "From Text".
Navigate to the folder with the CSV file. Excel will only highlight TXT files but you can still select the CSV file and press Get Data. The text import wizard opens. In step 1 you select Delimited. In step 2 you set the comma as the delimiter. Step 3 will give you a preview of how the text will be split in columns. Now, make sure that you set the correct data types for each of the columns (General, Text or Date). In your case, you need to set the column with the barcodes as Text. Press finish.
This should import the CSV file in neat columns with the original barcodes as texts, preserving the leading zero's.
Rocky247 Since conversion to scientific data may truncate accuracy already in the systems output, you will depend on having a correct combination of product code and bar code outside the host system.
Merging with a previous list where you already have fixed the bar codes manually, you will in the future only have to fix the new products;
Column O may miss accuracy for the last digits or be in scientific format if using Data: From Text/CSV as suggested by Riny_van_Eekelen.
Column P is looking up the previous bar code for known products.
=LET(
prodCode; OFFSET(A6;0;0;COUNTA(A:A)-1;1);
barCode; OFFSET(prodCode;0;COLUMNS(A:O)-1);
prevOutput;XLOOKUP(prodCode;prevReturn2dear!A:A;prevReturn2dear!O:O);
output; IFS(
ISNA(prevOutput);barCode;
prevOutput=barCode;prevOutput;
1;prevOutput
);
comment; IFS(
ISNA(prevOutput);"new product";
prevOutput=barCode;"-";
1;"using previous bar code"
);
output
)Attached also as an xlsX without macros that should be okay if you don't get problems (like conversions to scientific) when saving as csv.
HShowalt If all you need is to save EAN numbers that you already have without errors in Excel - go with the spoiler and save them as text.
If you need to import and work with numbers generated outside the workbook, spend some time to understand the difference between numbers and strings. The replies above contains both information and possibilities but it’s up to you to balance the reading effort against your needs.
I am having this exact same issue with a different POS provider. Have tried absolutely everything, but every time I try to upload the csv spreadsheet to my website provider, the product code data column converts automatically back to the scientific code. Anyone figure out any lasting solutions to this issue?Rocky247
- Thanks for the update.
