Solved

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

Posted on 2014-10-16
6
77 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This article discusses how to create an extensible mechanism for linked drop downs.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

827 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