?
Solved

How to read a csv file with headers ?

Posted on 2013-01-14
13
Medium Priority
?
625 Views
Last Modified: 2013-01-15
@footech or @subsun or whoever...

What if my CSV file start with headers is there any special treatment with the code so it does skip the header and jumps to real information ?

Thanks a lot

#CSV File
Last Name,First Name,Dept Descrip,Job Title,Work Phone,Clock Number,Employment Category,Room,Date Of Birth,Div Descrip
Hughes,Ardith,1.A3010 - Executive Admin,Executive Assistant - Admin,3034445555,1234,F,A069,3/15/1948,Administration

Open in new window

0
Comment
Question by:namerg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 40

Expert Comment

by:Subsun
ID: 38775276
Import-csv is still used to read information..
As per yur CSV header the values will be like
$_."Last Name" represent Hughes
$_."First Name" is Ardith
$_."Dept Descrip" is A3010 - Executive Admin etc..
0
 

Author Comment

by:namerg
ID: 38775362
Subsun, yes I know..

The code we have been working, the csv does not contain headers. What if not possible to have the csv file without headers?
Right now I do have the csv like:
Silveira,Rodrigo,Information Services,Sr System/NetworkAdministrator,303-333-4444,1111,2222

Open in new window

On this file is easier to get the info but with headers i have to jump to the next line to read information needed.

You know what i mean ?

Thanks a lot
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38775593
So if there is a header then you want to ignore it is that correct? If yes that will be bit difficult.. Does your .csv file will have always same headers, like Last Name,First Name,Dept Descrip,Job Title,...etc?
0
Four New Appliances. Same Industry-leading Speeds.

But don't take it from us.  The Firebox M370 is Miercom tested and Miercom approved, outperforming its competitors for stateless and stateful traffic throughput scenarios.  Learn more about the M370, M470, M570 and M670 and find the right solution for your organization today!

 
LVL 40

Expert Comment

by:Subsun
ID: 38775728
If you have always same header for the input .csv file then something like following will help you to get rid of the header..
If (Import-CSV user.csv | ? {$_."Last Name" -ne $null}){
$newcsv = Get-Content user.csv
$newcsv = $newcsv[1..($newcsv.count - 1)]
$newcsv > Newuser.csv
Import-CSV Newuser.csv -header sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber
}
Else 
{Import-CSV Newuser.csv -header sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber}

Open in new window

0
 

Author Comment

by:namerg
ID: 38775732
Yes, my friend. But, i mean the content of the file.
For example if CSV1 has:
manning, peyton,quarteback,broncos #information, data

Open in new window

and
CSV2 has:
lastname,firstname,position,team #headers
manning,peyton,quaterback,broncos #information, data

Open in new window

Those are totally different file with a slight difference in content, correct?
So, you are saying if i read either csv1 and csv2, always is going to read the data, the information and skip the headers ?
Do you understand me ?

Thanks,
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38775800
You mean to say the header will change??


So, you are saying if i read either csv1 and csv2, always is going to read the data, the information and skip the headers ?
Nope.. it will not skip the header..

Import-CSV will always take the first line as header..

For
lastname,firstname
manning,peyton
Result
lastname : manning
firstname : peyton

For
manning,peyton
manning,peyton

Result
manning : manning
peyton : peyton

So what I mean to say is, if you don't have something to identify first line then it will be difficult to say the file has a header or not..
0
 

Author Comment

by:namerg
ID: 38776057
If i am not wrong,

With or without headers always is going to get the info, the data. I do not need to jump to the next line to read the info, correct ?
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38776131
Yes it will read the data.. but if you have the header and you are using
Import-CSV Newuser.csv -header sn,givenName,department,title,...
to read data, then the result will be like follows. Means first set of data is invalid..
sn              : Last Name
givenName       : First Name
department      : Dept Descrip
title           : Job Title
telephoneNumber : Work Phone
employeeID      : Clock Number
employeeNumber  : Employment Category

