October 28, 2015 at 11:29 pm
Background:
I am trying to insert csv data into sql table using Powershell script(Import-CSVtoSQL.ps1) given on following web page:
https://gallery.technet.microsoft.com/scriptcenter/Import-Large-CSVs-into-SQL-216223d9
Issue:
This script is failing when I try to import null values (The null value is represented as blank in CSV file) from CSV file to datetime type column in a sql table.
it's throwing following error:
Error Message:
System.InvalidOperationException: The given value of type String from the data source cannot be converted to type datetime of the specified target column. ---> System.FormatException: Failed to convert parameter value from a String to a DateTime. --->
System.FormatException: String was not recognized as a valid DateTime.
at System.DateTime.Parse(String s, IFormatProvider provider)
at System.Convert.ToDateTime(String value, IFormatProvider provider)
at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)
at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed)
--- End of inner exception stack trace ---
Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
October 29, 2015 at 1:13 am
any pointer would be a great help.
Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
October 29, 2015 at 5:13 am
psingla (10/28/2015)
Background:I am trying to insert csv data into sql table using Powershell script(Import-CSVtoSQL.ps1) given on following web page:
https://gallery.technet.microsoft.com/scriptcenter/Import-Large-CSVs-into-SQL-216223d9
Issue:
This script is failing when I try to import null values (The null value is represented as blank in CSV file) from CSV file to datetime type column in a sql table.
it's throwing following error:
Error Message:
System.InvalidOperationException: The given value of type String from the data source cannot be converted to type datetime of the specified target column. ---> System.FormatException: Failed to convert parameter value from a String to a DateTime. --->
System.FormatException: String was not recognized as a valid DateTime.
at System.DateTime.Parse(String s, IFormatProvider provider)
at System.Convert.ToDateTime(String value, IFormatProvider provider)
at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)
at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed)
--- End of inner exception stack trace ---
I am facing same issue not only for datetime datatype but also for decimal and int
Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
October 29, 2015 at 6:59 am
You are going to need to explicitly specify the columns most likely to deal with the conversion issue. Chrissy's script does not handle that, you can check the comments from her blog post noted on Script Center page for an example and a bit more info.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply