SOLVED

Barcodes in Exel

Copper Contributor

Hi guys i need some major help.

 

I am setting up a POS system for a shop and i'm having issues with the barcode formatting. When first entering the data I have no problems, I reformat the column to text and everything works fine. What i need help with is downloaded documents.

When I want to add a new product into the shop system I have to download a bulk template of all the products already uploaded which is where my problem lies. I have both barcodes with leading 0's and long numbers which convert to scientific notation. Everything I can find tells me how to prevent these things from happening not what to do when the file does it automatically upon opening. No matter what I do to the document the scientific numbers stay scientific and the leading 0's have already been deleted. To make it more complicated only some scientific numbers stay in that format while others are more than happy to return to a simple number format. Currently i have to go through and pick out all the barcodes in scientific and reformat the cells as numbers then pick out all the ones that should have leading 0's, reformat them to text then manually re add the 0. The catch is they are all mixed up and there is no way to tell what should have a leading 0 once that 0 is gone. Just because its a short barcode doesn't mean it should have a 0 and vice versa.
I am sick of needing to check 3000 barcodes every time we get a new product in the store. All i do these days is fix work i've already fixed a thousand times. I'm going crazy. Does anybody know how to save my barcodes?

 

To work the file must be in CSV and contain only simple numbers in the barcode column. 

In the picture bellow column P is what it should look like column Q is what i get.

20 Replies
best response confirmed by Ali_111690 (Copper Contributor)
Solution

@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.

 

 

@Riny_van_EekelenI seem to have tried all possible options....but cannot get the column to import properly. Still importing in a formula at 5.06E+12 (for example). Have tried to open an excel file and import the data, even tried to do it in Google sheets. Not sure your option below works..??

@Rocky247 Can't really tell until I see what you are trying to import and how.

@Rocky247 

Importing a number and displaying as 5.06E+12 is not, of itself, a problem.  It is perfectly possible to display the number as a sequence of 13 digits padded with leading zeros by applying a number format

"0000000000000".  What is more troubling is that you seem to have imported some codes as text and others as numbers.  I am not sure why this is.

@Peter Bartholomew we've exported the data from our inventory system, and that column shows data like that 5.06E+12. We can then reformat that particular column to show it how we want it, but on resaving as a CSV it reverts to 5.06E+12.

@Rocky247 

If that means you lose the last 10 digits of the code then there is no way back!  Can you introduce a formula to create a text helper column?  If so

= TEXT(value, REPT(0,13))

will give a text string that should upload correctly.

@Peter Bartholomew Thanks for that - I was able to create a new column which displayed the data correctly (then I copied the data in the resuting column and repasted it as values only), but on exporting to a csv again to upload back into the system again I am left with the same issue.

@Rocky247 Would like to repeat my earlier question. What exactly are you trying to import and how? Perhaps you can upload a portion of the CSV file (with codes that cause problems) to your next post.

@Riny_van_Eekelen  see attached - last column. If I change to a number column and remove the decimal points it gives me back the full bar code. When i save as a csv I lose it again.

@Rocky247 Is that file really how it comes out of the POS? When I open it in a text editor it looks like this:

Screenshot 2022-01-01 at 13.13.18.png

The barcodes are, thus, already included as if they are scientific numbers.

If this is NOT the file from the POS, please upload one of those, without opening and resaving it in Excel first. Use a text editor to remove confidential information and perhaps to reduce the file size.

@Riny_van_Eekelen I've redownloaded the file and opened it in Notepad instead, but not sure how to edit it all without sending it all to you. Have also replied to a support ticket with the inventory system (DEAR Systems) because it shows correctly in the system but when exported (as a csv - thats the only option) it is a scientific number as per your screenshot. Just don't want to reimport it to DEAR if it does go and mess up our data.
Yes, downloading the csv from Dear Systems, opening it to remove all lines apart from one where we know the barcode is right, and saving it again, it will not upload back into the system - the barcode changes to scientific @Riny_van_Eekelen

@Rocky247 Then I don't know. Sorry.

Hi @Rocky247 

> it shows correctly in the system but when exported (as a csv - thats the only option) it is a scientific number as per your screenshot

This sounds like DEAR Systems code generates the output like it was created by Excel instead of writing the raw data directly to a csv file.
Ev figures omitted in rounding to scientific before reaching the csv file are lost while trailing zeros may be rescued.

 

__/ reading macro

