Powershell script Export-Csv weirdness

Question

I have created a small script to extract all lines of a specified string for a folder and its subfolder. It behaves as expected untill I want to export the lines to excel (csv). At that point it only exports two lines (at least it is the same two lines every single time). In reallity it should have exportet around 40 lines. The working script looks like this:

[string]$pathSource = "D:\Projects\test"
[string]$pathTarget = "D:\Projects\test\searchpattern-sql.csv"

[string[]]$excludeFileTypes = "*.dll","*.pdb","*.gif","*.dat","*.bin","*.msi","*.epi4","*.png","*.tlog","*.gif","*.cache","*.csproj","*.config","*.user","*.txt"

[string]$searchPattern = "sqlCommand"

Get-ChildItem $pathSource -Exclude $excludeFileTypes  -Recurse | Select-String $searchPattern | Sort-Object Path | Select-Object Path, LineNumber, Line

Adding a new pipe with the export cmdlet ( | Export-Csv -path $pathTarget) somehow ruins the search. What am I doing wrong?

Do you have any sample input/output that demonstrates the problem? — zdan
– zdan, Commented May 24, 2011 at 17:24
@zdan, the real problem turned out to be Excel, the script itself worked just fine. — JustAnotherCoder
– JustAnotherCoder, Commented May 25, 2011 at 5:00

JPBlanc · Accepted Answer · 2011-05-24 19:18:35Z

1

Just test this :

[string]$pathSource = "D:\Projects\test"
[string]$pathTarget = "D:\Projects\test\searchpattern-sql.csv"

[string[]]$excludeFileTypes = "*.dll","*.pdb","*.gif","*.dat","*.bin","*.msi","*.epi4","*.png","*.tlog","*.gif","*.cache","*.csproj","*.config","*.user","*.txt"

[string]$searchPattern = "sqlCommand"

$a = Get-ChildItem $pathSource -Exclude $excludeFileTypes  -Recurse | Select-String $searchPattern | Sort-Object Path | Select-Object Path, LineNumber, Line
$a | Export-Csv yourfile.csv -NoTypeInformation

The $a var just contains the array of lines that I export.

answered May 24, 2011 at 19:18

JPBlanc

73.1k18 gold badges137 silver badges180 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JustAnotherCoder Over a year ago

Thanks, that made a difference in Excel, but it was still not correct. And that can't be, there must be something else going on. And there was --> Excel. Excel when opening the .csv file was trying to be clever and failed. Only noticed this when opening the file using Notepad++. Tried my original script and opended the result in Notepad++ and the info was all there. So i guess the real answer is "don't always trust Excel when opening .csv files".

Emiliano Poggi · Accepted Answer · 2011-05-24 16:14:44Z

0

Personally I've found problems using Export-CSV. What I've found working fine is pipe to ConvrtTo-CSV and then to Set-Content or output to file using redirection.

answered May 24, 2011 at 16:14

Emiliano Poggi

24.9k8 gold badges58 silver badges66 bronze badges

2 Comments

JustAnotherCoder Over a year ago

No, I get the same result doing it that way. The ConvertTo-CSV works just fine but when I pipe it into the Set-Content cmdlet it is back to the same two lines...

JustAnotherCoder Over a year ago

the real problem was Excel, the script did what it was supposed to do.

Collectives™ on Stack Overflow

Powershell script Export-Csv weirdness

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related