Solved

How to read a csv file with headers ?

Posted on 2013-01-14
13
594 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
  • 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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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 500 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Set OWA language and time zone in Exchange for individuals, all users or per database.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now