chima
asked on
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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].
I tested this on a sample csv file that I created in a text editor and it worked. Here is the sample content:
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].
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-1954ASKER
sktneer and Ray, followed Ray's instructions and some cell are fine, but others still display the Dec-
ASKER
I think the proper format/setting has to come from the database.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
^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.
ASKER
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
File attached
Text-column.csv
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
Thank you all
ASKER