Lookup failing though data exists

Latha1919 · Post by **Latha1919** » Tue Nov 21, 2006 1:01 pm

Hi,

In the current project, we have a job processing the .csv files (source is the .csv file) and finally loads the data into SQL database. Within the .csv file, have the data of varchar data type, mostly names fields. After fetching the data from this file, these names fields data will be looked up in the SQL database tables, and will be retrieving the corresponding ID field values, when the names exist.

Issue is: In the .csv file one name field has a valid name. This name does exist in the SQL dataabase, but the lookup is failing, hence this record is getting rejected. When I queried the database for this name (copied the name from .csv file), its not returning any rows. But if I type the same name manually in the Query Analyzer and execute the query, then it's returning the record.

But both the names are same. But, why its not returning teh record in the first case.

Please advise.

Thanks,

narasimha · Post by **narasimha** » Tue Nov 21, 2006 1:11 pm

My first try would be to trim the field and do a lookup

Latha1919 · Post by **Latha1919** » Tue Nov 21, 2006 1:19 pm

If I consider the execution of queries in the Query Analyzer;
when I use the name value from .csv file, query is not returning any rows. But when I reenter the same name in query analyzer and execute it, its returning row.
Why is that?

narasimha · Post by **narasimha** » Tue Nov 21, 2006 1:26 pm

This is strange, as the names are the same!
When you are copying from the .csv file are you copying some blank spaces too...
Try to trim the value and see if you get results

DSguru2B · Post by **DSguru2B** » Tue Nov 21, 2006 1:30 pm

Trim it. When your re-enter it manually you are omitting the blank spaces. Thats why this behaviour.
Try trimming the incoming data and then perform the lookup.

DeepakCorning · Post by **DeepakCorning** » Tue Nov 21, 2006 1:44 pm

Usually occurs when the CSV files have extra spaces in between the names or after or before the name. Trim may remove the issue but u will have to put trim in both the stages , lookup ad well the source or the names which have spaces in the DB will get rejected.

Testing - You can change the query by saying :LIKE the value you copied from teh CSV file and go on removing the spaces and see what exactly is stored in the DB and what is in CSV.