phpnewbie112 Posted November 28, 2008 Share Posted November 28, 2008 Hello, I have a small script done under phpmaker to manage my database entries, in one column I have number that starts all with 0 ex 08653, 03658 when I export to csv, I am loosing this 0, could you pls advice how can I maintain this 0 maybe by changing the variable format? Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted November 28, 2008 Share Posted November 28, 2008 Numbers don't have leading zeros, but a formatted string consisting of numeric digits can. I'm going to guess that your field is actually a character/text/string type? You would generally need to treat this as a string and enclose it in quotes to retain all the characters. Quote Link to comment Share on other sites More sharing options...
phpnewbie112 Posted November 28, 2008 Author Share Posted November 28, 2008 this is the part exporting the number: $sCsvStr .= '"' . str_replace('"', '""', strval($addressbook->number->CurrentValue)) . '",'; Quote Link to comment Share on other sites More sharing options...
Mark Baker Posted November 28, 2008 Share Posted November 28, 2008 If the value is a numeric anyway, you shouldn't need the str_replace' $sCsvStr .= '"' . str_pad(strval($addressbook->number->CurrentValue),12,'0',STR_PAD_LEFT) . '",'; Where 12 is the number of characters we want in the number, padding with leading zeroes if necessary. And why are you actually building a CSV string yourself instead building an array of values and then using PHPs fputcsv() function to convert it to a CSV Quote Link to comment Share on other sites More sharing options...
phpnewbie112 Posted November 28, 2008 Author Share Posted November 28, 2008 actually it is the code generated by phpmaker. I just notice something that the problem might be coming from Excel when opening the csv file it is deleting the zero coz I opened the file in notepad and it is working fine Quote Link to comment Share on other sites More sharing options...
Mark Baker Posted November 28, 2008 Share Posted November 28, 2008 I just notice something that the problem might be coming from Excel when opening the csv file it is deleting the zero coz I opened the file in notepad and it is working fine Excel does that... it converts numeric values in CSV files to numbers, which are displayed using the default Excel format for numeric. Is this the behaviour that you want, so that you rusers simply open the CSV file using whatever software they want; or do you actually want to generate an actual Excel file, where you can control the formatting? Quote Link to comment Share on other sites More sharing options...
phpnewbie112 Posted November 28, 2008 Author Share Posted November 28, 2008 I want to display the correct format no matter what software is used to open the csv file. do you think we can do it? Quote Link to comment Share on other sites More sharing options...
Mark Baker Posted November 28, 2008 Share Posted November 28, 2008 I want to display the correct format no matter what software is used to open the csv file. do you think we can do it? That can't be done. Excel will always do what you've seen, and nothing you can do to the CSV file will change that. The only way to get the data correctly formatted when opened in Excel is to actually write an Excel file rathe rthan a CSV file.... but if it's an Excel file, then the users won't be able to open it in Notepad and see their data. Quote Link to comment Share on other sites More sharing options...
Mchl Posted November 28, 2008 Share Posted November 28, 2008 As far as I remember, Excel has the option to select data type for each column when importing CSV file, so it's up to user to do this. Quote Link to comment Share on other sites More sharing options...
Mark Baker Posted November 28, 2008 Share Posted November 28, 2008 As far as I remember, Excel has the option to select data type for each column when importing CSV file, so it's up to user to do this.Yup, but only if you explicitly use the import function. Most people don't even know that it exists, and simply double-click on a CSV file, and its set to open in Excel using default behaviour. Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted November 28, 2008 Share Posted November 28, 2008 The ="0123" suggested in the last post at this link seems to work - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=974947&SiteID=1 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.