• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 644
  • Last Modified:

How to read a csv file with headers ?

@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
namerg
Asked:
namerg
  • 7
  • 6
1 Solution
 
SubsunCommented:
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
 
namergSystems AdministratorAuthor Commented:
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
 
SubsunCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
SubsunCommented:
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
 
namergSystems AdministratorAuthor Commented:
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
 
SubsunCommented:
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
 
namergSystems AdministratorAuthor Commented:
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
 
SubsunCommented:
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
 
namergSystems AdministratorAuthor Commented:
Thank you for your patience....will test it. ;)
0
 
namergSystems AdministratorAuthor Commented:
:( 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
 
SubsunCommented:
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
 
namergSystems AdministratorAuthor Commented:
@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
 
SubsunCommented:
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now