Solved

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

Posted on 2014-10-16
6
75 Views
Last Modified: 2014-10-20
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
Comment
Question by:brucegust
  • 4
  • 2
6 Comments
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 40385360
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
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 40385369
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
 

Author Comment

by:brucegust
ID: 40385370
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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 

Author Comment

by:brucegust
ID: 40385371
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
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 40385389
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
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 40385408
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

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question