Solved

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

Posted on 2014-10-16
6
82 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 110

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 110

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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

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 110

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 110

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Suggested Solutions

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
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 dynamically set the form action using jQuery.

710 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