value of type String from the data source cannot be converted to type Real

Question

I have a csv file that has the first 7 columns as varchar (string), while the rest of them are 'Real' data type. I believe the .NET equivalent of Real is Single.

Anyways, i tried the following based on a suggestion:

,(Import-Csv -Path ".\Fact.csv") | Write-SqlTableData -ServerInstance "server.db.com" -DatabaseName
 "TestDB" -SchemaName "dbo" -TableName "Fact" -Force

and i received this (expected) error:

Write-SqlTableData : The given value of type String from the data source cannot be converted to type real of the specified target column.

The question is: How to make this utility accept Real datatypes?

csv file sample:

BGSC,MYTrim Code,CC,OC,Ctry Of Prod,Report Year,Scenario,Volume,Gross Revenue ,SA ,ANSR, TM  ,TL,"P and Warranty, others",CM ,Total Variable,Variable Profit , EBIT  
TOTAL_CKD,TOTAL_MODEL_YEAR_AND_GLOBAL_TRIM,COS_AU,4F80, ,2015,Current, -   , -   , -   , -   , -   , -   , -   , -   , -   , -   ," (136,748)"
CXXXXXXXXXX,TOTAL_MODEL_YEAR_AND_GLOBAL_TRIM,COS_AU,4F80, ,2013,Current, -   , -   , -   , -   , -   , -   , -   , -   , -   , -   ," 1,647 "
CXXXXXXXXXX,TOTAL_MODEL_YEAR_AND_GLOBAL_TRIM,COS_AU,4F80, ,2014,Current, -   ," 16,098 ", (0)," 16,098 ", 1 , -   , 8 ," 16,090 ", -   ," 16,090 "," 61,219 "
CXXXXXXXXXX,TOTAL_MODEL_YEAR_AND_GLOBAL_TRIM,COS_AU,4F80, ,2015,Current, -   ," (12,496)", -   ," (12,496)"," (13,386)", -   , -   , 890 , -   , 890 , (192)

Answer 1

you need to cast the type using Select-object

see this article for details

https://www.michev.info/Blog/Post/772/typecasting-for-import-csv

Answer 2

The following code will convert your "numbers" to integers. I defined a function to sanitize your number strings. Then I used Select-Object to redefine some of your properties:

function Sanitize-String {
    param(
        $String
    )

    if ($String -match '^-\s*$') {
        return "0"
    } else {
        return (($String -replace '[\"\s\,]', '') -replace '\((\d+)\)', '-$1')
    }
}

Import-Csv -Path ".\Fact.csv" | Select-Object -Property BGSC, 
    'MYTrim Code', 
    CC, 
    OC, 
    'Ctry Of Prod', 
    'Report Year', 
    Scenario, 
    @{Name='Volume';Expression={[int](Sanitize-String -String $_.Volume)}}, 
    @{Name='Gross Revenue';Expression={[int](Sanitize-String -String $_.'Gross Revenue ')}},
    @{Name='SA';Expression={[int](Sanitize-String -String $_.'SA ')}},
    @{Name='ANSR';Expression={[int](Sanitize-String -String $_.ANSR)}},
    @{Name='TM';Expression={[int](Sanitize-String -String $_.'TM ')}},
    @{Name='TL';Expression={[int](Sanitize-String -String $_.'TL')}},
    @{Name='P and Warranty, others';Expression={[int](Sanitize-String -String $_.'P and Warranty, others')}},
    @{Name='CM';Expression={[int](Sanitize-String -String $_.'CM ')}},
    @{Name='Total Variable';Expression={[int](Sanitize-String -String $_.'Total Variable')}},
    @{Name='Variable Profit';Expression={[int](Sanitize-String -String $_.'Variable Profit ')}},
    @{Name='EBIT';Expression={[int](Sanitize-String -String $_.'EBIT ')}}

It works, but to be honest, your csv file is a mess. It is possible to have properties including space characters. But do you really need (multiple) leading or trailing space characters in a property name? Also, try to insert numbers as raw as possible. Don't insert quotes, brackets, spaces around your numbers. Put in 0 directly instead of -.

---

A more automated way:

function Sanitize-String {
    param(
        $String
    )

    if ($String -match '^-\s*$') {
        return "0"
    } else {
        return (($String -replace '[\"\s\,]', '') -replace '\((\d+)\)', '-$1')
    }
}

$csvContent = Import-Csv -Path ".\Fact.csv" 

$results = @()
foreach ($line in $csvContent) {
    $i = 0
    $result = New-Object -TypeName psobject
    foreach ($prop in $line.PSObject.Properties) {
        if ($i -lt 7) {
            $result | Add-Member -MemberType $prop.MemberType -Name $prop.Name -Value $prop.Value
        } else {
            $result | Add-Member -MemberType $prop.MemberType -Name $prop.Name -Value ([int](Sanitize-String -String $prop.Value))
        }
        $i++
    }
    $results += $result
}
$results # pipe to your DB for example

Answer 3

I agree with Thomas that your csv file is in really bad shape. The headers are surrounded with whitespace, aswell as the data. Some of the numeric values are surounded by brackets, while others are not, and may or may not include a thousand separator character , (in your locale that is).

I would propose something to clean that all up like this:

$csv = Import-Csv -Path ".\Fact.csv"
# clean up the CSV
# get the header names from the first row, trim whitespaces and surround with quotes
$headers = ($csv[0].PSObject.Properties.Name.Trim()  | ForEach-Object { '"{0}"' -f $_ }) -join ','

# loop through all data rows in the csv. (each row is a PSObject with properties)
$data = foreach($item in $csv) {
    # loop through all properties (fields) in the item
    $row = foreach($field in $item.PSObject.Properties.Value) {
        '"{0}"'-f ($field -replace '[\s(),]' -replace '-', '0')
    }

    $row -join ','
}
# recombine the headers with the data
$newCsv = $headers, $data

# output on screen if you like
$newCsv | Format-Table -AutoSize

# save to a new file and use that for writing to the sql table
$newCsv | Export-Csv -Path ".\Fact2.csv" -NoTypeInformation
,(Import-Csv -Path ".\Fact2.csv") | Write-SqlTableData -ServerInstance "server.db.com" -DatabaseName "TestDB" -SchemaName "dbo" -TableName "Fact" -Force

This is what the CSV will look like after cleaning up:

"BGSC","MYTrim Code","CC","OC","Ctry Of Prod","Report Year","Scenario","Volume","Gross Revenue","SA","ANSR","TM","TL","P and Warranty, others","CM","Total Variable","Variable Profit","EBIT"
"TOTAL_CKD","TOTAL_MODEL_YEAR_AND_GLOBAL_TRIM","COS_AU","4F80","","2015","Current","0","0","0","0","0","0","0","0","0","0","136748"
"CXXXXXXXXXX","TOTAL_MODEL_YEAR_AND_GLOBAL_TRIM","COS_AU","4F80","","2013","Current","0","0","0","0","0","0","0","0","0","0","1647"
"CXXXXXXXXXX","TOTAL_MODEL_YEAR_AND_GLOBAL_TRIM","COS_AU","4F80","","2014","Current","0","16098","0","16098","1","0","8","16090","0","16090","61219"
"CXXXXXXXXXX","TOTAL_MODEL_YEAR_AND_GLOBAL_TRIM","COS_AU","4F80","","2015","Current","0","12496","0","12496","13386","0","0","890","0","890","192"

and there should be no problem converting these values to type 'Real'

^{A CSV file does not determine a value type. It only has headers and data fields. When imported, all data is of type String. The Write-SqlTableData cmdlet (tries to) convert these values into the data type as defined in the table}