sn              : Hughes
givenName       : Ardith
department      : 1.A3010 - Executive Admin
title           : Executive Assistant - Admin
telephoneNumber : 3034445555
employeeID      : 1234
employeeNumber  : F

Open in new window

As I said in earlier post (ID: 38775728), If you are sure about the header then you can try my script..
0
 

Author Comment

by:namerg
ID: 38776402
Thank you for your patience....will test it. ;)
0
 

Author Comment

by:namerg
ID: 38776932
:( it looks like you did not understand me. :(

This is what I meant:
#File1.csv has the following content and NO HEADERS
STARR,BART,Green Bay,Quaterback,303-444-3504,1111,2222
MONTANA,JOE,San Francisco,Quaterback,303-444-3515,1111,2222
BAUGH,SAMMY,Washington,Quaterback,303-444-3513,1111,2222
GRAHAM,OTTO,Cleveland,Quaterback,303-444-3539,1111,2222

#File2.csv has the following content WITH HEADERS
sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber
STARR,BART,Green Bay,Quaterback,303-444-3504,1111,2222
MONTANA,JOE,San Francisco,Quaterback,303-444-3515,1111,2222
BAUGH,SAMMY,Washington,Quaterback,303-444-3513,1111,2222
GRAHAM,OTTO,Cleveland,Quaterback,303-444-3539,1111,2222:

Open in new window

So, the code
$File = Import-Csv "c:\scripts\ad\temp\file1.csv" -header sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber
clear-host
write-output "NO HEADERS"
Foreach ($File_User in $File) {
		write-output $file_user.sn
		write-output $file_user.givenName
		write-output $file_user.department
		write-output $file_user.titie
		write-output $file_user.telephoneNumber
		write-output $file_user.employeeID
		write-output $file_user.employeeNumber
                write-output ""
}
$File2 = Import-Csv "c:\scripts\ad\temp\file2.csv"
write-output ""
write-output "YES HEADERS"
Foreach ($File2_User in $File2) {
		write-output $file2_user.sn  
		write-output $file2_user.givenName
		write-output $file2_user.department
		write-output $file2_user.titie
		write-output $file2_user.telephoneNumber
		write-output $file2_user.employeeID
		write-output $file2_user.employeeNumber
                write-output ""
}

Open in new window

And, I was expecting the following result:
NO HEADERS
STARR
BART
Green Bay
303-444-3504
1111
2222

MONTANA
JOE
San Francisco
303-444-3515
1111
2222

BAUGH
SAMMY
Washington
303-444-3513
1111
2222

GRAHAM
OTTO
Cleveland
303-444-3539
1111
2222


YES HEADERS
STARR
BART
Green Bay
303-444-3504
1111
2222

MONTANA
JOE
San Francisco
303-444-3515
1111
2222

BAUGH
SAMMY
Washington
303-444-3513
1111
2222

GRAHAM
OTTO
Cleveland
303-444-3539
1111
2222

Open in new window


I am all set.
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38777187
If you have different files the it wont be an issue.. you can write separate code as mentioned by you.. so how did you chose which file to use? or is it like only one of the flies available at a  time?
0
 

Author Comment

by:namerg
ID: 38778632
@subsun, I am all set. I just wanted to know how read a file with headers and without headers and the key is the following:

if the file.csv has headers you do not specify -headers
if the file.csv does not have header, you do specify -headers.

cheers,
G
0
 
LVL 40

Accepted Solution

by:
Subsun earned 2000 total points
ID: 38778660
I just wanted to know how read a file with headers and without headers
This is clear..
What if my CSV file start with headers is there any special treatment with the code so it does skip the header and jumps to real information ?
This was confusing. I thought you may get the CSV files with headers as input and you want to remove the headers if they exist.

Anyways If you all set then it's good.. :-)
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
Auditing domain password hashes is a commonly overlooked but critical requirement to ensuring secure passwords practices are followed. Methods exist to extract hashes directly for a live domain however this article describes a process to extract u…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question