How to detect that a file is not ANSI-Latin1?

Question

I have a date import project in which the clients send ANSI-latin1 encoded files (iso-8859-1). However... It seems that on a weekly basis we get a surprise file, one that is not of the correct format and the import basically dies horribly and needs manual intervention to recover and move on... Most common bad file formats seem to be excel, compress file or an XML/HTML file...

So in order to mitigate the human intervention, I would like to reasonably determine if we have a strong ANSI candidate file, before attempting to go through each line of the file looking for 1 of 64 bad characters and then making a guestimate on whether the whole line or file is bad on the # of bad characters found…

I was thinking of maybe making a Unicode/UTF check and/or magic number check or evening trying to check for a few specific application types.. The files have no file extensions so any check would be by examining the content and any fast way to rule out the file as non-ANSI would be perfect, since the import process needs to process 100-500 records a second.

NOTE: Over 100 different types of bad files have been sent to us, including images and PDF's. So there is a concern about whether you can easily and quickly rule out LTOS of different non ANSI types rather than specifically targeting just a few...

Answer 1

Given your example "bad" files types, I'd say put in series of quick checks on the first few bytes of the file:

• Is a UTF-16 BOM?
• Is it "<html" or "<!DOCTYPE"?
• Is it "<xml"?
• Does it have a NUL character?
• Is it `PK\003\004' (the zip file header?)
• Is it (whatever Excel files start with; you'll have to look that one up 8-)

Answer 2

I like RichieHindle's answer it is very good. You should also look at error handling in your import. If you come across a bad file capture the error, log it and move on. You should not be stopping the import of other files, or worse the import of other customers because of one error in one file... If there was a way to notify the customer by email etc that the file could not be imported you might not have to do as much manual intervention.

Answer 3

On a Unix-type system, you'd use the "file" command for this. I wonder if there's a port of "file" to Windows? I couldn't find one in Google, but I'd bet it's available on GNU.org somewhere...

If you happen to have a stockpile of typical "bad" files around, it'd be pretty easy to build a database of file signatures, similar to what "file" uses.

Answer 4

Looking at the first few bytes is a good idea, but sometimes it can lead to false conclusions.

I recall making a CSV file for inserting values into a MySQL database, but I opened it with Excel first to check that everything looked OK.

Excel immediately said "This is a SYLK file, are you sure you know what you're doing?"

I'd never even heard of SYLK files before, but Wikipedia told me it's a CSV file with a header in which the first characters are 'ID,'.

This probably doesn't have any bearing on what you're doing, but I thought I'd point out that magic numbers aren't as magic as they might be.

Answer 5

You can read the beginning into a StreamReader and then call the CurrentEncoding property.

http://msdn.microsoft.com/en-us/library/system.io.streamreader.currentencoding.aspx

Note that 100% reliably detecting the encoding is theoretically impossible. However, the CurrentEncoding property goes through the best general set of heuristics to make a good guess.