Excel to SQL direct import error

Question

Working for a considerable time on cracking some sales data, I came across an error which started to bug me for so real, eating my time of work. After so much of an effort, I was so fed up and nearly to give up on un-importable records.

The scenario:

1. Bulk sales data comes on txt/csv format needs to be imported to SQL database and then matched with Address History information available on a combination of tables by verifying strings directly from field to field.

2. If codes matched, need to run a script to update few tables with data. If not matched, need to insert a whole bunch of data in to different tables to create ID which requires for the final sales import.

3. Most of the was matching, except for few which was giving the trouble. I just needed to import those to history tables. Then started the problems, even though, I updated them, i couldn't match them.

After some much of frustrated hours, I just asked my girl-friend to check when there any error in the string, I worked with.

The string is "Bramhall Stockport" to be matched to "Bramhall   Stockport". For SQL script, these two strings are not matching.

I bet if you copy and paste on your table this would match, coz now this is txt format.

Then, Ana figured the error (She is not a computer geek, Architecture Masters), by simply coping and pasting on Microsoft Word 2007.

Screenshot: http://www.contentbcc.com/Anushka/sql_xls.png

Do you see the difference? First is in the txt/csv file and second on the SQL table.

Answer 1

In the first one, you have three regular spaces (ascii 20). In the second one, you have a regular space followed by a non-breaking space, (unicode 0xA0). In excel you can do a search and replace with ALT+0160 as the search and a space character as the replacement to fix it.