2

I am receiving this error when trying to do a bulk copy to my SQL Server db in my ASP.NET MVC project

The given value of type String from the data source cannot be converted to type nvarchar of the specified target column.

I have looked through similar issues and found 1 which helped me pinpoint my issue: c# can't insert data table using sqlbulkcopy. I would like to point out the answer given by KatieAmber:

I have another answer for this question, maybe it can come in handy for someone. If you have a String which contains " (quotation mark), you get this error message too. At least I just got this error message, and I searched for the wrong row, and I found this. As I removed the quotation marks, the error disappeared.

So when looking at the data in my .csv I noticed that the GPS coordinates were all enclosed in quotes, like this "-29.721447,31.066920". Of course in this case, I immediately realised that I cannot remove the quotes as this single lat, long value would then become 2 separate values, separated by the comma.

So my question is, is there a possible way to work around this? Could I possibly patch my SqlBulkCopy to accept the quoted string? Or maybe specify another separator?

Community
  • 1
  • 1
AbdulG
  • 720
  • 5
  • 16
  • 3
    I have never seen `SqlBulkCopy` choke on a quote - `SqlBulkCopy` is raw TDS, and TDS knows how columns work (the T is "tabular"); something seems very wrong here... what is the *source* of this data? are you sure this isn't simply a column mismatch (type, length) or a glitch in the *reader*? – Marc Gravell Jul 03 '14 at 08:34
  • 1
    Did you check [http://stackoverflow.com/a/18149263/2655508](http://stackoverflow.com/a/18149263/2655508) – Heslacher Jul 03 '14 at 08:44
  • 1
    What length is your nvarchar column? Are you trying to insert a string that is longer than the maximum length? – GarethD Jul 03 '14 at 08:45
  • @heslacher I have seen that. the answer doesn't help me. – AbdulG Jul 03 '14 at 08:46
  • @MarcGravell the source is an excel file that had been converted to csv. I can confirm that there is no mismatch in the columns. How would I identify a glitch in the reader? – AbdulG Jul 03 '14 at 08:50
  • @GarethD it is definately not longer that the maximum specified in sql – AbdulG Jul 03 '14 at 08:50
  • I am going to attempt using a different separator, will notify here with the results – AbdulG Jul 03 '14 at 08:53
  • 1
    @AbdulG can you simply tell us the size of the column? that would really really help. How are you reading the CSV? what API are you using? are you filling a DataTable? or are you using an IDataReader? if so: which one? – Marc Gravell Jul 03 '14 at 08:53
  • 1
    @MarcGravell the size is nvarchar(30), and I am filling a DataTable. Also the file is read in with the StreamReader – AbdulG Jul 03 '14 at 08:57
  • 1
    @AbdulG right; so, if the CSV reader has done its job correctly, the final actual value there doesn't include quotes - it is the string containing the value `-29.721447,31.066920`; if it was me, I would check the DataTable and find the maximum length (over the rows) for each string-based column in turn. I suspect the error is not where you think it is. – Marc Gravell Jul 03 '14 at 09:00
  • 1
    Something like: http://pastie.org/9349535 – Marc Gravell Jul 03 '14 at 09:03
  • @MarcGravell I see, maybe I have just been lead astray by that answer I mentioned. I'll look into your suggestion. – AbdulG Jul 03 '14 at 09:03

0 Answers0