MS Excel cell value is transformed

Hello,
I'm doing a .csv file export from an application.  For most of the data the Event ID is presented correctly in Excel, except those that have a "12-" prefix.  I'm trying to convince the DBA that it is because of the database representation.  Maybe I am wrong, but I would like to know.
The image below shows the Dec-54 and Dec-84 which should be 12-1954 and 12-2284, respectively.
I tried changing the format of the cells, but the 12-XXXX does not show up.  And because I have done nothing to the other cells, which are correct, these cells should be correct as well.  The Dec- values show up in the .csv file as well.  In both .xlsx and .csv have the problem.
Dec-54 value
chimaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowPartnerCommented:
Can you post a sample workbook as a small image is not enough
gowlfow
chimaAuthor Commented:
gowlfow, sorry I can't.  I am able to do a .xls export from the same app and it does show these cells correctly, and they have a "General" format setting.  The others are "General" as well.  So, it might be the transition from .csv to the .xlsx.  Maybe it is the .xlsx that it does not like.  If I change it to .xls might that resolve the problem.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try selecting Text as Column Data Format for that particular column during the import from the text file and see if that issue gets resolved.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Glenn RayExcel VBA DeveloperCommented:
Instead of opening the CSV file directly, I recommend importing it as a text file.

After selecting the file (menu: Data, Get External Data group, From Text), choose "Delimited".  On the next screen, choose "Comma" as the delimiter, click [Next >].  On the last screen, click on the column with the hyphenated values (ex, "12-1954") and click the "Text" option.  Click [Finish].

steps for ensuring text values
I tested this on a sample csv file that I created in a text editor and it worked.  Here is the sample content:
Blue,WTF,14-3479
Green,GW-352 TB,14-0126
Blue,WTF-KU1A,12-2284
Blue,WTF-KU1B,12-1954

Open in new window

chimaAuthor Commented:
sktneer and Ray, followed Ray's instructions and some cell are fine, but others still display the Dec-
Dec-
chimaAuthor Commented:
I think the proper format/setting has to come from the database.
aikimarkCommented:
did you format the destination column as text BEFORE you did the data import?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
On the Text Import Wizard, did you select the desired column in the data preview window before changing the format to Text? Remember when you select a column in the data preview window, it is highlighted as black.
Glenn RayExcel VBA DeveloperCommented:
^This.  Please see my sample screenshot; the window on the right (step 3 of 3) shows what you should see when you click on that column and then select "Text" as the column data format.
chimaAuthor Commented:
aikimark yes I tried it again.  This time I formated that column as "text" and the results are the same.  Other than not initializing that column, I did follow Glenn's instructions.
File attached
Text-column.csv
Glenn RayExcel VBA DeveloperCommented:
Here are the contents of the Text-column.csv file you attached - as opened in a text editor:
Column A,Equipment Code,Event ID,Start Date Time (MST),End Date Time (MST)
test,test B,12-1859,7/30/2012 8:00,7/30/2012 12:00
test,test B,12-1252,7/30/2012 8:30,7/30/2012 16:00
test,test B,12-1759,7/31/2012 8:00,7/31/2012 12:00
test,test B,12-1253,7/31/2012 8:30,7/31/2012 16:00
test,test B,12-1760,7/31/2012 13:00,7/31/2012 17:00
test,test B,Dec-39,7/30/2012 9:30,7/31/2012 17:00
test,test B,12-1404,7/30/2012 6:00,8/1/2012 16:00
test,test B,12-1887,7/31/2012 9:00,8/2/2012 16:30
test,test B,12-1681,7/31/2012 8:30,8/3/2012 16:00
test,test B,12-1898,7/25/2012 6:42,8/3/2012 17:00
test,test B,12-1884,8/6/2012 9:30,8/6/2012 11:45
test,test B,21-Dec,8/6/2012 13:00,8/6/2012 13:30
test,test B,31-Dec,8/7/2012 9:30,8/7/2012 10:00
test,test B,30-Dec,8/7/2012 9:30,8/7/2012 10:00
test,test B,12-1866,8/7/2012 12:00,8/7/2012 16:00
test,test B,12-1799,8/6/2012 9:00,8/8/2012 17:30
test,test B,Dec-50,8/10/2012 8:00,8/10/2012 14:00
test,test B,12-1067,5/8/2012 9:15,8/10/2012 15:00
test,test B,26-Dec,8/7/2012 10:00,8/10/2012 16:00

Open in new window


You can see that in lines 7,  13, 14, 15, 18, & 20, there has already been a conversion of the original string values to a datestring.  You'll need to find the original, unchanged text/csv file with which to work.

-Glenn

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chimaAuthor Commented:
Glenn Ray, thank you for your response.  Your comment is the point I'm hoping to solve; " I'm trying to convince the DBA that it is because of the database representation."  You and the others responding have proven the point that the proper data string needs to originate at the database.  I'll be closing this ticket shortly.
chimaAuthor Commented:
Thank you all
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.