Jump to content
Larry Ullman's Book Forums
Cofa

How To Read Elements In Csv File By The Field Names?

Recommended Posts

Hello all again!

 

This post is somewhat related to my previous question:

"How To Identify A Record In Text File, Then Print Selected Values Of That Record?"

 

I am still working with the same script but with a new problem:

How could I read elements in csv file by the field names?

 

To better illustrate my question, I list the following two parts of my work:

 

 

Data file to be read, in "csv" format:

Note the first line contains the field names like you make it up in Excel.

File name: "00_test_readcsv_records.csv"

=============================

firstname#lastname#companyname#intro

321#Lastname321#321 Company#Introduction 321

Peter#KWAN#First Street Company#This is my introduction. Please send me your comments. Thanks!

Mary#THOMSON#Johnson High School#I am a grade 3 teacher here. I love facing the kids everyday!

Billy#YU#Billy Travel Agency#We specialize in cruise ship tours in the Caribbean - Let me know when you'd like to have your vacation! 111How long can this line be? The quick brown fox jumps over the lazy dog. How long can this line be? The quick brown fox jumps over the lazy dog. How long can this line be? The quick brown fox jumps over the lazy dog. How long can this line be? The quick brown fox jumps over the lazy dog. How long can this line be? The quick brown fox jumps over the lazy dog.

Peter2#KWAN2#First Street Company#This is my introduction. Please send me your comments. Thanks!

 

 

PHP script (including formatting code):

=================================================

<div style="width:780px; margin:30px;">

<p>1ST PART - INTERNAL CODE:</p>

<?php

$matcher = $_REQUEST['matcher'];

$file = "00_test_readcsv_records.csv";

$fp = fopen($file, r);

while ($row = fgetcsv($fp, 1000, '#')) {

if ( strtolower($row[0]) == strtolower($matcher) ) {

$firstname = $row[0];

$lastname = $row[1];

$companyname = $row[2];

$intro = $row[3];

break;

}

}

fclose($fp);

?>

<p>2ND PART - PRINT ELEMENTS OF MATCHED RECORD</p>

<?php

echo "<br>First name = " . $firstname;

echo "<br>Last name = " . $lastname;

echo "<br>Company name = " . $companyname;

echo "<br>Introduction = " . $intro;

?>

</div>

====================================END OF SCRIPT

 

 

Now my question is, with the line

