I need to tweak this code that creates a csv file - Part II

The data that's going into the csv file is fine, but my user wants the posted time to read as mm/dd/yyyy h:mm:ss. Right now, it shows up as mm/dd/yyyy h:mm. I can change it manually, but is there a way I can dictate the way it's entered into the csv file? The data is sound, as far as it being in the database as 2014-03-07 22:56:59, but when it hits Excel, it's defaulting to a format that omits the seconds. How can I fix that?

Here's the code that I'm using right now:

$michelle="select actor_id, actor_display_name, posted_time, geo_coords_lat, geo_coords_lon, location_name from twitter_csv_test where geo_coords_lat BETWEEN '$lat_1' AND '$lat_2' and geo_coords_lon BETWEEN '$lon_2' and '$lon_1'";
$michelle_query=mysqli_query($cxn, $michelle);
if(!$michelle_query)
{
$rats=mysqli_errno($cxn).': '.mysqli_error($cxn);
die($rats);
}
$michelle_columns=mysqli_field_count($cxn);

//gets the field names from your table and sets them up as the first row in your csv file

$heading=mysqli_fetch_fields($michelle_query);

foreach ($heading as $val)
{
	$output .='"'.$val->name .'",';
}
$output .="\n";


while($michelle_row=mysqli_fetch_array($michelle_query))
{
	for($i=0; $i<$michelle_columns; $i++)
	{
		$output .='"'.$michelle_row["$i"].'",';
	}
$output .="\n";
}

$filename="twitter.csv";
header('Content-type:application/csv');
header('Content-Disposition: attachment; filename='.$filename);
echo $output;

Open in new window

brucegustPHP DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
I don't believe you can fix it from the server.  I think it's a setting in Excel.  When you generate a CSV file, it's got the data but that's about all.  The display format is controlled on the client's machine.

The only thing I could think to try on the server side would be to put it into single quotes.
0
Ray PaseurCommented:
In Excel, I highlighted a range of cells then did:
Right-Click -> Format Cells -> Number -> Date and did not find anything useful, however...

Right-Click -> Format Cells -> Number -> Custom looks promising.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brucegustPHP DeveloperAuthor Commented:
I'll give it a shot, but I was wondering if that wasn't the case.

If I did try to put the data in single quotes, how would this change:

while($michelle_row=mysqli_fetch_array($michelle_query))
{
      for($i=0; $i<$michelle_columns; $i++)
      {
            $output .='"'.$michelle_row["$i"].'",';
      }
$output .="\n";
}

Would '"'.$michelle_row["I"]."',';  become '''.$michelle["I"].'''?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

brucegustPHP DeveloperAuthor Commented:
And yes, I did the formatting and it looks fine. My end user isn't confident that his team is going to know how to do that and wanted to know if I could do it programmatically.
0
Ray PaseurCommented:
I'll show you the code change if you'll give us the test data.  I've been programming long enough to understand that programming without test data is just guessing!
0
Ray PaseurCommented:
Just tested the single quotes idea.  It didn't work - Excel assumed the quotes were part of the data and cannot use it as a date.  Sorry, but I think the end user is going to have to give his team a quick lesson on Excel.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.