2

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.

The file is out in the wild here: http://ourairports.com/data/ and specifically: http://ourairports.com/data/airports.csv

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.

Thanks

Improve this question
4
  • In the import wizard, you can specify the input size of the fields you're importing, as well as the field size in the destination SQL Server table.
    – Hannah Vernon ♦
    Commented Oct 21, 2014 at 1:22
  • Are external scripts an option? I've found the SQL Import/Export wizard to be a colossal pain, but one can whip up a simple C# app to read from a file and write to a table in a couple hours. I'm sure other languages are just as easy.
    – Jon of All Trades
    Commented Oct 21, 2014 at 16:22
  • I suppose. Ideally I'd use T-SQL to Bulk Insert it but that hasn't worked either. MS Access itself happily imports it and exports it to SQL Server without any complaints.
    – Steve
    Commented Oct 22, 2014 at 10:45
  • Steven, have you tried a format file for use with the BULK INSERT (also works with BCP) that defines the layout?
    – Solomon Rutzky
    Commented Nov 22, 2014 at 21:51

2 Answers 2

Reset to default
1

The method of insert doesn't appear to be the problem, more the data itself

The error you are receiving is because one of the columns you have in your database table is too short (see your column 'wikipedia_link') see what length it is and if you can increase it, note it is quite possible to have more errors on other columns that are similar, judging by the data contained in that field the longest field is 128 characters, so I'd make the field nvarchar(130) as a minimum

If you load the csv in excel you can find out the max length of a column by using {=MAX(LEN(Q:Q))} (NOTE to get the {} array calculation after typing the query press ctrl + shift + enter)

Check all your fields are long enough and try again

Improve this answer
1
  • Thanks. I eventually got it: name, municipality, home_link, wikipedia_link and keywords all had to be 255 characters. A good data set for me to practice C# Linq against. Thank you all.
    – Steve
    Commented Oct 18, 2015 at 21:08
0

I was able to import the file successfully using the import & export wizard by changing the column widths for; name, wikipedia_link and keywords to 255

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.