if ( strtolower($row[0]) == strtolower($matcher) ) {

 

 

Can I read elements of the array ($row) by a specific "field name" of the csv file? For example, if the "$matcher" is meant to match a value of the field "lastname" (not necessary to be the first element "$row[0]", but an element that can be identified by the "field name", not by its position in the array), how could I modify the above code to fulfil this job?

 

The reason I want to do this way is whenever I need to change the order of the fields in the csv file, I can still use the same PHP script without changing the key# in the script.

 

Thanks!

 

Cofa

10/20/2011

Share this post


Link to post
Share on other sites

I'm really don't know excel enough to understand 'fieldname'. However, it looks to me like you think that your data is in rows and I have always seen data in columns. If data is in columns, then the first row would contain the titles (headings), so the first row would tell you the 'fieldnames'.

Share this post


Link to post
Share on other sites

Actually, reading more closely I see you are parsing just one row.

If the first row in your excel excel are the titles then those would be your fieldnames, I would think.

Share this post


Link to post
Share on other sites

Hello Abigail,

 

Thanks for trying to help. May be let me redo my questions...

 

First of all, let me simplify my data file. I am not sure if it is important if it is an Excel file; but it has to be a text file with a "value separator" - here I have used "#" as the separator. So here is the simplified file:

 

File name: "00_test_readcsv_records.csv"

=============================

firstname#lastname#companyname#intro

Peter#KWAN#First Street Company#This is my introduction. Please send me your comments. Thanks!

Mary#THOMSON#Johnson High School#I am a grade 3 teacher here. I love facing the kids everyday!

As you can see, the file has 3 rows. Row 1 contains "fieldnames" only, while rows 2 & 3 represent two records. The purpose of row 1 is to identify the fieldnames of values in all other rows.

 

With my previous script, it will read the file row by row, and check if the first element of a row ($row[0]) would match the value of "$matcher". If it does, the values of the row will be printed.

 

With this script, it is always the first element ($row[0]) of a row to be checked. And the first element is corresponding to the fieldname "firstname" on the first row of the file.

 

In my expectation, in some other files with which the existing script is to be used, the order of the fieldnames in the first row may be different. For example, in a particular file, the field "firstname" could be in the 3rd field (the 3rd element of every row, or $row[2]). If this is the case, the existing script will have to change accordingly or it will not work as expected.

 

So my question, and my desire as well :rolleyes: , is how I could change my script so that the specific element to be checked can be quoted by its fieldname (say, the field "firstname" in this example), regardless of the element's position in the row. To further explain my question, is there any similar expression like the first one below (match conditional), where an element in a row can be quoted by name corresponding to the fieldname?

 

Say, having: if ( strtolower($row['firstname']) == strtolower($matcher) ) { //Quoting an element by name corresponding to its fieldname $row['firstname']

Instead of: if ( strtolower($row[0]) == strtolower($matcher) ) { //Quoting an element by its position in the row $row[0]

Thanks,

Cofa

10/23/2011

Share this post


Link to post
Share on other sites

I think you can do it by using a variable for the array index.

But I think you will have to parse (compare) the fieldnames to find which one is firstname, lastname, etc. So if firstname is 3rd, $firstnameindex=2 but if firstname is 1st then $firstnameindex=0. So when you reference the array use row[$firstnameindex], something like this:

 

if ( strtolower($row[$firstnameindex]) == strtolower($matcher) ) {

$firstname = $row[$firstnameindex];

$lastname = $row[$lastnameindex];

$companyname = $row[$companynameindex];

$intro = $row[$introindex];

break;

}

 

 

 

  • Upvote 1

Share this post


Link to post
Share on other sites

Hello Abigail,

 

Thank you for your direction! With some research, I now have come up with a working script (tested and shown below). I also enclosed a revised text file. Note that I have added a new field ("scode") to the file.

 

With the new script, I will have to check if the secrete code ("scode") of a record matches the given $matcher. If it does, elements of the record are printed. I believe the new script will work with any data file without worrying about the positions of the fieldnames, as long as the fieldnames in all files are consistant.

 

I am very happy with the results! Again, thanks a lot to Abigail!

 

Cofa

10/24/2011

 

PS: I am still a newbie. So please feel free to show me any improvement that can be made with the script.

 

 

 

Text file:

===================================================

firstname#lastname#scode#companyname#intro

Peter#KWAN#aa11#First Street Company#This is my introduction. Please send me your comments. Thanks!

Mary#THOMSON#bb22#Johnson Elementary School#I am a grade 3 teacher here. I love facing the kids everyday!

Billy#YU#cc33#Billy Travel Agency#We specialize in cruise ship tours in the Caribbean - Let me know when you'd like to have your vacation!

Peter2#KWAN2#dd44#First Street Company#This is my introduction. Please send me your comments. Thanks!

 

 

The revised script:

===================================================

<div style="margin:60px;">

 

<?php

$matcher = $_REQUEST['matcher'];

$file = "00_test_readcsv_records.csv";

$fp = fopen($file, r);

 

//Finding the position of fieldnames from Row 1

$fieldnames = fgetcsv($fp, 1000, '#');

$scodeindex = array_search("scode", $fieldnames);

$firstnameindex = array_search("firstname", $fieldnames);

$lastnameindex = array_search("lastname", $fieldnames);

$companynameindex = array_search("companyname", $fieldnames);

$introindex = array_search("intro", $fieldnames);

echo "<p><b>Fieldnames of Row 1 have been read and identified:</b>

<br>Scodeindex = " .$scodeindex.

"<br>Firstnameindex = " .$firstnameindex.

"<br>Lastnameindex = " .$lastnameindex.

"<br>Companynameindex = " .$companynameindex.

"<br>Introindex = " .$introindex.

"<br>==END OF READING Fieldnames on ROW 1==</p>";

 

//Finding elements of the matched record

while ($row = fgetcsv($fp, 1000, '#')) {

if ( strtolower($row[$scodeindex]) == strtolower($matcher) ) {

$firstname = $row[$firstnameindex];

$lastname = $row[$lastnameindex];

$companyname = $row[$companynameindex];

$intro = $row[$introindex];

break;

}

}

fclose($fp);

?>

<p>PRINTING ELEMENTS OF THE MATCHED RECORD</p>

<?php

echo "<br>First name = " . $firstname;

echo "<br>Last name = " . $lastname;

echo "<br>Company name = " . $companyname;

echo "<br>Introduction = " . $intro;

?>

</div>

Share this post


Link to post
Share on other sites

That looks good to me, Josee. I don't see anything wrong with it.

However, I don't think you really need scode. Once you know the index of firstname you can match that name the same as you did before. But maybe you have some other reason why you want to use scode and either way will work.

 

This might be obvious already and it's just a suggestion. If you don't need the vars firstname, lastname, etc later you can just echo the info when you first see it.

  • Upvote 1

Share this post


Link to post
Share on other sites

Hi Josee, I read about the sprintf() function you provided and my understanding is that it might be good for printing results; I can't see any use for my primary purposes (i.e., to match a search from a text file). Maybe you could please point out something that I've missed but thanks for commenting anyway.

 

Hi Abigail, the use of "scode" (secret code or whatever) could serve as a general term or can be a better control of the contents of a file - I expect that it shall be issued by the host to maintain uniqueness ("firstname" could have more than one identical value in the file).

 

I found "array_search()" via Google search - I am very happy with this function and indeed it plays a very important part of the script. (I thought of using "explode" at first but was stuck at being unable to determine the exact position of the elements of Row 1.)

 

By the way, I assume advanced programmers won't use text files for the purposes of database so the above script could be for newbies like me only?

 

Cheers everyone!

 

Cofa

10/25/2011

Share this post


Link to post
Share on other sites

My last post I meant to say Cofa, not Josee

Actually I think there might be good reason to use a file like you do.

If your user (or even yourself) has any data already in excel then you can load that into your database with a script like you have. I do that myself, so I don't have to type each name into my form, I can get a list of names into excel, then upload that with a script something like yours. However, once I read the excel file then I don't keep using it, I store that data in my database, which is the much better way of doing it.

 

One thing I thought of is when I wrote my script, I also take into account that my user could put 'bad data' into that excel file. First I thought it might be accidentally but then I thought maybe even maliciously. So I thought I should protect my database because whatever is in that excel file will also end up in my database. What I do is 'clean' the input just like I would for input from a user form. Also any checking that the data is within limits. If you are writing for other people to input data then assume you will have some people that are completely computer illiterate and they might stay that way (because the don't want to learn). If I remember correctly, it is possible to load the excel file directly into MySQL, and it is very tempting to do that, but then there is risk of unfiltered data into your database.

  • Upvote 1

Share this post


Link to post
Share on other sites

Hi Josee: Thanks for your clarification. No worry as I just wanted to see if I could learn any improvement to my writing skill.

 

As to Abigail's comments, I want to let you know how I would use my script. Firstly, the data file is totally prepared internally - I gather data of subscribers from other means (form mail or fax etc) and save them in an Excel file. As you already pointed out, my Excel file will have all fieldnames on the first row and it can be easily updated on my computer. Once done, the file will be saved in csv and uploaded to the server. I do not plan to gather data from writing to file at this point (my server host told me the security risk is high).

 

Secondly as to ussage, the script is for members of our club to inform their contact about any upcoming event, in their own names. For example, if our club has an upcoming Christmas party, I can prepare an online registration form. Any group member of us, say one called Subscriber A, can then send a URL containing his unique "scode", something like http://ourupcomingparty.cmm/?matcher=aa11 (a fake URL), to his mailing list. The URL will go to the online registration form, with some id data imprinted in some <input> tags of the form. So when we receive the registration request, we will know who has introduced this guest (say, as per our text file, scode "aa11" is from Peter KWAN). With this script, the online form can also display name or id info of the member who sends the form.

 

Of course the script can be used in other similar situations. For me, the good part is I don't need to worry about the order (positions) of the fieldnames in my Excel files.

 

I am not using any database yet (will learn it next step), so text file is the only way I can go with (or, is it?). And because of the required purposes, I had to research to develop this script.

 

Cofa

10/26/2011

Share this post


Link to post
Share on other sites

Hello, Cofa,

 

Developing websites is only a leisure-time activity for me, so I learn very slowly. I waited ages before summoning the courage to add MySQL to what I was already trying to learn… only to discover that I had really been making things more difficult for me by avoiding MySQL at all costs! I'm quite sure you'll enjoy databases once you start working with them. They make many things much easier, and one of the really nice aspects of databases is that you can use them on your own computer as well as on the web, just for your own use.

  • Upvote 1

Share this post


Link to post
Share on other sites

Seems like you have a really nice website in the works, Cofa.

What you describe will work well, I think.

I agree with Josee, that you will do better with MySQL (or similar database) but meanwhile, excel is a good way to do it.

Share this post


Link to post
Share on other sites

Hi Josee and Abigail,

 

The script does work as I expected - thanks again for all the advice!

 

And I certainly will proceed to start learning MySQL soon.

 

Cheers!

Cofa

10/27/2011

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...