Help with Excel query! Excel experts!?

Question

Okay, I'm working with a database in microsoft Excel that I've scanned in using OCR software. The OCR software I'm using is a little glitchy and some of the dates got mis-scanned and therefore the file is having issues importing into microsoft access. There are over 300,000 records and I don't feel like manually searching through. Bottom line, is there any way I can have this query search for two "/" in each date cell and if it doesn't detect it, it highlite it or notify me some way. Thanks for the help!!!!

Answer 1

If I had 300,000 records of anything, I wouldn't let it anywhere near Excel.



Using FIND("/",A1) will not work if the the date is a date fromat. You would have to be certain that your date was imported as text to be sure of it working, otherwise you will get a #VALUE! error on all numeric data including dates. This also applies to the SEARCH function, it is only good if the date is in text format.



Using VALUE(A1) will also not be wise as it will give you false positives, as any data that is numeric but not a date will result in a numeric value. For example if 01/01/2007 were read by your OCR as 01012007 you would get a value of 1012007, equivelent to a date of 11/10/4670.



Another problem you have is if your OCR misses a digit, for example if it reads 11/01/2007 as 1/01/2007.



I would recommend getting your OCR to save the data as a CSV file and importing directly into MS Access using a VBA sub-routine, that way as well as searching the date field for two occurances of / it can also check to make sure it has the correct number of digits, and thus ensure a higher level of data integrity than if you try to go through excel first.



If you are unsure how to do this then I recommend you employ somebody that does this sort of thing for a living.

Answer 2

You'll need some variety of CGI programming to do this. That means dynamic pages written in something like PHP, Perl, C, Java, etc. I'd recommend PHP because it's probably the simplest, and lowest learning curve. What's CGI? It basically means "dynamic content". You're probably familiar with basic HTML pages. They're just files that you have, and they get delivered as-is to your clients. Each time the webserver gets a request for an HTML page, it just sends the HTML file-- very simple. But CGI is different. Rather than a flat file, it's a program whose output gets sent to the end user. So whenever the webserver gets a request for a CGI program, the webserver runs the program, and sends the output of that program to the user. The client may still see HTML, but it's created on-the-fly by your CGI program. So the program can do all kinds of things like write output to a database, check some information, verify the userid, etc, etc. But having CGI on your site will mean you need an ISP who will allow that. Check with your ISP to see if they allow scripting or not, and what sorts of dynamic web content they allow. If they don't allow it, you need a new ISP. But before you rush on your way to a new ISP, another thing to check for is some sort of database ability as well. You want to store these contact lists somehow, and while you could invent your own file format to store them, I'd caution you against it. Mostly because there's a lot of holes for newbies to fall into that won't bite you until your users start using the system. Anyway, I'd recommend MySQL, but there's a variety of other stuff out there. Next up, familiarize yourself with the CGI language and database of your choosing. Make some simple CGI scripts that use basic forms and form processing. Then figure out how to set up your databases. Make some test tables, test data-- figure out how to work with it. Finally, hook the CGI into the database by writing to it when your form processing CGI's are called. Now you're ready to write your application for real. DaveE

Answer 3

OR

Lets say your date is:



8/092007 (missing the"/")



It's sitting in A1



type in b1 =Value(a1)



It will return #VALUE! if it's not a good date.



Put the =value(a1) through for all of the cells.

Turn on your filters

Data>filter>auto filter



Now select #VALUE in your filter and there you go. All dates that are not good working dates have been selected for correction.



Since it loves to mis-interpret the same way- I would then suggest SORTING the A column- and then get the same of mistake together in a chunk for a better correction.



I have been working with OCR in the same manner too.



(it loves grabbing 7 and thinks it's a ? for some reason)

Answer 4

There is a way. I would write an "if" formula in a cell next to the date column to check it, using Search. Here's the syntax for Search:



SEARCH(find_text,within_text,start_num)



You may need to do this in two columns to capture both /'s.

Answer 5

Let's say you have a supposed date in A1.

Make B1 = FIND("/", A1)

Make C1 = FIND("/", A1, B1+1)

Make D1 = FIND("/", A1, C1+1)

Make E1 = IF(ISERR(D1), IF(ISERR(C1), "ERROR", "OK"), "ERROR")

The result of this is that E1 will be "OK" if the date in A1 contains 2 slashes and will be "ERROR" otherwise.