The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated. I've been trying to import just one row. I keep getting formatting errorrs with ints and dates.
When I try to import a date I get the following:
Error while importing data for property 'lastOrderDate' :The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.
lastOrderDate Last Order Date
Advanced settings Date format dd/MM/yy HH:mm
When I try to import currency I get this:
Error while importing data for property 'totalValueOfOrders' :Conversion failed when converting the nvarchar value '740.95' to data type int.
You can specify a date format when mapping the field using the advanced mapping icon. then specify something like dd/MM/yy hh:mm as the format string to format the dates. Using the preview button on that field it will display how it will be converted. The format of the preview must match the format of the value in your datasource then CMSImport knows how to convert this value.
If you want to import the value 740.95 to the totalValueOfOrders field you need to change the datatype of that alias to text. The number field of Umbraco can only have integer values, 740 will work, 741 also but 740.xx never since that is a double.
Should not make any difference both csv and Excel should work. And there is no double datatype in the cms that's why you should use a text datatype for that. When you add regular expression validation you can make sure only double like values are being inserted that you can parse on the fron-end to the real double value.
Import of datetime or int causing error
Hi There,
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated. I've been trying to import just one row. I keep getting formatting errorrs with ints and dates.
When I try to import a date I get the following:
Error while importing data for property 'lastOrderDate' :The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.
lastOrderDate
Last Order Date
Advanced settings
Date format dd/MM/yy HH:mm
When I try to import currency I get this:
Error while importing data for property 'totalValueOfOrders' :Conversion failed when converting the nvarchar value '740.95' to data type int.
<!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} .font5 {color:windowtext; font-size:8.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl65 {font-size:8.0pt; border:.5pt solid black; white-space:normal;} .xl66 {font-size:8.0pt; mso-number-format:"General Date"; border:.5pt solid black; white-space:normal;} .xl67 {font-size:8.0pt; mso-number-format:"\0022$\0022\#\,\#\#0\.00\;\[Red\]\\-\0022$\0022\#\,\#\#0\.00"; border:.5pt solid black; background:#CCFFFF; mso-pattern:auto none; white-space:normal;} .xl68 {font-size:8.0pt; mso-number-format:Standard; border:.5pt solid black; white-space:normal;} .xl69 {color:#0000D4; text-decoration:underline; text-underline-style:single; border:.5pt solid black; white-space:normal;} ruby {ruby-align:left;} rt {color:windowtext; font-size:8.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-char-type:none; display:none;} -->
What is the best format to use? I have used .csv and .excel but none of them are working.
Do I need to convert the excel db to access db ? Any pointers would be great.
Sean
Hi Sean,
You can specify a date format when mapping the field using the advanced mapping icon. then specify something like dd/MM/yy hh:mm as the format string to format the dates. Using the preview button on that field it will display how it will be converted. The format of the preview must match the format of the value in your datasource then CMSImport knows how to convert this value.
If you want to import the value 740.95 to the totalValueOfOrders field you need to change the datatype of that alias to text. The number field of Umbraco can only have integer values, 740 will work, 741 also but 740.xx never since that is a double.
Hope this helps,
Richard
Hi Richard, I did specify the date format but the currency value is in the same row. Would this have any affect?
I'll try the date format again. Which format is easier xls or csv format?
Also, How can I create a datatype of double in the CMS?
Sean
Hi Sean,
Should not make any difference both csv and Excel should work. And there is no double datatype in the cms that's why you should use a text datatype for that. When you add regular expression validation you can make sure only double like values are being inserted that you can parse on the fron-end to the real double value.
Hope this helps,
Richard
is working on a reply...