1

As my first PS project, I am trying to loop through a large CSV file (>25k lines, circa 30Mb) and truncate the ‘Query’ field if it’s character count is larger than a certain value (i.e. larger than Excel can handle).

I have this following code:



Function Get-FileName($initialDirectory)
{
    [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
    
    $OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
    $OpenFileDialog.initialDirectory = $initialDirectory
    $OpenFileDialog.filter = "CSV (*.csv)| *.csv"
    $OpenFileDialog.ShowDialog() | Out-Null
    $OpenFileDialog.filename
}

$inputfile = Get-FileName "C:\Logs" 

$csv = Import-CSV $inputfile

$csv.Query | ForEach-Object -Process {
    if((Measure-Object -Character) -gt 30000) 
        {
            $csv.Query = $csv.Query.String(30000)
        }
    }
Export-Csv -Path $inputfile -NoTypeInformation

However I am getting the following error:
“Cannot compare “Microsoft.PowerShell.Commands.TextMeasureInfo” because it is not IComparable.”
on the Measure-Object cmdlet
How can I check to see if the length of the Query is less than 30000?
Bonus question: Is this the most efficient way of reading the file?

Thanks!

3 Spice ups
2 
$string="123456"
$string[0..3] -join ""

Try this… It’ll return the first 4 characters only from the $string

1 Spice up
3

OK, I have tweaked the code slightly to:

$csv.Query | ForEach-Object -Process {
    $count = $_.length
    if ($count -gt 30000) 
    {
            $truncQuery = $_[0..30000] -join ""
            $csv.Query = $truncQuery
        }
    }

However, I am now getting an error that the Query property cannot be found on the object when I try to set the value to $truncQuery

4

post the first 3-4 lines of your csv file ( sanitize as needed ). Need to know if the csv file contains a header line or not…

@iainsmart

5

It’s a bit tricky to sanitize the CSV, but it does have a header line:

"Event Date and Time","User","OS Host Names","Source IP","Destination IP","Server Group","Service","Operation","Object","Query","SQL Exception Occurred","Source Application"

The $_ variable in the ForEach_Object loop is picking up the correct value for the Query field

6

Give me one or 2 fake lines… and 'll see what i can come up with on my machine later

Does the total length of one line has to be 30000chars or less or just the query entry?

7

It’s just the Query entry (Excel has a limit of 32,767 characters per cell, but even using smaller numbers has the same issue!)

Example csv:

"Event Date and Time","User","OS Host Names","Source IP","Destination IP","Server Group","Service","Operation","Object","Query","SQL Exception Occurred","Source Application"
"09/29/2016  2:30:13 AM","User1","","aaa.bbb.ccc.ddd","aaa.bbb.ccc.ddd","Server Group 1","Service 1","Update","sysjobactivity","DECLARE @nextScheduledRunDate DATETIME SET @nextScheduledRunDate = msdb.dbo.agent_datetime(@P1, @P2) UPDATE msdb.dbo.sysjobactivity SET next_scheduled_run_date = @nextScheduledRunDate WHERE session_id = @P3 AND job_id = @P4","No Exception Occurred",""
"09/29/2016  8:53:22 AM","User2","","aaa.bbb.ccc.ddd","aaa.bbb.ccc.ddd","Server Group 2","Service 2","set deadlock_priority","","SET ROWCOUNT 0 SET TEXTSIZE 2147483647 SET NOCOUNT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ARITHABORT ON SET LOCK_TIMEOUT -1 SET QUERY_GOVERNOR_COST_LIMIT 0 SET DEADLOCK_PRIORITY NORMAL SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET ANSI_NULLS ON SET ANSI_NULL_DFLT_ON ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET CURSOR_CLOSE_ON_COMMIT OFF SET IMPLICIT_TRANSACTIONS OFF SET QUOTED_IDENTIFIER ON","No Exception Occurred",""
"09/29/2016  1:37:09 PM","User3","","aaa.bbb.ccc.ddd","aaa.bbb.ccc.ddd","Server Group 1","Service 1","","","SELECT SYSTEM_USER","No Exception Occurred",""

This CSV has3 rows with Query field lengths of 223, 401 and 8. Trying to truncate row 2 to 300 characters has the same issue as 30000 chars in my CSV.

8

Is it allways the same field that goes too long?? i.e. The query field??

If so then try this quick throw together …

Don’t chuckle too much about the sample csv i used… Wasn’t in a creative mood

Sample csv

"Event Date and Time","User","OS Host Names","Source IP","Destination IP","Server Group","Service","Operation","Object","Query","SQL Exception Occurred","Source Application"
"03 October 2016 14:02:05","Joe6pack","smallville","127.0.0.1","127.0.0.2","snowwhite","caffeinate","a","b","1234567890123456789012345678901234567890","no clue","pokemon go"

Code

$inputfile="c:\data\test.csv"
$csv=import-csv $inputfile
foreach ($line in $csv)
 {
 $line.query=$line.query[0..9] -join ""

 }
 $csv | export-csv -Path $inputfile -NoTypeInformation

Result is here ( the query field’s truncated now )

"Event Date and Time","User","OS Host Names","Source IP","Destination IP","Server Group","Service","Operation","Object","Query","SQL Exception Occurred","Source Application"
"03 October 2016 14:02:05","Joe6pack","smallville","127.0.0.1","127.0.0.2","snowwhite","caffeinate","a","b","1234567890","no clue","pokemon go"

If that’s it then good luck. If not do tell me…

@iainsmart

1 Spice up
9 
$csv = @"
one,two,three
1,2,3
100,200,300
1000,2000,3000
1,2000,3
"@ -split "`r`n"

$csv = ConvertFrom-Csv $csv

$properties = $csv | 
Get-Member -MemberType NoteProperty | 
Select-Object -ExpandProperty Name

$new_csv_data = ForEach($line in $csv) {
    $props = @{}

    ForEach($property in $properties) {
        If($line."$property".Length -gt 3) {
            $props[$property] = $line."$property".Substring(0,3)
        } Else {
            $props[$property] = $line."$property"
        }
    }

    New-Object -TypeName PSObject -Property $props
}

$new_csv_data |
Select-Object -Property $properties

There is an example that trims every cell if it is longer than 3.

1 Spice up
10

Nice … Am going to steal that code.

11

For whatever reason, putting the field name in lower case works!