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

  • 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. – Max Vernon Oct 21 '14 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 Oct 21 '14 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 Oct 22 '14 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 Nov 22 '14 at 21:51
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

  • 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 Oct 18 '15 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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