Solved

MYSQL / CSV / EXCEL /

Posted on 2010-09-02
9
503 Views
Last Modified: 2012-08-13
I have a question:  I'm retrieving data from a table from a mysql database and the fields that are coming out are in the form of:
DOLEI3239DDON
3232DJUIJHDID7
32982939JIOJN3
DKJDFOIU2KE32
DFA3TIU9J22N3

OK so im able to get a CSV file with the result set.  Each column has the correct values.  BUT THERE IS ONE PROBLEM.
If you notice the entry DKJDFOIU2KE32 is a regular string, but when the user opens up the CSV file in EXCEL or any MSOffice 2010, it treats the last 3 characters, E32 in this case, as an EXPONENT!!!! so the string value becomes not DKJDFOIU2KE32 but rather DKJDFOIU2K344342234223393824982346 or whatever else...  

MY QUESTION: I know Microsoft Office 2010 is based on XML tags, so is there some kind of setter of doctypes for this so that i can restrict to how the data is treated or to specify to ignore the E as an exponent if its in the last few positions of the string or something..  I need a regular string to display, im passing it as a string and treating it like one, but its being treated as a number BY EXCEL (rather the exponent part is being treated as a number)!  

NOTE: if the user opens it with textpad, notepad, wordpad everythign appears FINE!!!! but if its excel then its messed up.  PLEASE HELP!!!!!!!!
0
Comment
Question by:Squadless
  • 4
  • 4
9 Comments
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 500 total points
Comment Utility
rename the file, changing the .CSV file type to .TXT before you do your import.  Excel does weird things with CSV files.
0
 
LVL 1

Author Comment

by:Squadless
Comment Utility
The thing is the user wants to see it in Excel, so changing the fileType isnt an option.  I need to somehow force the column in excel to be a unmodified String before its populated from the result set.
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
please try the rename and import
0
 
LVL 1

Author Comment

by:Squadless
Comment Utility
I think i explained it bad.  I am not importing.  I am exporting.  The data is already in the database.  I have a site that generates reports.  The data is aggregated and is spit back to the user in the CSV format which average users open up using EXCEL.  ALL the columns are treated as general strings, except when at the end of the string there's a E32 or EXX (xx being any 2 digit number) Excel thinks that thats an EXPONENT and expands it to like 329238479237840293847023948702394.  

How can i set exponent off or something of that sort in excel? so the user doesnt have to open it with Textpad or notepad and can still use excel but the column would look normal.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
Comment Utility
I understand what you are trying to do.  Please do the thing that I asked you and report the results in a comment.
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
How are the users opening the file?

Do you have any control over that?

How are you actually distributing the file/data to the user?
0
 
LVL 1

Author Comment

by:Squadless
Comment Utility
@ aikimark I believe I've tried that already but not 100% certain.  I will try it now again.

@ imnorie: Users are double clicking on the file.  0 control over what they open it with.  
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
It might help us if you posted a sample of the MySQL output.  That way we could test it.

How much control do you have over your users and their environment?

What do the users do with the data they view?
0
 
LVL 1

Author Closing Comment

by:Squadless
Comment Utility
its good
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now