How to import large column (>4000 characters) from a CSV into database table

Question

We have a large CSV file with couple of columns. One of the column has a large data many times spanning more than 4000 characters and gets truncated when importing using data flow.

ADF data type is String
SQL data type is Nvarchar(max)

What are the approaches which can allow for successful import of data w/o truncation

@LeonYue : No. There are multiple files and this a daily process, not a one time thing. — DhruvJoshi, Jan 12 '21 at 06:58
I mean just copy one piece of the csv data and import them into sql database to make a test that if the data will be truncated. — Leon Yue, Jan 12 '21 at 07:00
The nvarchar(max) data type column length limit is 4000 characters. I think we can't store the data which length over than it. — Leon Yue, Jan 12 '21 at 07:07
@LeonYue https://stackoverflow.com/questions/11131958/what-is-the-maximum-characters-for-the-nvarcharmax/11131977 — DhruvJoshi, Jan 12 '21 at 07:14

score 4 · Accepted Answer · answered Jan 12 '21 at 09:53

You can use normal copy activity to dump the data from csv file to SQL. we tried reciprocating the scenario wherein had a column in file with length > 5k and destination as NVARCHAR(max) and it was success without data getting truncated.

And @LeonYue, NVARCHAR(max) can store beyond 4k characters which is a whole seperate discussion as mentioned by @Dhruv

How to import large column (>4000 characters) from a CSV into database table

1 Answers1