Solved

MYSQL / CSV / EXCEL /

Posted on 2010-09-02
9
530 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
[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
  • 4
9 Comments
 
LVL 46

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 46

Expert Comment

by:aikimark
ID: 33593152
please try the rename and import
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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
 
LVL 46

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 34

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 46

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

634 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