"Can we make it pretty?" - Export-CSV formatting?

bob-13 · 2018-03-22T14:20:52.000Z

So… I have a script that does an SQL Query and returns data. It is User info, so let’s use this as an example

Location, Dept., Office #, LastName, FirstName
Edmonton,Sales,218,Smith,Joe
Edmonton,Sales,220,Doe,Joe
Edmonton,IT,none,Smith,Bob
Calgary,Sales,392,Smith,Jane

So when you open the report that’s what you see. What would make the report look “cleaner” would be:

Location, Dept., Office #, LastName, FirstName
Edmonton ,Sales,218,Smith,Joe
,220,Doe,Joe
,IT,none,Smith,Bob
Calgary,Sales,392,Smith,Jane

So that you see “white space” when the column value doesn’t change between rows.Is there an easy way to do this? … honestly it is just making it pretty not changing the value of the info reported.

browncoatsgt · 2018-03-22T14:59:16.000Z

I just wrote it rough and quick to give you the idea:

Foreach $s in $sales {

$newlocation=$2.location

if ($newlocation=$oldlocation) {

$location=“”

} else {

$location=$newlocation

}

$oldlocation=$newlocation

}

bob-13 · 2018-03-22T15:09:38.000Z

BrowncoatSGT:

I just wrote it rough and quick to give you the idea:

Foreach $s in $sales {

$newlocation=$2.location

if ($newlocation=$oldlocation) {

$location=“”

} else {

$location=$newlocation

}

$oldlocation=$newlocation

}

And unless I’m mistaken I’d have to do that for every column that “might” be the same… so … yeah, if they want it cleaner they can edit it after the fact. (ok, I’d only have to make the mass of code once, but it over complicates a “one page” script.)

