Count CSV columns without using Excel API

Question

Is there any method/code/reference to count the number of columns of every each row of records in a CSV file by maybe using PowerShell Get-Content and without using any Excel API as the records in the CSV file are extremely large (1GB+). Using Excel API such as Import-Csv or New-Object -ComObject Excel.Application will take extremely long periods of time to process.

The following image is the example of records in a CSV file.

And the following image is the same CSV open in Notepad++. As you can see there is only one delimiter in row 4. Hence this row counted as 2 columns while others are 4 columns.

I would like create a script that can check every row of CSV data with short time taken and can detect the columns are missing if the delimiter is not same as the header(as shown in row 4 with 2 columns but in row 1 header with 4 columns). So the final result would be like this:

Row 2 4 Columns Pass
Row 3 4 Columns Pass
Row 4 2 Columns Fail

I'm currently using COMAPI in my completed script, but I want to switch to using Get-Content. However, the quoted comma leads to an incorrect result.

# sample testing code snippet
$path = "testing.csv"
$delimiter = ","
Get-Content $path | ForEach-Object {($_.Split($delimiter)).Count}

And the result from the same CSV file will be like this:

4
4
4
3

A CSV is not an excel file. A CSV is a text file. So just use text functions to do what you need to do (count delimiters on every row) — Nick.Mc
– Nick.Mc, Commented Dec 6, 2017 at 6:39
Stack Overflow is a community not a code writing service, it's expected that you attempt to code this yourself. I would suggest you do some research on your issue (maybe try the search box at the top of the page) and make an attempt at writing some code yourself. I'll help you by point you towards Get-Content and Import-Csv, those are the basis of possible solutions — henrycarteruk
– henrycarteruk, Commented Dec 6, 2017 at 6:40
@JamesC. Of course I did some research, that why I posting this question with sample data and asking is there any alternative solution. I did tried run using ComAPI and it took around 2 minutes to execute 1000 of rows of data. And I did mentioned using methods such as get-content, however get-content will facing issues that the delimiter (,) may is part of data as shown in my figure above. Hence I would seek for opinion on alternative method to solve this problem. Thanks for your advice anyway. — Yong Cai
– Yong Cai, Commented Dec 6, 2017 at 6:45
@Nick.McDermaid however if use text functions such as get-content will have quoted delimiter issues, then the row 4 would be shown as 3 columns as there are 2 comma if I not mistaken — Yong Cai
– Yong Cai, Commented Dec 6, 2017 at 7:02
If you've done research on the question, add your code so we know what you've tried. — henrycarteruk
– henrycarteruk, Commented Dec 6, 2017 at 7:19

iRon · Accepted Answer · 2017-12-07 09:20:16Z

2

If you Import-Csv, every item value ($_.PSObject.Properties.Value) is converted to a string, unless the whole item is missing from the column, the .Value property will be set to $Null.

If your csv file includes a header row, it is presumed that the number of headers are at least the same as the maximum number of columns in a row:

Import-Csv .\testing.csv | ForEach {@($_.PSObject.Properties | Where {$_.Value -ne $Null}).Count}

In case your csv file doesn't include a header row, you might add a large number of headers yourself:

Import-Csv .\testing.csv -Header @(0..99) | ForEach {@($_.PSObject.Properties | Where {$_.Value -ne $Null}).Count}

edited Dec 7, 2017 at 9:20

answered Dec 6, 2017 at 10:02

iRon

24.4k10 gold badges60 silver badges107 bronze badges

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

2 Comments

Yong Cai Over a year ago

Hi, thanks for reply, I tried your method, working but if let say I add another rows of data into same csv file with 5 columns, the result will be 4,4,2,4. The last value will be remain as 4 instead of 5. Any idea to fix that?

iRon Over a year ago

Yes, add a header to the Import-Csv. E.g. -Header @(0..99). I have changed this in the answer. Note that if you use the -Header parameter the first line in the the csv file is seen as data (meaning that these properties are counted and added in the top of the list).

Collectives™ on Stack Overflow

Count CSV columns without using Excel API

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related