2

I have a code which creates a CSV file and puts certain data in there. Some of this data is text and some are numeric strings. When this CSV file is imported in Excel the program removes the leading zeroes from the numeric strings (phone numbers and zip codes). Is there a way I can format/change these numeric string values so that the Excel can read them in a way that it'll keep the leading zeroes? Or is just the Excel the problem and this problem should be worked from there?

I have tried adding apostrophe before the numeric strings and the numbers will be there but the apostrophe will also stay and I don't want that.

$dataorder = ["Receiver:", $_POST['orderperson'], $_POST['address'], $_POST['postnumber'], $_POST['city'], $_POST['email'], $_POST['phone']];

fputcsv($fh, $dataorder, $delimeter);
tuomvii
  • 55
  • 6
  • It is an excel problem, not PHP. Use the "import from text" command and import the phone number, zip code fields as text. – Salman A Apr 11 '19 at 11:07
  • In Excel you add a `'` single quote prefix to tell excel this is a text even if it contains numbers. So add a `'` to the fields in the PHP before you write them to the csv file – RiggsFolly Apr 11 '19 at 11:14
  • The problem when I import the file is that Excel removes the 0 right from the start. Changing the format to text doesn't bring it back. – tuomvii Apr 11 '19 at 11:15
  • Riggs, like I said in my text, I tried it, but I don't want the quote to stay there even if the zeroes aren't removed. – tuomvii Apr 11 '19 at 11:21
  • @SalmanA oh right, I hadn't used that Power Query view which helped me format the fields to text before importing. Thank yoU! – tuomvii Apr 11 '19 at 11:28

1 Answers1

0

Excel does not know how to handle a field which contains just numbers and thus it trims any leading zeros. There is nothing you can do at the CSV generation process. That you can do is tell excel how to treat each data column when importing the CSV file. At step 3 when you import text data (Data Tab > From Text > Select csv file), excel let you choose each column data type which default is General; there change it to Text for the numeric fields and it will keep all the leading zeros. Here is a screenshot with the step 3 of the text importer, it's in greek but you'll get the point (Γενική -> General, Κείμενο -> Text):

Excel CSV import

Christos Lytras
  • 36,310
  • 4
  • 80
  • 113