Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MYSQL / CSV / EXCEL /

Posted on 2010-09-02
9
Medium Priority
?
538 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 2000 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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 2000 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

721 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