0

I have a csv file which has one column named PurchaseorderID and some numbers below it. I have a PS script which is supposed to fetch each row from that file and complete a SQL query and run it. However, when I run below script:-

$csv = Import-Csv 'c:\path\to\file.csv'

Foreach ($row in $csv) {
    Invoke-Sqlcmd -ServerInstance '.\SQL' -Database DB01 -Query "INSERT
    into  BuyerWorksheetCentralRepository.[po].[PurchaseOrderMessage] 
    values (
        $($row.PurchaseOrderID), 
        4, 
        'Timeout waiting for mainframe to send response message', 
        getdate(), 
        'deb00535'
    )"
}

I get below output:-

insert into  BuyerWorksheetCentralRepository.[po].[PurchaseOrderMessage] 
values (, 4, 'Timeout waiting for mainframe to send response message',getdate(),'deb00535'

There no output, before "4" where its supposed to be.

Now when I remove the "PurchaseorderID" relation, it does output the numbers in the lines, but with a "H1=" induced header.

insert into  BuyerWorksheetCentralRepository.[po].[PurchaseOrderMessage] 
values (@{H1=PurchaseOrderID}, 4, 'Timeout waiting for mainframe to send response message',getdate(),'deb00535')

Not sure where I am doing wrong. Please help.

This is the code through which I generate the CSV.

$ServerName = "SQLGBUYPROD"
$DatabaseName = "BuyerWorksheetCentralRepository"
$Query =  "SELECT PurchaseOrderID
FROM [BuyerWorksheetCentralRepository].[po].[PurchaseOrder]
where DivisionNum IN (8,10,28)
and ponumprefix = 0 -- Root
and inprocessstatusid IN (4) --- In Transmission
and updatetime > '2020-10-08 00:00:00.000' AND updatetime < DATEADD(hour, -1, GETDATE())
order by updatetime desc"

#Timeout parameters
$QueryTimeout = 120
$ConnectionTimeout = 30

#Action of connecting to the Database and executing the query and returning results if there were any.
$conn=New-Object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerName,$DatabaseName,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()
$cmd=New-Object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables | Out-File "D:\Chayan\POmiss\st6po.csv" -Append
Chayan Chakraborty
  • 13
  • 4
  • Please update your question so it uses code formatting (4 space indents) - it makes the code in your question much easier to read and preserves your code's formatting. – Bill_Stewart May 27 '21 at 18:04
  • 1
    It looks to me like your data file isn't formatted correctly. Look at the data that was loaded into `$csv` and the CSV file itself. Are there column headers on the first line of the file, or is it a blank line? – Bacon Bits May 27 '21 at 19:10
  • @BaconBits , It has a empty row at the top. The CSV itself is a result of another PS code, which basically runs a SQL query. I have updated the code to generate the CSV file in my original question. Here is my resultant CSV file. https://drive.google.com/file/d/1K5ywqLtAPMuCEFkVKsBbztLh29GT4SGY/view?usp=sharing – Chayan Chakraborty May 28 '21 at 15:40
  • Yeah, that is not a valid CSV file layout. A CSV file should have the first line of the file as the headers, and data beginning on the second line. That's what `Import-Csv` expects. You can also choose to have no headers in the file, in which case the first line should be data and specify the column headers with the `-Headers` parameter of `Import-Csv`. – Bacon Bits May 28 '21 at 16:15
  • @BaconBits can you please help with how should I edit my code to accommodate that file? The code which generates the CSV is just a SQL query which outputs the result from that SQL Table. Can the file be formatted before we can start fetching the data. – Chayan Chakraborty May 28 '21 at 17:14
  • Try changing `$ds.Tables | Out-File "D:\Chayan\POmiss\st6po.csv" -Append` to `$ds.Tables[0] | Export-Csv "D:\Chayan\POmiss\st6po.csv" -Append -NoTypeInformation`. You'll also need to delete the CSV to get the system to reset the format. – Bacon Bits May 28 '21 at 17:26
  • I don't recall if Powershell v2 has an `-Append` parameter for `Export-Csv`. It may have been a version 3 addition. But... you really, really should not be using Powershell v2 in 2021. – Bacon Bits May 28 '21 at 17:28

0 Answers0