Adding a ' to Multidimensional Array Values

I have a multidimensional array that I need to input into a tab delimited spreadsheet. My problem is that I have leading zeros on some of the values that get dropped when it’s put into Excel. I’ve had this issue before and adding a single quote ’ at the beginning of the value tells Excel to read the value as is and it reads properly.

I’m having issues getting the ’ into the specific value of the multidimensional array.

My array is as follows:

$data = Array ( [0] => Array ( [order_no] => [label] => [label_id] => [artist] => Joe Sample 
[title] => Sample Title [cost] => 2.99 [media] => DVD [upc_no] => 000742155776 [isbn_no] => [product_no] => 000742155776 
[price] => 5.99 [qty] => 1 [location] => 6 [comments] => [in_price_change] => 0 ) [1] => Array ( [order_no] => [label] => 
[label_id] => [artist] => Jim Sample [title] => Fake Title [cost] => 1.99 [media] => DVD [upc_no] => 030742557876
[isbn_no] => [product_no] => 030742557876 [price] => 4.99 [qty] => 1 [location] => 6 [comments] => [in_price_change] => 0 )

I’m thinking I should run $data through a foreach loop and add the ’ to the [upc_no] & [product_no] values.

I can’t quite get any combinations of this to work. Am I off on my thinking? I would think that this would be possible. Any help would be appreciated.

Thank you in advance.

what you showed us is not the array - take this is an example of an array:


$data = array( 'product_no' =>'030742557876', 'price' => 4.99);

This is the result of
$d = var_export($data, 1);
echo $d
;


array ( 'product_no' => '030742557876', 'price' => 4.99, )

This is the result of
var_dump( $data );


array
  'product_no' => string '030742557876' (length=12)
  'price' => float 4.99

This is the result of
print_r($data);
(which is what you posted)


Array ( [product_no] => 030742557876 [price] => 4.99 )

If your original array contained an unquoted integer ie. 030742557876 then the results would have been vastly different …

So, are you saying you have to loop through your array elements and create a csv file containing quoted fields?

I think you’ll have to make sure the values with leading 0’s in your array are a string in the array because otherwise I think php will also ignore the leading 0’s when it reads the values.

But in any case, this might help you get started. Note though, that although a single quote is prepended to the upcNo values, any leading 0’s have been ignored which is not what you want. It might be better to get the code that creates your array to make values with leading 0’s strings.

This might give you an idea of the type of looping involved in looping through your multi-dimension array.

<?php

$myArray = array();
  $myArray[0] = array('artist' => 'artist1','upcNo' => 000123456);
  $myArray[1] = array('artist' => 'artist2','upcNo' => 0005678);
  $myArray[2] = array('artist' => 'artist3','upcNo' => 56789123);

  $handle = fopen('outFile.txt',w);

  foreach($myArray as $row){
      $str = '';
      foreach($row as $key => $val){
          $str .= ($key == 'upcNo')? "'".$val.',' : $val.',';
      }
      fwrite($handle, substr($str, 0, -1)."\
");
  }
  fclose($handle);
  echo 'Done';

?>

The effect of an unquoted integer starting with 0 is much worse than that,


// quotes removed from the first array value
$data = array( 'product_no' => 030742557876, 'price' => 4.99);

var_dump( $data );

// gives
array
  'product_no' => int 6538607 // eek!
  'price' => float 4.99

Because the int value for product_no is unquoted, it is read to be an Octal number.

Integers Manual page Quote:


$a = 1234; // decimal number
$a = -123; // a negative number
$a = 0123; // octal number (equivalent to 83 decimal)
$a = 0x1A; // hexadecimal number (equivalent to 26 decimal)

You’ll possibly need to typecast those vals as strings.

The point I am trying to make is that if the op can get the code that creates his/her array to enclose values with leading 0’s in quotes then life should become a whole lot easier.

For example:

<?php

$myArray = array();
  $myArray[0] = array('artist' => 'artist1','upcNo' => '000123456');
  $myArray[1] = array('artist' => 'artist2','upcNo' => '0005678');
  $myArray[2] = array('artist' => 'artist3','upcNo' => 56789123);

  $handle = fopen('outFile.txt',w);

  foreach($myArray as $row){
      $str = '';
      foreach($row as $key => $val){
          $str .= $val.',';
      }
      fwrite($handle, substr($str, 0, -1)."\
");
  }
  fclose($handle);
  echo 'Done';

?>


and outFile.txt then contains:


artist1,000123456
artist2,0005678
artist3,56789123


It’ll probably be easier and less code to make all the upcNo values in the array a string.

And if for some reason they want a leading ’ infront of the upcNo values then you can replace

$str .= $val.',';

with

$str .= ($key == 'upcNo')? "'".$val.',' : $val.',';

and the output will be:


artist1,'000123456
artist2,'0005678
artist3,'56789123

Cups and Max Height,

Thank you both for your replies.

Cups, yes, what I quoted in my original post was in fact the result of print_r($data);. Sorry for the misunderstanding on the labeling. Would you mind explaining the difference in a little more detail what the differences are between the examples you posted, just for future knowledge?

Max Height, I believe that the value for upc_no is a string. I’m pulling the value via an mysql query. The value in the table is a VarChar as some of the product numbers actually contain letters. Probably should not call them product numbers, but I’m working with an existing system and trying not to overhaul too much. Not sure if that would make them a string or not.

I was able to add the ’ to all the values contained within the array using a foreach loop within another loop. That gets the product_no to work fine. Only problem is it leaves the ’ at the beginning of the cell for any values that don’t start with 0. I believe the problem lays with how Excel pastes the values into the spreadsheet. I think the default cell format is a general number which Excel will automatically drop the leading zeros for unless it has the ’ at the beginning to tell it to paste the number as is.

I’ll take a look at the posts you were both so generous to help with and try to work the code. I think you’ve given me a good direction to head in. I’ll post my results.

Thank you again.

ok, that was going to be my next question - where is your array coming from?

So now it appears you are extracting data from a database table and outputing a csv file containing the data. If that is the case, then you shouldn’t need an array at all.

Your data is stored in the table as a varchar with a value say, 00123456. In the select query’s result set, it will also be stored as 00123456. So all you should need to do is loop through the result set and first check if the value has a leading 0. If it does, prepend a single quote to the value (for Excel’s purposes) before outputing the value to the csv file.

So all you should need to do is loop through the result set and first check if the value has a leading 0. If it does, prepend a single quote to the value (for Excel’s purposes) before outputing the value to the csv file.

I’ll give that a whirl. Thanks!

I might be guilty of going off at a bit of a tangent there, I admit.

Still, one of the points I frequently make, is that if you have an existing array and you’d like to share it with us on here, showing us the output of print_r or even var_dump means we have to type the whole thing out again in order to run any tests on the code. Not only is it a PITA, it also means we often end up reducing the array – as both replies in this particular thread clearly illustrate.

Whereas if you had used :


$d = var_export($data, 1);
echo $d;
// gives
array ( 'product_no' => '030742557876', 'price' => 4.99, )  

you’d be giving us an array we can copy/paste and play with on your behalf. You’ll be more likely to get quality replies.

Going back to your question which as it turns out seems to be, How do I extract a csv text file from Mysql? then you might be surprised to learn you can have Mysql do this for you (there may well be some permission issues you need to grapple with, I don’t make light of that).

[google]Mysql export as csv[/google]

Cups: Thank you for explaining. I get what your saying. Sorry for the additional work I may have created. I thought copy and pasting the code I quoted would do the trick. Probably a lot more room for error as well having to type them out. I’ll be sure to do the var_export from now on.

As far as my issue. I got it solved. It’s actually a Tab Delimited file I need it exported as. I was able to get the single quote in front of the upc_no and import it to Excel with the help of your examples. Didn’t work as I had planned. I’ve done it before with a leading zero for zip codes and that solved the problem.

I knew Excel was getting the value with the 0, so I started poking around in Excel. What I found that worked was to save the Tab Delimited file, open it as a notepad. Copy and paste the info. Then used Excel’s text import wizard and on the last step of that, changed the format of that column to Text from General. That did the trick.

Thank you both for your help and some gained knowledge!
CFenn