(I mean the script including the 13 lines of sql query and commenting, and conversion to XLS this is… 60 lines. That is… 8 lines for one column and I have… at least 3 columns to “tidy” up.

Neally · 2018-03-22T15:13:48.000Z

BrowncoatSGT:

If you post code, please use the ‘Insert Code’ button. Please and thank you!

PLEASE READ BEFORE POSTING! Read if you're new to the PowerShell forum! Programming & Development

Hi, and welcome to the PowerShell forum! Don’t apologize for being a “noob” or “newbie” or “n00b.” There’s just no need – nobody will think you’re stupid, and the forums are all about asking questions. Just ask! Use a descriptive subject. Don’t say “Need help” or “PowerShell Help”, actually summarize what the problem is. It helps the rest of us keep track of which problem is which. Don’t post massive scripts. We’re all volunteers and we don’t have time to read all that, nor will we copy…

if ($newlocation=$oldlocation){<#stuff#>}

This does not work as ‘=’ is value assignment, not compare, it should be ‘-eq’

@browncoatsgt

kevin-systemtools-software · 2018-03-22T15:31:28.000Z

I’m not clear on whether you want an actual ‘report’, or just want to extract the data. Your output is essentially a CSV (comma separated value) output, which if you put that into a file, you can then just open it with Excel, and you will have everything nicely lined up into rows and columns. Trying to add spaces to even things out is going to be next to impossible because it would have to be based on what the maximum width is for the various fields, which can’t be predicted at run-time.

bob-13 · 2018-03-22T17:54:01.000Z

Kevin (SystemTools Software):

I’m not clear on whether you want an actual ‘report’, or just want to extract the data. Your output is essentially a CSV (comma separated value) output, which if you put that into a file, you can then just open it with Excel, and you will have everything nicely lined up into rows and columns. Trying to add spaces to even things out is going to be next to impossible because it would have to be based on what the maximum width is for the various fields, which can’t be predicted at run-time.

To be more precise this is what I’m asking:

Pretty.png798×181 8.71 KB

Of course if I succeed they’ll want … more like each location on a separate tab, or at least a spacer line…

Neally · 2018-03-22T17:55:18.000Z

How come ‘joe’ is ok to be listed twice?

Or Smith?

Is that just needed for he first columns?

bob-13 · 2018-03-22T18:00:29.000Z

They need “full user” info, but the common stuff, like location and Dept. should be grouped… and then it is easier to tell when it changes if the repeats aren’t there.

If the same “Joe Smith” had an office in Edmonton and Calgary they’d probably want me to show that somehow… but that should never happen.

Edit:

Seriously, the report as is should be just fine, they just want to make it… pretty. I want to say “it is pretty… pretty functional.”

kevin-systemtools-software · 2018-03-22T18:26:52.000Z

I see now. Your best bet is to import this data into Microsoft Access, which has an import wizard. Then, run the report wizard which, if memory serves, has a ready-made template that does exactly this. The wizard gives you a preview so you can see exactly what it is going to do. You can even have it do the sorting for you.

Neally · 2018-03-22T19:55:29.000Z

Like so?

$data = import-csv $env:userprofile\desktop\input.csv
$data | ft *

foreach ($column in ($data | get-member | where membertype -eq 'noteproperty' | sort -Descending).name) {
    if (-not($column -eq 'LastName' -or $column -eq 'FirstName')) {
        foreach ($row in 1..($data.count - 1)) {
            if ($data[$row].$column -eq $data[$row - 1].$column -or $data[$row].$column -eq $data[$row - 2].$column) {
                $data[$row].$column = " "
            }
        }
    }
}

$data | ft *

bob-13 · 2018-03-22T20:48:48.000Z

Neally:

Like so?

$data = import-csv $env:userprofile\desktop\input.csv
$data | ft *

foreach ($column in ($data | get-member | where membertype -eq 'noteproperty' | sort -Descending).name) {
    if (-not($column -eq 'LastName' -or $column -eq 'FirstName')) {
        foreach ($row in 1..($data.count - 1)) {
            if ($data[$row].$column -eq $data[$row - 1].$column -or $data[$row].$column -eq $data[$row - 2].$column) {
                $data[$row].$column = " "
            }
        }
    }
}

$data | ft *

I suspect… not.

That would work with the data I used in the demo, but the SQL Query that creates the Data to export to CSV is more complex… there could be many rows with 1 value so the “Row-1” and “row -2” would need some kind of… loop to find the last row where that column had a value.

However these replies have confirmed my suspicions that this isn’t simple. The SQL query does all the sorting, and grouping, but the “erase redundant” values in columns… is the part I haven’t done.

(Query has 8 columns the first 4 would need “cleanup” the last 4 wouldn’t and an example of it has only 1 value in column 1 and 72 rows… so I’d be expected to remove 71 of them, the second column has… 3 values and an uneven split… etc.)

I’ll just say… “you want it to work or look nice before you get it?” I mean really… they can’t hit the delete key themselves?

Neally · 2018-03-22T20:51:20.000Z

How come they can’t do the sorting and such in SQL.

SQL is better for that compared to PowerShell in theory

bob-13 · 2018-03-22T20:59:44.000Z

Neally:

How come they can’t do the sorting and such in SQL.

SQL is better for that compared to PowerShell in theory

The sot is done in sql, But either I don’t know the syntax, or every record includes the data, and no blank spaces.

I suppose I could go the the SQL forum and ask for help.

psophos · 2018-03-22T23:34:45.000Z

Could it be done? Probably.

Is there a point? Not really.

Information is being thrown away. So the report cannot realistically be manipulated any further. Like sorting in Excel by department or location or whatever. Or additional grouping and sorting.

Making it ‘pretty’ (very subjective) is probably not worth the effort in powershell.

Perhaps the output from SQL using GROUP BY, HAVING, ORDER BY and other SQL commands would be enough. Takes a little effort and I’ve forgotten most of it at this point.

bob-13 · 2018-03-23T12:17:18.000Z

M Boyle:

Could it be done? Probably.

Is there a point? Not really.

Information is being thrown away. So the report cannot realistically be manipulated any further. Like sorting in Excel by department or location or whatever. Or additional grouping and sorting.

Making it ‘pretty’ (very subjective) is probably not worth the effort in powershell.

Perhaps the output from SQL using GROUP BY, HAVING, ORDER BY and other SQL commands would be enough. Takes a little effort and I’ve forgotten most of it at this point.

Cannot manipulate further? I’m not sure I agree with that, but, I understand where you are coming from.

The SQL clause is already one of the “more complex” I’ve done in years. It includes group by and order by clauses, and multiple tables and where conditions etc. so… I fully believe there is little point in these “readability touch-ups” when the data is there… and if you don’t like it you can delete it yourself, (Or hide it, etc.)

psophos · 2018-03-23T13:04:16.000Z

Cannot manipulate further? I’m not sure I agree with that, but, I understand where you are coming from.

The SQL clause is already one of the “more complex” I’ve done in years. It includes group by and order by clauses, and multiple tables and where conditions etc. so… I fully believe there is little point in these “readability touch-ups” when the data is there… and if you don’t like it you can delete it yourself, (Or hide it, etc.)

I was referring to the lost data in your ‘after’ example. The end user cannot fire up the excel data tools and sort things by dept (as an example) without then losing the ability to resort it by location. Well they can but the data will now be in entirely the wrong order/

And that’s probably the best reason to say no to the end users. The data output in its current form is “general purpose”.

They can use the data tools to further manipulate it as they need to or delete data to format it as they’d like to see it and they have to data available to use if they come up with some other great idea in future.