CODEWITHSUNDEEP
powershellcsvheaderexport-csv
Kelly L. Qualls
Kelly L. Qualls

Reputation: 21

Removing Headers from CSV in Powershell

Need help removing headers from a CSV file. The tool the files are being imported into has very stringent requirements. The top row has to be Title then row 2 date: 8/19/2019. Row three should contain data with no headers.

Tried to import it back and drop the top row using [1..-1] notation as well as tried to bring the data in with a Select-object -skip 1. Still can't make it work.

Also tried writing the object array out to TXT but it only comes out as fields instead of being tab based like in CSV. Example:

Date: INfo: Field1: 

get-content "c:\users\user123\documents\adh\_$(get-date (get-date).addDays($i) -f yyyy-MM-dd).csv" | Select-Object -Skip 1 | Set-Content "c:\users\user123\documents\adh\_$(get-date (get-date).addDays($i) -f yyyy-MM-dd).txt"

The file still contains the headers even after I tried to remove them.

Upvotes: 1

Views: 7685

Answers (5)

mklement0
mklement0

Reputation: 437258

It sounds like your starting point is an in-memory object array, to be converted to CSV without headers.

To get a CSV representation without headers you needn't create an intermediate file with Export-Csv - simply pipe to ConvertTo-Csv, which produces an array of strings, whose first element - the header row - you can skip with Select-Object -Skip 1.

Your entire file can therefore be written via a single expandable here-string:

# Format the date string.
$i = 2 # sample value
$dateString = '{0:yyyy-MM-dd}' -f (Get-Date).AddDays($i)

# The input object array
# $objArray = ...

# Use an expandable here-string to write the output file.
@"
Title
$dateString
$($objArray | ConvertTo-Csv | Select-Object -Skip 1 | Out-String)
"@ | Set-Content -NoNewline "c:\users\user123\documents\adh\_$dateString.txt"

As for what you tried:

tried to import it back and drop the top row using [1..-1] notation

[1..-1] does not skip the first element of an array in PowerShell, because 1..-1 is a range expression that expands to the following list of indices: 1, 0, -1; that is, you're extracting exactly 3 elements: the 2nd (1), the first (0), and the last one (-1).

Also tried writing the object array out to TXT but it only comes out as fields instead of being tab based like in CSV. Example:

That sounds like you sent your in-memory object array to a *.txt file using something other than Export-Csv, which won't give you CSV output; it sounds like you used > or Out-File, where objects are formatted the same way they'd print to the console, which is a for-display format, not one suitable for programmatic processing.

get-content ....csv | Select-Object -Skip 1 | Set-Content ....txt

That command should work, assuming that the input *.csv file was created with
Export-Csv -NoTypeInformation.

If you didn't use -NoTypeInformation, use -Skip 2, i.e., skip an additional line, so as to skip the type-annotation line that Export-Csv - unfortunately - outputs as the 1st line by default.

Note: This counter-intuitive default behavior of Export-Csv in Windows PowerShell has been fixed in PowerShell Core.

Upvotes: 1

user6811411
user6811411

Reputation:

Given a generated sample file:

> Get-Content .\_2019-08-19.csv
"COLA","ColB"
"Test1","Foo"
"Test2","Baz"
"Test3","Bar"
"Test4","Baz"
"Test5","Baz"

This script:

## Q:\Test\2019\08\20\SO_SO_57581085.ps1
## 
$i = 1
$BaseDir = 'C:\Users\user123\Documents\adh'
# $BaseDir = (gi .).FullName
$BaseName= '_{0:yyyy-MM-dd}' -f (Get-Date).AddDays(-$i)

"Your Title`ndate: {0:M\/d\/yyyy}" -f (Get-Date).AddDays(-$i) | 
  Set-Content (Join-Path $BaseDir ($BaseName+'.txt')) # -Encoding if req.

Get-Content (Join-Path $BaseDir ($BaseName+'.csv')) | 
  Select-Object -Skip 1 | 
    Add-Content (Join-Path $BaseDir ($BaseName+'.txt')) # -Encoding if req.

Yields this result file:

> Get-Content .\_2019-08-19.txt
Your Title
date: 8/19/2019
"Test1","Foo"
"Test2","Baz"
"Test3","Bar"
"Test4","Baz"
"Test5","Baz"

Upvotes: 1

lit
lit

Reputation: 16236

The file can be constructed after you get the .csv file that contains a field header record. The csmem.csv file has been produced, but has a header record. This will write the title and date on the first two (2) records, then append the .csv file without the header record.

"The Title" | Out-File -FilePath "C:\src\t\sf.csv" -Encoding ascii
(Get-Date).ToString('yyyy-MM-dd') | Out-File -FilePath "C:\src\t\sf.csv" -Encoding ascii -Append
Get-Content -Path 'C:\src\t\csmem.csv' |
    Select-Object -Skip 1 |
    Out-File -FilePath "C:\src\t\sf.csv" -Encoding ascii -Append

Upvotes: 1

Zach Alexander
Zach Alexander

Reputation: 403

I was able to reproduce your issue by generating a test CSV like so:

Get-Process | Select ProcessName, ID, SI | Export-CSV C:\temp\test.csv -NoTypeInformation

What you've stumbled upon is an interesting aspect of the pipeline. I don't know the specifics of WHY it's doing what it's doing, but you have two ways of working around it automatically putting the headers back in the CSV:

  1. Add -NoClobber to your Out-File: Get-Content C:\temp\test.csv | Select -Skip 1 | Out-File -FilePath "C:\temp\new.csv" -NoClobber The caveat here is that it won't let you overwrite the file you've opened with Get-Content so you'll have to send it to a separate file
  2. Or you can bypass the pipeline by storing the result of Get-Content C:\temp\test.csv | Select -Skip 1 into a variable and then sending that variable to a regular Out-File call. This would let you overwrite the file.

Upvotes: 0

Mahmood Shehab
Mahmood Shehab

Reputation: 301

Copy the file on a test environment, try to add -NoTypeInformation this might solve your problem.

-NoTypeInformation will remove the metadata, so be careful in the testing.

Upvotes: 0

Related Questions