SQL data output formatting

PowerShell Help
1

I am querying and AS400 DB2 database and getting correct output but I need som help with formating my output.

$sql = 'select 
idsuno as "supplier",
idsunm as "name",
iidtcd as "customer_date_code"
from M3EPRD.CIDMAS
left join rerwer.CIDVEN on idcono=iicono and idsuno=iisuno
where IDCONO=1 -- input CONO
and IDSUTY = 0 and IDSTAT=20'

$dsn = "sys_prd"
$path = "suppliers_"

function get-result ($sql, $dsn)
{
$Datatable = New-Object System.Data.DataTable

$connection = New-Object -TypeName System.Data.Odbc.OdbcConnection
$connection.ConnectionString = "DSN=$dsn;UserID=user;Password=passwd;"
trap
{
Write-Error "Cannot connect to SQL server";
exit
}
$connection.Open()

$command = New-Object -TypeName System.Data.Odbc.Odbccommand
$command.Connection = $connection
$command.CommandText = $sql
$Command.CommandTimeout = 0
$reader = $command.ExecuteReader()
$Datatable.Load($Reader)

$connection.Close();
return $Datatable
}

Write-Output "Quering DSN $dsn for data"
$res = get-result -sql $sql -dsn $dsn | ConvertTo-Csv -NoTypeInformation -Delimiter ';'
$res
Write-Output "Removing double quotes"
$res = $res.Replace("`"", "")
Write-Output "Saving result to disk"
$res | Set-Content -path $path

This code gives me this output.

Example data:

supplier;name;customer_date_code
00000001  ;MALL                                ;2
00000200  ;VÄLJ LEVERANTÖR                     ;2
00115000  ;A-PLAST AB                          ;2
00160000  ;ABAT AB                             ;2
00179000  ;ABB AB                              ;2
00214000  ;ABC VERBINDUNGSTECHNIK GMBH O CO.KG ;2

My problem is that I don’t want the fixed length on my data rows. I want all rows to look like the header row. Any suggestions how i can achieve that? I tried to replce whitespaces but that removes whitespaces inside name column.

2

You can use the .trim() method of a string.

’  white space    at the start and at the end  '.trim()

3

Try using -Replace





$res -Replace( '\s+', ' ')

 

4

I can’t get replace to work whitout removing all spaces inside my names.

To use trim() I need to split every row in different colums.

Sample row with double qoutes to illustrate.

"00000200 ";"VÄLJ LEVERANTÖR ";“2”

5

THere might be more sophisticated solutions and I think it would be probably more efficient to avoid this problem on the “SQL side” but if it’s necessary … 

(‘"00000200  ";"VÄLJ  LEVERANTÖR  “;” 2 "’ -split ‘;’ -replace ‘"’ -replace ‘\s+’,’ ’ ).trim() -join ‘;’

6

The 80’s called, they want their technology back. Ha! First here are some interesting finds:

I’d done a project years ago to interface with AS400, but I don’t recall needing to parse the returns, but as Olaf said it’s probably better to do trimming SQL, something like:

$sql = @"
select 
     RTRIM(idsuno) as "supplier",
     RTRIM(idsunm) as "name",
     RTRIM(iidtcd) as "customer_date_code"
from M3EPRD.CIDMAS
left join rerwer.CIDVEN on idcono=iicono and idsuno=iisuno
where IDCONO=1 -- input CONO
and IDSUTY = 0 and IDSTAT=20
"@

In the code you posted, you are also only creating a data table, not a PSObject, which is what any conversion commands expect such as Export-CSV. The second link has this little

$da = New-Object system.Data.odbc.odbcDataAdapter($cmd)

[void]$da.fill($ds)

$conn.close()

$result = $ds.tables[0]

This is getting the data table and converting it to a PSObject. Try one or both of those and see if the results look better.

7

Thanks for all valuable input.

I tested the code from reddit to get a psobject. Now I can you trim. Next step is to figure out how to get i back to a format suitable for a exportto-csv.

$userName = 'user'
$password = 'passwd'
#Provide IP of your DB2 server
$system = "erp_prd"

$query = 'select
idsuno as "supplier",
idsunm as "name",
iidtcd as "customer_date_code"
from M3EPRD.CIDMAS
left join M3EPRD.CIDVEN on idcono=iicono and idsuno=iisuno
where IDCONO=1 -- input CONO
and IDSUTY = 0 and IDSTAT=20'

#The connection use the default port
$conn = New-Object -type System.Data.Odbc.OdbcConnection
$conn.ConnectionString = "DSN=$system;Uid=$userName;Pwd=$password;"

$conn.Open()
$cmd = New-Object System.Data.Odbc.OdbcCommand($query, $conn)
$cmd.CommandTimeout = 15
$ds = New-Object system.Data.DataSet
$da = New-Object system.Data.odbc.odbcDataAdapter($cmd)

[void]$da.fill($ds)
$conn.close()
$result = $ds.tables[0]

foreach ($row in $result)
{
	$row.supplier.trim()
	$row.name.trim()
}

 

8

Finaly I found the correct solution. You can run trim inside the SQL query to get desired result.

select 
trim(idsuno) as "supplier",
TRIM(idsunm) as "name",
trim(iidtcd) as "customer_date_code"
from M3EPRD.CIDMAS
left join M3EPRD.CIDVEN on idcono=iicono and idsuno=iisuno
where IDCONO=1 -- input CONO
and IDSUTY = 0 and IDSTAT=20
9

Not sure if this applies in your case. I installed Microsoft Sql Server Management Studio, and just use Invoke-Sqlcmd to submit an sql query, and get back an DataRow object array. There’s an SQLSERVER: drive too, but that’s slower to search.

# search jobs, like:
# sql reboot

# event_folder table has folder_id, parent_id, name
# event table has event_id, folder_id, name, description, etc

$jobname = $args[0]

Invoke-Sqlcmd -database express -query "select 
  event.name as job, event_folder.name as folder, b.name as parent
from 
  event 
inner join event_folder on event.folder_id = event_folder.folder_id
inner join event_folder b on event_folder.parent_id = b.folder_id
where 
  event.name like '%$jobname%';"