• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 103
  • Last Modified:

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

0
brucegust
Asked:
brucegust
  • 4
  • 2
1 Solution
 
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
 
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now