Sign in
 
HomeLibraryLearnDownloadsRepositoryCommunityForumsBlog
Script Center >
> 
> 
Working with CSV file in PowerShell 2.0

Answered Working with CSV file in PowerShell 2.0

  • Tuesday, November 20, 2012 10:04 AM
     
     
    Sign In to Vote
    0

    Hello,

    I am trying to remove the first row from a csv file (generally heading row when its opened with MS Excel) using "Export-Csv -NoTypeInformation" cmdlet but its not working. 

    Any suggestion would be highly appreciated.

     

All Replies

  • Tuesday, November 20, 2012 10:10 AM
     
     
    Sign In to Vote
    0

    Heading is important in CSV files, it tells what are the names of all columns/properties and therefore shouldn't be removed

    NoTypeInformation has different meaning:

    Omits the type information from the CSV file. By default, the first line of the CSV file contains "#TYPE " followed by the fully-qualified name of the type of the object

    You should use Import from text when opening csv file from Excel

     


     
  • Tuesday, November 20, 2012 10:19 AM
     
     
    Sign In to Vote
    0

    Thank you Blindrood !!! That did not help. Please let me elaborate it. I have exported some users' info from AD in csv format. The imported csv file contains many columns. I want to select name, mail and id only. So far its working good. But I don't want the first row ie. name, mail and id as heading. I am using the following code -- 

    Import-Csv D:\PS\export.csv | select name,mail,id| Export-Csv -path D:\PS\xyz.csv -NoTypeInformation

    I tried it without -NoTypeInformation but it added another row in the output file.


     
  • Tuesday, November 20, 2012 10:30 AM
     
     Answered Has Code
    Sign In to Vote
    0

    Well what you could do is use the following lines to strip the first line of a text file:

    $CsvFile = Get-Content -Path C:\File.csv
Set-Content -Path C:\File.csv -Value $CsvFile[1..$($CsvFile.Count-1)]

    Jaap Brasser
    http://www.jaapbrasser.com

    • Marked As Answer by Awaneendra Wednesday, November 21, 2012 8:11 AM
    •  
     
  • Tuesday, November 20, 2012 10:32 AM
     
      Has Code
    Sign In to Vote
    0

    How about this?

    Import-Csv D:\PS\export.csv | select name,mail,id| ConvertTo-CSV | Select -skip 2 | Out-File -Path D:\PS\xyz.csv

     
  • Tuesday, November 20, 2012 12:53 PM
     
      Has Code
    Sign In to Vote
    0
     
     ConvertTo-Csv -NoTypeInformation | Foreach-Object  -begin { $start=$true }  -process { if ($start) { $start=$false } else { $_ }  } |
    out-file -FilePath ./mycsv.csv -Append
     
  • Wednesday, November 21, 2012 8:35 AM
     
     
    Sign In to Vote
    0

    Hello Sir,

    Thank you very much. It worked like a charm. 

    I want to convert the csv file (the one which I got after running your code) into xml and save the file with current timestamp. I am using PowerShell V2.0

    Thanks and regards,

    Awaneendra

     
  • Wednesday, November 21, 2012 9:08 AM
     
      Has Code
    Sign In to Vote
    0

    You can use the Export-Clixml command to do the conversion. Combine this with get-date to add the current date/time-stamp to the filename and you are set:

    Import-csv -Path D:\Export.csv | Export-Clixml -Path D:\Export_$((Get-Date).ToString('yyyyMMdd-hhmm')).xml

    Jaap Brasser
    http://www.jaapbrasser.com

     
  • Wednesday, November 21, 2012 12:05 PM
     
     
    Sign In to Vote
    0
    Thanks again for your reply. Its not doing what is needed. I have a vbscript for the same. How do I run that in powershell and stay till execution is complete ? I mean PowerShell should not return to prompt before converting to XML.
     
  • Wednesday, November 21, 2012 12:12 PM
     
     Answered Has Code
    Sign In to Vote
    1

    You would just execute the script as follows, PowerShell will not execute anything until the vbscript has finished:

    cscript script.vbs


    Jaap Brasser
    http://www.jaapbrasser.com

    • Marked As Answer by Awaneendra Wednesday, November 21, 2012 4:23 PM
    •  
     
  • Wednesday, November 21, 2012 4:24 PM
     
     
    Sign In to Vote
    0
    Thank you Sir, you made my day. It worked as I was expecting. 
    • Marked As Answer by Awaneendra Thursday, November 22, 2012 9:36 AM
    • Unmarked As Answer by Awaneendra Thursday, November 22, 2012 9:36 AM
    •