Solved

MYSQL / CSV / EXCEL /

Posted on 2010-09-02
9
510 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
ID: 33592973
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
ID: 33593101
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
ID: 33593152
please try the rename and import
0
 
LVL 1

Author Comment

by:Squadless
ID: 33593305
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 33593469
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
ID: 33598088
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
ID: 33598636
@ 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
ID: 33599325
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
ID: 33749190
its good
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

920 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

12 Experts available now in Live!

Get 1:1 Help Now