CODEWITHSUNDEEP
sqlsql-serverleft-joinimport-csvvarcharmax
slowlygettingthere
slowlygettingthere

Reputation: 117

Problem doing left join on varchar on csv imported data

I am importing some data from a CSV with the intention of then doing a lookup on one of the column names to get their location from another table (we're having to do it by name rather than an ID as we aren't provided with one unfortunately from the import data).

However when I do a left join on the names which look the same they aren't matching. If I do a select on the columns from each table using CAST(ColumnName AS VARBINARY) I can see the binary data is actually different.

How can I either do a left join so they link or update the data to strip whatever the CSV is adding? Looking in notepad at the CSV it looks clean weirdly. I'm currently doing:

BULK
    INSERT [tbl_Import]
FROM 
    'C:\data.csv'
WITH
    (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
    )
GO

Update [tbl_Import]
set Name = LTRIM(RTRIM(Name))

But when I try and do a join, it returns nothing

Select 
ci.Name
,p.NorthSouth
,p.Name
From 
tbl_Import ci
left join lu_NorthSouth p
on p.Name = ci.Name

Any help appreciated. Thanks

Upvotes: 1

Views: 95

Answers (0)

Related Questions