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.
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.
$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.
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.
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 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.
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).
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