So I am having a heck of a time importing a CSV file. The ultimate goal is to create a repeatable process to import the file. The only way I've been able to import the file is in a roundabout way using MS Access (Import into Access then export to SQL Server), which it happily and merrily does without issue.
But surely SQL Server (Express or otherwise) should be able to import it also.
It is a great data set to play with (real world data) and I have designs on normalizing it, bringing it into SSRS and what not, largely to learn and have something to 'show' a prospective employer.
But I'll be damned if the file will import without errors. Ideally the columns will import and convert appropriately, i.e. columns that are numeric should end up in the table defined as Int or whatever is most appropriate.
The most frequent error I am seeing is:
Data conversion failed. The data conversion for column "wikipedia_link" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page."
Ideally I'd like to import the file via T-SQL and Bulk Insert, but have similar issues.
Perhaps if someone can take a gander and see what's up, ideally I'd learn something here and not looking for just a quick answer, unless of course I'm just doing something stupid like forgetting a check box.