Sql-server – Line-breaks in Text column of CSV file

From what you are describing it sounds like you needs to provide the BULK INSERT command a format file so it knows how to parse your data. You can use the bcp command to auto-generate one for you, however I have always had to edit them afterwards before they work properly. (Lots of testing I am afraid).

The links below should be good starting points for you:

Format Files: http://msdn.microsoft.com/en-us/library/ms190393.aspx

BCP: http://msdn.microsoft.com/en-us/library/ms162802.aspx

BULK INSERT: http://msdn.microsoft.com/en-us/library/ms188365.aspx

EXAMPLE: http://msdn.microsoft.com/en-us/library/ms178129.aspx

I hope this helps you.

EDIT

Based on the extra information that you have added above it looks like you have set all field terminators to pipes, which is not what you want. You will need to edit the format file on a per column basis.

For instance, in the example above your first two columns are delimited by a semi-colon. The third would need to be delimited by ";|". Here is an example of what I mean for the first few columns:

9.0 
18 
1       SQLCHAR       0       510     ";"     1     web1numdoss 
2       SQLCHAR       0       510     ";"     2     web1dem 
3       SQLCHAR       0       510     ";|"     3     web1def 
...
18      SQLCHAR       0       510     "\r\n"   18    Col018 

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