0

So I am uploading csv data from a specific file in a folder on my desktop into a SQL Server database using Powershell and some rows will upload just fine however the majority are returning the following error

"Exception calling "ExecuteNonQuery" with "0" argument(s): "Conversion failed when converting date and/or time from character string." At line:3 char:5

  • $sqlCommand.ExecuteNonQuery()

  • ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    • CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    • FullyQualifiedErrorId : SqlException"

I know it's an issue converting one of the columns containing a date however I'm not sure how to go about fixing it. Any help is appreciated. The script is as follows:

    # Define variables
$serverName = "TDALYW10"
$databaseName = "ScriptTest"
$tableName = "VehicleStock"
$csvPath = "C:\Users\TDaly\Desktop\PowerShellTest2\vehicleStock.csv"

# Create a new SQL Server connection
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server=$serverName;Database=$databaseName;Integrated Security=True"

# Open the SQL Server connection
$sqlConnection.Open()

# Create a new SQL Server command
$sqlCommand = New-Object System.Data.SqlClient.SqlCommand
$sqlCommand.Connection = $sqlConnection

# Read the CSV file
$csv = Import-Csv $csvPath

# Loop through each row in the CSV file and insert it into the SQL Server table
foreach ($row in $csv) {
    $sqlCommand.CommandText = "INSERT INTO $tableName (branch, stockNo, reg, category, make, model, variant, buyInSP, purchaseInvDate, dateIn, purchaseInvRef, odometer, clock, qual, [Base & Delivery], Overallowance, WriteDown, Sublet, PDI, Accessory, Bonus, [Motor Tax], [House Charge], Finance, Other, Previous, totalCost, purchaseAmt, purchaseVAT, retail, trade, VRT, VIBE, fuel, cc, body, colour, trim, owners, year, regDate, class, transm, [new/used], daysInStock, lastMOT, nextMOT) VALUES ('$($row.branch)', '$($row.stockNo)', '$($row.reg)', '$($row.category)', '$($row.make)', '$($row.model)', '$($row.variant)', '$($row.buyInSP)', '$($row.purchaseInvDate)', '$($row.dateIn)', '$($row.purchaseInvRef)', '$($row.odometer)', '$($row.clock)', '$($row.qual)', '$($row.'Base & Delivery')', '$($row.Overallowance)', '$($row.WriteDown)', '$($row.Sublet)', '$($row.PDI)', '$($row.Accessory)', '$($row.Bonus)', '$($row.'Motor Tax')', '$($row.'House Charge')', '$($row.Finance)', '$($row.Other)', '$($row.Previous)', '$($row.totalCost)', '$($row.purchaseAmt)', '$($row.purchaseVAT)', '$($row.retail)', '$($row.trade)', '$($row.VRT)', '$($row.VIBE)', '$($row.fuel)', '$($row.cc)', '$($row.body)', '$($row.colour)', '$($row.trim)', '$($row.owners)', '$($row.year)', '$($row.regDate)', '$($row.class)', '$($row.transm)', '$($row.'new/used')', '$($row.daysInStock)', '$($row.lastMOT)', '$($row.nextMOT)')"
    $sqlCommand.ExecuteNonQuery()
}

# Close the SQL Server connection
$sqlConnection.Close()
```
Improve this question
9
  • "Conversion failed when converting date and/or time from character string." The error is telling you the problem here; you have an invalid date (and time) value. perhaps it's being stored in an ambiguous date format? Perhaps it really isn't a valid date (such as 2023-02-29 or 2021-19-17). We have no sample data, so we don't know which. If it's just ambiguous, you'd likely be better off loading into a staging table, where all/most of your columns are string based data types, and then you can transform the data from that table to the production table. Commented May 3, 2023 at 11:31
  • See: Change date format in CSV using PowerShell Commented May 3, 2023 at 11:32
  • Apologies, it turns out I was just being an idiot and forgot to correctly set the data type in the SQL table itself. The only issue I'm running into now is when a quotation mark appears in my data for example in the Variant column sometimes there is quotation marks and it is producing the error "Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near 'd'. Unclosed quotation mark after the character string ', ', ')'." Commented May 3, 2023 at 11:49
  • 1
    this is because you're building the query from raw data. It's usually bad idea, because a file can contain rows with columns like DROP DATABASE oops Commented May 3, 2023 at 12:21
  • Is there an easy way around this? Commented May 3, 2023 at 12:25
Related questions
3327
How to add a column with a default value to an existing table in SQL Server?
1801
How can I delete using INNER JOIN with SQL Server?
1698
How can I do an UPDATE statement with JOIN in SQL Server?
Load 6 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.