MS Excel cell value is transformed

chima
chima used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Can you post a sample workbook as a small image is not enough
gowlfow

Author

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 Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Glenn RayExcel VBA Developer
Top Expert 2014

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

Author

Commented:
sktneer and Ray, followed Ray's instructions and some cell are fine, but others still display the Dec-
Dec-

Author

Commented:
I think the proper format/setting has to come from the database.
Top Expert 2014
Commented:
did you format the destination column as text BEFORE you did the data import?
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
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 Developer
Top Expert 2014

Commented:
^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.

Author

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
Excel VBA Developer
Top Expert 2014
Commented:
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

Author

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.

Author

Commented:
Thank you all

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial