1

Can someone please confirm / deny if there is a cell limit (or any other kind of size restriction) when importing flat files into SQL Server?

I have been having an exasperating time recently, importing csv files which continually return the error "Could not convert to nvarchar, data would be truncated"... obviously that error message doesn't help, so I put error reporting on, and noticed that it routinely errored between rows 300 and 330... regardless of file.

I must've put 20 files in and got the same error, always at the same roughly the same row range. I have manually checked every file - there is no obvious cause for that error message in that cell range in any of the files.

My files aren't large (<1mb), contain mainly nvarchar data, and are limited to 500 rows... they are however quite wide (250 cols), leading me to wonder if there's a cell limit?

I tried chopping the files in two, so they were ~250 rows each, and - like magic - they import without issue.

Before I change the process that produces these files, can someone confirm if my suspicion is correct? (Or suggest an alternative cause?)

Edit - Using the built in "Import Flat File" Wizard in SQL Server 15.0. Comma separated; double-quotes; not sure re single quote in the string because the text is in Spanish and they don't use apostrophes!

Edit 2 - probably best to close this one now. A lot of logical suggestions which mirror the troubleshooting I've been doing. That is at least reassuring that it's not an obvious oversight.

smallnorthumbrianvillage
  • 49
  • 9
  • 1
    RE: ""Could not convert to nvarchar, data would be truncated". That looks like one of two possibilities: 1. you have non printable characters that will not translate to nvarchar or 2. the target column has a length shorter than the data length. – jim Feb 20 '21 at 17:00
  • 2 is possible, but I have visually verified, however 1 is plausible. There are emoticons in the source file. – smallnorthumbrianvillage Feb 20 '21 at 17:27
  • What is your table definition `CREATE TABLE`? – Charlieface Feb 20 '21 at 19:16
  • @charlieface Not sure. Using the built in Wizard: **Tasks>Import flat file** It'll be whatever the default is there. – smallnorthumbrianvillage Feb 20 '21 at 19:53
  • I would imagine it would be wise to set the column size to the largest possible size for that column in the files, you can do this in the `Modify Columns` page. The wizard is just guessing – Charlieface Feb 20 '21 at 19:55
  • @Charlieface first thing I tried and it made no difference. Yet splitting the file in two by rows works every time... it makes no sense whatsoever.... – smallnorthumbrianvillage Feb 20 '21 at 20:16

3 Answers3

2

I would suggest loading the file to a staging area first so there's no issue with data type/ length of the existing fields in destination table.

When importing, check the 'advanced' details for each column after choosing data source. The wizard checks a sample of rows at the start of the table (can't remember how many) to estimate column length. It defaults to 50, so if the first x number of rows in your table have under 50 characters it will use that, not realising that subsequent rows in the table have more characters.

This may explain why splitting it in two solves the problem... the first part may have rows all in the lower range of characters (eg under 50), and when it processes the second part it does find the larger rows when looking at a sample to estimate column width and sets it accordingly. If both sizes are smaller than the destination column, there will be no issue with the actual INSERT.

You can set the OutputColumnWidth to a more suitable number.

Joe Shark
  • 688
  • 4
  • 9
0

Can someone please confirm / deny if there is a cell limit (or any other kind of size restriction) when importing flat files into SQL Server?

You are not hitting some sort of limit in SQL Server. It's something with your files or the tool you're using to load them.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • How are the csv files formatted? comma separated or tab or semicolon? strings enclosed by double or single quote? how do you treat the string encloser if you find it within a string - i.e what would the string `Harry's Bar` look as a string literal with single quotes as the string encloser? – marcothesane Feb 20 '21 at 15:47
  • Do you have the first line with column names? Can you paste the first 10 lines of an exemplary CSV file? – marcothesane Feb 20 '21 at 15:48
  • Column names are all the same across files, except some have additional _1, _2, etc. – smallnorthumbrianvillage Feb 20 '21 at 15:58
0

Microsoft Excel has a character limit of 32,767 characters, you can read more about this limit here.

https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3

You can find the length of characters with the LEN() function. As for SQL Server, you can set your data type as VARCHAR and get it up to 8,000 characters. You can see some specs here.

https://www.sqlservertutorial.net/sql-server-basics/sql-server-nvarchar/

I'd suggest that you try importing a small sample of data first; save a copy of your file with just a few dozen rows of data. Make sure it works, and then try a few more times with larger and larger data sets, going from Excel to SQL Server. Whatever the error is, it should quickly become apparent to you, I think, as you go from small sets of data to larger ones.

ASH
  • 20,759
  • 19
  • 87
  • 200
  • Hi, yes, I've had that error with csvs from another source. In that case the cell overflow caused the data to break across rows. In this case, most nvarchar cells are in the 1-2k range. In the former case, I found the best solution was *not* to open the files in excel. That doesn't help in this instance. – smallnorthumbrianvillage Feb 21 '21 at 07:56
  • Opening Excel can introduce some weirdness. For instance, if the forma in Excel is not text, leading zeros of an ID, for instance can get dropped off. If you drop zeros and then try to lookup something by ID, all kinds of things will be thrown off. I open open these kinds of files with Windows Notepad, or Notepad++. View these files for piece of mind, then close the file and don't save it. Maybe that will help. – ASH Feb 21 '21 at 17:26
  • I generally think you're right on this, but your advice "don't open in excel" has also just solved another, unrelated, issue I was about to post a long question about.... when I realised "all sorts of weirdness" was happening specifically because I was reviewing the downloaded file in Excel! – smallnorthumbrianvillage Feb 22 '21 at 10:44