Pipe delimited file to JSON

[nephesh]

I have a text file with attributes in this format:

 

username1 | attribute1 | value1

username1 | attribute2 | value2

username1 | attribute3 | value2

username2 | attribute1 | value1

username2 | attribute2 | value2

username2 | attribute3 | value3

 

I would like to get them into a JSON array like:

 

[ users: [ { username1: { attribute1: "value1", attribute2: "value2", attribute3: "value3" }, 

{ username2: {  attribute1: "value1", attribute2: "value2", attribute3: "value3" }, 

{ username3: { attribute1: "value1", attribute2: "value2", attribute3: "value3" }, ] ]

 

Here is what I have so far:

 

$file='C:\wamp\www\faculty-staff-dir.lst';
$csv= file_get_contents($file);
$array = preg_split("/[\n]+/", $csv);
foreach($array as $key=>$value) {
$array[$key]=array($value=$value);
}
$json = json_encode($array);
print_r($json);

it's outputting in this format now:

[["username1 | attribute1 | value1"], ["username1 | attribute2 | value2"], ["username1 | attribute3 | value3"], etc..

 

Any help would be greatly appreciated!!

 

[requinix]

Actually that code won't get that output. I assume you posted an edited version? Next time just go with the real thing.

 

Obviously you need to split the line apart to get the three pieces of it, and equally obviously you need to merge all the user data together by taking into account the username and any existing data you have for it.

 

Start with a good loop for reading lines from a file

$h = fopen($file, "rt");
while (!feof($h)) {
	$line = rtrim(fgets($h));
	// ...
}
fclose($h);

You can split $line with explode.

 

To merge the data keep an array keyed by username that contains all the attributes. Start with an empty array

$users = array();

and for each line create or add an entry for the attribute.

if (isset($users[/* username */])) {
	$users[/* username */][/* attribute */] = /* value */;
} else {
	$users[/* username */] = array(/* attribute */ => /* value */);
}

The resulting $users array will be one step away from the final array you need to convert to JSON.

[nephesh]

Thanks requinix! The "username1, username2", etc. values don't have an actual "attribute" for them, its just the users last name, so somehow I need to get that value set as my example output.

[nephesh]

I am very new to php, so this line you mention: "To merge the data keep an array keyed by username that contains all the attributes. Start with an empty array"

 

I can't quite grasp how to accomplish this, as $line is the array of data and you don't push the $values from it anywhere the empty $users array.

Please forgive my ignorance, I am more familiar with JavaScript.

[requinix]

Thanks requinix! The "username1, username2", etc. values don't have an actual "attribute" for them, its just the users last name, so somehow I need to get that value set as my example output.

Okay... Either I don't understand what you said, or it doesn't change anything with what I said.

 

as $line is the array of data

No, it isn't. $line is a string. It's a line from the file. So "username1 | attribute1 | value1". You have to use explode() to split that line into an array.

 

and you don't push the $values from it anywhere the empty $users array.

My code doesn't have a $value/$values variable, and it does "push" the value in the third bit of code.

[nephesh]

What am I missing then? With this code, I call explode on $line and assign it to the $lineArray, but my foreach outputs "0 =>". if I echo $line in the while loop and see the entire string of users, but I've also tested the supposed array $lineArray with count() and it returns a "1".

$file='C:\\wamp\\www\\faculty-staff-dir.lst';
$h = fopen($file, "rt");
while (!feof($h)) {
$line = rtrim(fgets($h), "\n");
}
fclose($h);


$lineArray = explode(" | ", $line);
foreach($lineArray as $key => $value){
echo "{$key} => {$value}";
}

[requinix]

You're missing the

// ...

which implies there's supposed to be more code in there. And you missed the stuff with $users. And the foreach on $lineArray doesn't belong.

 

In the loop,

1. Read a line

2. Split the line to get the three fields

3. Add to the $users array as shown in the third bit of code I posted

[nephesh]

Requinix, the second part of your code that is to assign a value to an attribute in the $users array won't work, because I don't have a consistent name for the attribute, some users have different attributes than others, that is why I was trying the foreach loop to create an associate array. That is also why I said there is not an attribute "user", it is just the first value in the line.

[requinix]

because I don't have a consistent name for the attribute,

It just hasn't clicked with you yet, huh?

 

Watch:

$line = "username1 | attribute1 | value1";
$lineArray = explode(" | ", $line);

$lineArray[0] is consistently the username, $lineArray[1] is consistently the attribute, and $lineArray[2] is consistently the value.

[nephesh]

While I was waiting, I kinda figured part of it out, but I don't know how to output the username as part of the final $users array. The code I have below outputs like this:

[{"attribute1":"value1"}] [{"attribute2":"value2"}] ... and so on. Would like something : [{ "user": {"attribute1":"value1"}] [{"attribute2":"value2"}] }] or something close so I could do something like $users['user']['attribute1'] to get the value. Once again thanks for all your help and for being patient with me!

$file="C:\\wamp\\www\\faculty-staff-dir.lst";
$h = fopen($file, "rt");
$line = "";

$lineArray = array();
$users = array();
while (!feof($h)) {
$line = rtrim(fgets($h), "\n");
$lineArray = explode("|", $line);

if(isset($lineArray[0])){
$users[0] = array($lineArray[1] => $lineArray[2]);
}
$json = json_encode($users);
print_r($json);
}
fclose($h);

[requinix]

if(isset($lineArray[0])){

That will test if the line had a username*. You already know the line has a username (presumably) so that test is not helping.

 

$users[0] = array($lineArray[1] => $lineArray[2]);

Not only is there no mention of the username anywhere in there, you're taking one array with the current attribute and value and setting it in $users. If you want to merge all attributes then you must have some sort of code that involves merging.

 

So:

while (!feof($h)) {
$line = rtrim(fgets($h), "\n");
$lineArray = explode("|", $line);

Fine so far. You've shown with examples that the line is delimited by pipes surrounded by spaces, so if you split on just the pipes alone then you'll have a bunch of extra spaces all over.

 

Next you should check if the current user exists in $users. Yes, use isset for that, but you have to test if the username (as $lineArray[0]) is a key (using [...]) in the users array (which is $users). Try that one more time.

If the user does not exist then you should create them. Each key in $users is a username and each value is an array of attributes. For simplicity, start each new user with an empty set of attributes. That's an empty array.

 

Finally, regardless of whether the user exists or not (ie, this is the "simplicity"), take your current attribute/value pair and add them to the existing array in $users according to the username. This does not involve creating a new array but adding a new value to one you already have.

 

* Technically it tests if the array has an element in it, but because of how explode() works the array will always have an element in it - even if it's an empty string. So that test is useless.

[nephesh]

I've got it working except it is skipping over a attribute that has a value of a number. Is there a way to rectify this?

 

$file="C:\\wamp\\www\\faculty-staff-dir.lst";
$h = fopen($file, "rt");
$line = "";

$lineArray = array();
$users = array();
while (!feof($h)) {
$line = rtrim(fgets($h), "\n");
$lineArray = explode("|", $line);
$lineArray = array_map('trim', $lineArray);

if(isset($users[$lineArray[0]])){
$users[$lineArray[0]][$lineArray[1]] = $lineArray[2]; 
} else {
$users[$lineArray[0]] = array();
}
}
$json = json_encode($users);
print_r($json);

fclose($h);

[requinix]

No, actually it's skipping the first attribute. The fact that it's a number is irrelevant.

} else {
$users[$lineArray[0]] = array();
}

"If the user does not exist in the array then create them with an empty array," and... that's it. That's all you're doing. You're throwing away the attribute from that same line.

 

It seems you have a habit of picking and choosing parts of what I say and then ignoring the rest of the post. That's not a good thing. Try reading all of what I said in my previous post and compare what I said to what you actually did.

[nephesh]

I am sorry, I created an empty array as you suggested "start each new user with an empty set of attributes. That's an empty array." , but when I add the attributes to the new array like so,

} else {
$users[$lineArray[0]] = array($lineArray[1] => $lineArray[2]);
}

I get two "Undefined offset: 1 in C:\wamp\www\csv-to-json.php on line 17" errors even  though the code is outputting this format:

{"username":{"userID":"1234", "attribute2":"value2"...

[requinix]

but when I add the attributes to the new array like so,

That's not adding attributes to the array. That's creating one new array with the current attribute.

 

You went backwards. What happened to the code you had yesterday?

 

This

if(isset($users[$lineArray[0]])){
$users[$lineArray[0]][$lineArray[1]] = $lineArray[2]; 
} else {
$users[$lineArray[0]] = array();
}

means

if the user exists in the array {
	set attribute=value in the existing (sub)array
} else {
	set the new (sub)array to be empty
}

It should be more like

if the user does not exist in the array {
	set the new (sub)array to be empty
}
set attribute=value in the existing and possibly recently-created (sub)array

Edited October 3, 2017 by requinix

[nephesh]

I'ts still being used, I didn't show it, because I thought you were saying I was leaving out the attribute on the new array. I guess I am confused about what you said was wrong.

 

if(isset($users[$lineArray[0]])){
$users[$lineArray[0]][$lineArray[1]] = $lineArray[2]; 
} else {
$users[$lineArray[0]] = array($lineArray[1] => $lineArray[2]);
}

[Barand]

I don't now what line 17 is, but I suspect the error "undefined index 1" is a symptom of your attempting to process an empty input line.

[nephesh]

Acts the same way even with the new array created before the attributes are added and still gives me the undefined index on this line:

$users[$lineArray[0]][$lineArray[1]] = $lineArray[2];

Here is the code in it's entirety:

$file="C:\\wamp\\www\\faculty-staff-dir.lst";$h = fopen($file, "rt");
$line = "";

$lineArray = array();
$users = array();
while (!feof($h)) {
$line = rtrim(fgets($h), "\n");
$lineArray = explode("|", $line);
$lineArray = array_map('trim', $lineArray);

if(!isset($users[$lineArray[0]])){
$users[$lineArray[0]] = array();
}
$users[$lineArray[0]][$lineArray[1]] = $lineArray[2]; 
}
$json = json_encode($users);
print_r($json);

fclose($h);

[nephesh]

 

Acts the same way even with the new array created before the attributes are added and still gives me the undefined index on this line:

$users[$lineArray[0]][$lineArray[1]] = $lineArray[2];

Here is the code in it's entirety:

$file="C:\\wamp\\www\\faculty-staff-dir.lst";$h = fopen($file, "rt");
$line = "";

$lineArray = array();
$users = array();
while (!feof($h)) {
$line = rtrim(fgets($h), "\n");
$lineArray = explode("|", $line);
$lineArray = array_map('trim', $lineArray);

if(!isset($users[$lineArray[0]])){
$users[$lineArray[0]] = array();
}
$users[$lineArray[0]][$lineArray[1]] = $lineArray[2]; 
}
$json = json_encode($users);
print_r($json);

fclose($h);

 

 

Okay, I think I've got it by changing the attribute assignment line, but it still gives me the undefined index on that line:

$users[$lineArray[0]][0][$lineArray[1]] = $lineArray[2]; 

Whole code:

$file="C:\\wamp\\www\\faculty-staff-dir.lst";
$h = fopen($file, "rt");
$line = "";

$lineArray = array();
$users = array();
while (!feof($h)) {
$line = rtrim(fgets($h), "\n");
$lineArray = explode("|", $line);
$lineArray = array_map('trim', $lineArray);

if(!isset($users[$lineArray[0]])){
$users[$lineArray[0]] = array();
} 
$users[$lineArray[0]][0][$lineArray[1]] = $lineArray[2]; 

}
$json = json_encode($users);
print_r($json);

fclose($h);

[nephesh]

 

Okay, I think I've got it by changing the attribute assignment line, but it still gives me the undefined index on that line:

$users[$lineArray[0]][0][$lineArray[1]] = $lineArray[2]; 

Whole code:

$file="C:\\wamp\\www\\faculty-staff-dir.lst";
$h = fopen($file, "rt");
$line = "";

$lineArray = array();
$users = array();
while (!feof($h)) {
$line = rtrim(fgets($h), "\n");
$lineArray = explode("|", $line);
$lineArray = array_map('trim', $lineArray);

if(!isset($users[$lineArray[0]])){
$users[$lineArray[0]] = array();
} 
$users[$lineArray[0]][0][$lineArray[1]] = $lineArray[2]; 

}
$json = json_encode($users);
print_r($json);

fclose($h);

 

 

Got rid of the undefined index errors. Final working code:

$file="C:\\wamp\\www\\faculty-staff-dir.lst";
$h = fopen($file, "rt");
$line = "";

$lineArray = array();
$users = array();
while (!feof($h)) {
$line = rtrim(fgets($h), "\n");
$lineArray = explode("|", $line);
$lineArray = array_map('trim', $lineArray);

if(!isset($users[$lineArray[0]])){
$users[$lineArray[0]] = array();
} if(isset($lineArray[0][0]) && isset($users[$lineArray[0]])){
$users[$lineArray[0]][0][$lineArray[1]] = $lineArray[2];
} 
}
$json = json_encode($users);
print_r($json);

fclose($h);

Look okay?

 

Now I just need to write the json to a file.

[gizmola]

Are you using the code tags around your code? Use those rather than php. If you are, then where is your indentation. Better yet read and adopt this coding standard. http://www.php-fig.org/psr/psr-2/

 

There can be some gotchas to json_encode() in regards to variable typing of numbers. If your source has numbers in the data, this may look to php like strings and you might need to cast those values to an appropriate number type, unless you don't care about that. Your code is very generic in that regard at the moment.

 

I just have to throw this out there -- that there are component libraries like the symfony2 serializer and JMS serializer libraries that will turn your array into json, xml and yaml, as you like.

 

Now that you have already done the hard part, adding on a component library would be an educational task that could save you a lot of time in the future. For example the symfony2 filesystem component would facilitate this and your job would be done:

 

$fs->dumpFile('faculty-staff-dir.json', json_encode($json));

[nephesh]

Thanks for the suggestion!

One more question, I've decided that I want to work with the $users array before converting it to json, but the array is outputting like:

 

Array ( [username] => Array ( [iD] => 09342095 [Address] => 458 Apt 12)) [username] => Array([iD] => 72372054 [Address] => 232 Center St)) ...and so on

 

I am trying to get the usernames to output from a loop using the code below where the username is the key, but nothing is echoed. What am I missing

for ($i=0; $i < count($users); $i++) { 
    $user = $users[$i];
    echo "Username: ". $user;
}

[Barand]

Use a foreach() loop

foreach ($users as $username => $userdata) {
    echo $username . '<br>';
}

[nephesh]

Thanks Barand, I had figured that out myself while waiting for a response. I think everything is working the way it's supposed to now.