I added another test line with a leading zero

bosinander_0-1641052897305.png

and can via a macro read them as text

bosinander_2-1641053112452.png

Row 26. An inserted leading apostrophe (') makes the cells content commented and not interpreted as a number.

Row 24. Converts scientific format to plain numeric (0). Here, trailing zeros are appended.

 

Sub CSV_Read()
    path = ThisWorkbook.path & "\" & Range("source").Value
    foundFile = Dir(path)
    If foundFile <> "" Then
        Range("target").CurrentRegion.ClearContents
        
        Close
        fileNo = FreeFile
        
        Open ThisWorkbook.path & "\" & foundFile For Input As #fileNo
        text = Input$(LOF(1), 1)
        Close #fileNo
        
        allRowsInArray = Split(text, vbCrLf)
        rowCount = UBound(allRowsInArray)
    
        rowCounter = 0
        Do While rowCounter < rowCount
            currentLineToArray = Split(allRowsInArray(rowCounter), ",")
            columnCount = UBound(currentLineToArray)
            For columnCounter = 0 To columnCount
                If columnCounter = Range("special_columnNo").Value Then
                    If InStr(currentLineToArray(columnCounter), "E") > 0 Then
                        Range("target").Offset(rowCounter, columnCounter).Value = "'" & Format(currentLineToArray(columnCounter), "0")
                    Else
                        Range("target").Offset(rowCounter, columnCounter).Value = "'" & currentLineToArray(columnCounter)
                    End If
                Else
                    Range("target").Offset(rowCounter, columnCounter).Value = currentLineToArray(columnCounter)
                End If
            Next
            rowCounter = rowCounter + 1
        Loop
    End If
End Sub

 

__/ writing macro

Using macro also for saving the edited csv file, the codes still looks like 'text' and the apostrophe is omitted. Leading zero still there.

bosinander_3-1641053318753.png

 

Sub CSV_Write()
    text = ""
    rowCount = Range("target").CurrentRegion.Rows.Count
    columnCount = Range("target").CurrentRegion.Columns.Count
    
    For rowCounter = 0 To rowCount - 1
        For columnCounter = 0 To columnCount - 1
            If columnCounter = 0 Then
                text = text & Range("target").Offset(rowCounter, columnCounter)
            ElseIf columnCounter = columnCount - 1 Then
                text = text & "," & Range("target").Offset(rowCounter, columnCounter) & vbCrLf
            Else
                text = text & "," & Range("target").Offset(rowCounter, columnCounter)
            End If
        Next
    Next rowCounter
    
    path = Left(Range("source").Value, Len(Range("source").Value) - 4) & "_" & Format(Now(), "YYYY-MM-DD_hhmmss") & Right(Range("source").Value, 4)
    path = ThisWorkbook.path & "\" & Replace(path, "*", "")
    
    fileNo = FreeFile
    Open path For Output As #fileNo
    
    'Write the selected data into the text file.
    Print #fileNo, text      'Write includes "
    Close #fileNo
End Sub

 

 

__/ prefs

To avoid editing in the macro, you can enter the input files name in P1. The file is expected to be in the same file as the macro.

P2 gives you the possibility to change column, should the output change column with the bar codes.

bosinander_4-1641056704302.png

Q2 counts the columns offset from first column.

 

The macro outputs the file starting in the cell named target. In the attached file, A5.

bosinander_5-1641056954633.png

__/ Scientific formatting

When Number Format is "General", the column width affects until 12 figures.

Scientific applies rounding and cuts accuracy.

bosinander_6-1641057388683.png

@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;

bosinander_0-1641103397069.png

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.

 

We finally figured out that when you export the file from Dear Systems as a csv, the column is in Scientific. On opening it, if we convert it to a standard excel file to work on it/add Data filters etc, we then changed the column to a Number column and adjusted the trailing zeros. Then when we were done, we saved as a csv and didn't open it before re-uploading it. If we did reopen it, it changes the cell back to Scientific. @bosinander @Riny_van_Eekelen @Peter Bartholomew
Thanks for the update.

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 

Spoiler
Yes, it is easy once you know how.

Save in the csv as a number format and use the sheet. It will be fine. But if you reopen it before uploading it, it WILL revert back everytime. Don’t know the technicalities but it does work.
1 best response

Accepted Solutions
best response confirmed by Ali_111690 (Copper Contributor)
Solution

@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.

 

 

View solution in original post