How to separate EAN and UPC barcodes?

[Kurt Hansen]

We have for many years (of forgotten and unknown reasons) put a zero in front of all 12-digit bar codes so that all bar codes in our FileMaker database appears as a 13-digit code.

Our products in the database are from suppliers from all parts of the world and some are using EAN, while others use UPC.

I need to separate them, so they will reflect the original barcodes (UPC (12) / EAN (13)). We have approximately 33,500 items in the database.

How do I do it?

[comment]

How would you do it manually?

[Kurt Hansen]

I don't know, Consultant. Sorry.

Barcodes are very confusing (to me). Example from our FM database:

0608917220124

How do I determine whether it's a 12-digit UPC with a manually added leading zero or a 13-digit EAN with the country code 0?

Edited March 2, 2016 by Kurt Hansen

[comment]

This is a Filemaker forum and - at least at this point - your question is about barcodes (or, more precisely, about the numbers that these barcodes carry as payload). I know very little about that, but I suspect that what you're asking for may be impossible.

As your own example demonstrates, EAN numbers can start with a 0, so you can't tell them apart just by looking at them (although you can tag all codes that do not start with a 0 as unequivocally EAN, taking at least that chunk out of the game).

The next possible tool to use would be the check digit. However, AFAIK both schemes use the same check digit algorithm and a 0 has no weight in this algorithm. IOW, a valid 12-digit UPC with added 0 will also pass validation as 13-digit EAN. At least that's what I think based on my limited understanding. You can verify that using your own means - but the problem is that even if this is incorrect, validation would still pass 10% of the originally 12-digit UPC codes as having a valid EAN check digit simply by coincidence.

Your last resort would be to analyze the codes by their components and eliminate those that are not likely to appear in your line of business. I am not sure how that could be done (if at all) and the results could not be guaranteed to be unequivocal either.

Finally, consider the possibility that this may not matter for any practical purpose - see: https://en.wikipedia.org/wiki/Global_Trade_Item_Number#Comparison_of_UPC.2C_EAN_and_GTIN

Edited March 2, 2016 by comment

[ggt667]

I would do this determination in the actual software reading the barcode, I have only used barcodes with iOS( any barcode and cocoaFX ) and HTML( FX.php and barcodes Code 39 ) with FileMaker.

For the iOS version I am able to do this in the objective C code.

For the HTML version I would have no idea how to determine which barcode accent was used.

Does FileMaker itself have a Get()-function for this?

[jbante]

I'm with Comment on this. Why exactly do you need to separate UPC from EAN barcodes? (Which is to say, you probably don't really need to do this.) EAN, and GTIN after it, were designed specifically to accommodate the older UPC system without changes. You're probably best off using the 13-digit codes for all values, and adding or removing the leading zero from 12-digit UPCs as appropriate when interacting with outside systems that need to work that way.

ggt667, FileMaker can report the barcode type of a scanned barcode using the GetContainerAttribute function, but the container field must have been set using the Insert From Device script step, which only works on an iOS device —  so it would require setting up an iDevice to scan through all the records. Even then, this isn't particularly useful for distinguishing between EAN and UPC barcode images, and I'm skeptical that any iOS tools are useful for this, either. A UPC barcode and the equivalent EAN barcode (with leading 0 added to the UPC) are the exact same bar pattern. This is by design, for backwards compatibility. Configure any barcode scanner to only read EAN(13) barcodes, and it will read UPCs by assuming there's a leading 0.

[Kurt Hansen]

1 hour ago, jbante said:

Why exactly do you need to separate UPC from EAN barcodes?

We are a a company with many activities: import/export, domestic distribution and we also run a retail store. The supplier of the POS in the store tells us incessantly that the leading zeroes causes him problems because we have products from many countries and with barcodes in different systems.

When we update the POS with a CSV extract from FileMaker (all bar codes are 13 digits here), then it will create duplicates because his system has removed all leading zeros in the existing UPC barcodes in the POS.

1 hour ago, jbante said:

A UPC barcode and the equivalent EAN barcode (with leading 0 added to the UPC) are the exact same bar pattern. This is by design, for backwards compatibility. Configure any barcode scanner to only read EAN(13) barcodes, and it will read UPCs by assuming there's a leading 0.

If it is true (which I'm sure it is) then our supplier is not professional enough?

Edited March 4, 2016 by Kurt Hansen

[comment]

1 hour ago, jbante said:

Configure any barcode scanner to only read EAN(13) barcodes, and it will read UPCs by assuming there's a leading 0.

 I think such assumption would only be valid for products made in USA or Canada? Quoting from the link in my post above:

A UPC formed in the United States can be transformed into an EAN by prefixing it with a zero.

 

40 minutes ago, Kurt Hansen said:

The supplier of the POS in the store tells us incessantly that the leading zeroes causes him problems

Perhaps you could simply remove the leading zero from all values, before passing them to the POS?

 

44 minutes ago, Kurt Hansen said:

his system has removed all leading zeros in the existing UPC barcodes in the POS.

Does that mean he has a list you could use for comparison?

[jbante]

14 hours ago, Kurt Hansen said:

If it is true (which I'm sure it is) then our supplier is not professional enough?

I'm inclined to give your supplier the benefit of the doubt and suppose that perhaps they are a victim of the limitations of their tools. Comment's suggestions are on the right path for resolving the issue from your end.

[MonkeybreadSoftware]

in MBS Plugins we have a few barcode functions:

http://www.mbsplugins.eu/component_Barcode.shtml

We do have functions for EAN and UPC checksums, so you may find difference by checking the checksums.

Only one should match.

[comment]

24 minutes ago, MonkeybreadSoftware said:

Only one should match.

Are you sure about that?

[MonkeybreadSoftware]

oh, I'm sorry, those may be equal.

[tru80]

Normally if I purchase a UPC Code, I will receive both separate numbers UPC 12-digit barcode and a EAN 13-digit barcode, I normally receive both numbers each time I order UPC codes, which makes it alot easier and hassle free.  I have been using http://officialupccodes.com for a very long time and I haven't had any problems and fast service. 

[dronte]

How to SEPARATE digits from EAN code....

You need to use this function:

Middle ( text ; start ; numberOfCharacters )

text - is the source field which you need separate

start - its the position of number (from left of course) 

numberOfCharacters - its how many digits you need to separate... in Your case only 1 i expect.. 

so then it looks like this: 
Middle ( num ; 1 ; 1 )

Middle ( num ; 2 ; 1 )

Middle ( num ; 3 ; 1 )

etc.. 

;o)

[comment]

5 minutes ago, dronte said:

How to SEPARATE digits from EAN code....

No one asked about that.