Solved

MYSQL / CSV / EXCEL /

Posted on 2010-09-02
9
513 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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