Error during load and SQL helper exception in ExecuteNonQuery
I'm getting this error when I try to import data from a CSV file:
Error during load, check the logfile for details The CSV appears to be corrupt near record '9' field '15 at position '2800'.
It looks like it has problems when it encounters this string:
User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; WOW64; Trident/4.0; MathPlayer 2.10d; User-agent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; http://bsalsa.com) ; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; FDM)
This is somewhat understandable, because of the ";" characters. But this part is surrounded by double quotes in the csv as per spec.
When trying to set the Text Indicator to " (double quotes), it gives me the "SQL helper exception in ExecuteNonQuery" error on the last 12 records. The first 8 are ok.
Is there a way to find out which SQL Query it has problems with?
I think the csv fields areto big to be parsed thorugh the external CSV Parser I'm using. I suggest you import it first into a sql server table and use that as the datasource. You have some strange data in the columns is that on purpose?
Well the columns are not strange, but you have sql statements included in the content. It could be that SQL server ignores that during the import. But you could try using an xml file. Make sure you use elements. Attributes don;t work (yet)'.
I have plans to improve the xml DataAdapter, but since xml is so flexible it's also a lot harder to use Therefore I want to create custom DataAdapters for specific xml file formats suchs as RSS and blogml also. These formats are based on a standard and then I can easily exttracty attributes and elements from the xml file.
I found out what makes the errors both in the CSV and XML. The date that I extracted from the nodes is not SQL compatible, so I had to use the following toString methid when extracting them:
Error during load and SQL helper exception in ExecuteNonQuery
I'm getting this error when I try to import data from a CSV file:
It looks like it has problems when it encounters this string:
This is somewhat understandable, because of the ";" characters. But this part is surrounded by double quotes in the csv as per spec.
When trying to set the Text Indicator to " (double quotes), it gives me the "SQL helper exception in ExecuteNonQuery" error on the last 12 records. The first 8 are ok.
Is there a way to find out which SQL Query it has problems with?
Here is the CSV file in question.
Hi Ove,
I think the csv fields areto big to be parsed thorugh the external CSV Parser I'm using. I suggest you import it first into a sql server table and use that as the datasource. You have some strange data in the columns is that on purpose?
Hope this helps you,
Richard
Strange data columns?
Maybe... It's all the posts from my blog which I want to import to a new umbraco setup using CMSImport, so yes, the csv fields are really large.
Will I encounter the same problem with the XML parser?
Well the columns are not strange, but you have sql statements included in the content. It could be that SQL server ignores that during the import. But you could try using an xml file. Make sure you use elements. Attributes don;t work (yet)'.
Cheers,
Richard
Yes, that makes sense. One of the blog posts includes SQL code in C#. I will try with an XML file and report back with how ut goes.
Is there a table in the umbraco database that includes the content i need to copy my blog posts to another umbraco install?
It worked with an XML file, but I had to strip all attributes from the xml, otherwise it would not validate the xml file.
Great that it worked for you!
I have plans to improve the xml DataAdapter, but since xml is so flexible it's also a lot harder to use Therefore I want to create custom DataAdapters for specific xml file formats suchs as RSS and blogml also. These formats are based on a standard and then I can easily exttracty attributes and elements from the xml file.
Cheers,
Richard
It would be wonderful if the error messages were stored somewhere so I could find out what is wrong with the SQL Query.
I found out what makes the errors both in the CSV and XML. The date that I extracted from the nodes is not SQL compatible, so I had to use the following toString methid when extracting them:
node.CreateDate.ToString("yyyy'-'MM'-'dd' 'HH':'mm':'ss'.'fff")After that, it worked like a charm... :)
Thanks Ove,
I will create a Custom FieldAdapter for Version 1.1 to solve this issue.
Thanks,
Richard
is working on a reply...