CNPJ e CEP em lote a partir de planilha: 4 armadilhas (e um script Python testado)

When working with Brazilian company IDs (CNPJ) and postal codes (CEP) from a spreadsheet, there are four main pitfalls to be aware of. Firstly, Excel treats CNPJ and CEP as numbers, stripping leading zeros. This can cause issues when converting, such as the Banco do Brasil's CNPJ 00.000.000/0001-91 becoming 191 instead of the proper CEP format. Secondly, check digits must be able to handle the new alphanumeric CNPJ format, which adds another layer of complexity.

The second pitfall is that validating the check digits locally saves on API requests. For the alphanumeric CNPJ, the rule is the same as before (modulus 11), but each character's value is its ASCII code minus 48. If the result is less than 2, it remains the same; otherwise, subtract from 11. Keep in mind that sequences of repeated digits, like 00000000000000, will pass the check and need additional validation.

The third trap is respecting the usage limits of free public APIs, such as BrasilAPI, Minha Receita, ViaCEP, and OpenCEP. These services have limits to prevent abuse, and you should implement a self-imposed limit of API calls per minute to avoid hitting the 429 (too many requests) error. If you receive a 429, don't retry repeatedly, as it will only worsen the situation for everyone. Instead, switch to the next available source and consider using a paid service like Apify's Actor, which costs US$0.002 per lookup.

Lastly, always maintain separate columns for CNPJ and CEP data to avoid confusion. Use the outlined functions to clean and normalize the data, and remember that some CNPJs with 7 or 8 digits may require special handling due to lost leading zeros.