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? Link to comment https://forums.phpfreaks.com/topic/134613-export-csv-and-0-number/ 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. Link to comment https://forums.phpfreaks.com/topic/134613-export-csv-and-0-number/#findComment-700871 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)) . '",'; Link to comment https://forums.phpfreaks.com/topic/134613-export-csv-and-0-number/#findComment-700872 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 Link to comment https://forums.phpfreaks.com/topic/134613-export-csv-and-0-number/#findComment-700877 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 Link to comment https://forums.phpfreaks.com/topic/134613-export-csv-and-0-number/#findComment-700888 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? Link to comment https://forums.phpfreaks.com/topic/134613-export-csv-and-0-number/#findComment-700889 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? Link to comment https://forums.phpfreaks.com/topic/134613-export-csv-and-0-number/#findComment-700904 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. Link to comment https://forums.phpfreaks.com/topic/134613-export-csv-and-0-number/#findComment-700912 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. Link to comment https://forums.phpfreaks.com/topic/134613-export-csv-and-0-number/#findComment-700914 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. Link to comment https://forums.phpfreaks.com/topic/134613-export-csv-and-0-number/#findComment-700925 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 Link to comment https://forums.phpfreaks.com/topic/134613-export-csv-and-0-number/#findComment-701077 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.