Zeros truncated in CSV output

susmitha

Member
Hi all,

When trying to print LITM in CSV output, zeros are truncated. For example if LITM=000012345 then only 12345 is getting printed in output.
I tried to solve this problem by concating "' " to LITM. But now even this is getting printed in CSV('000012345).
Is there any other solution for this??

Thanks in advance
Susmitha
 

jiju

Active Member
Hi,
Kindly try this ="value" when trying to print it on the csv.Hope this will help.

Regards
Jiju
 

susmitha

Member
Hi All,
Thanks for prompt replies.
I tried all the above mentioned solutions but still my problem is not solved.
Any other suggestions??

Thanks
Susmitha
 

susmitha

Member
Hi ,
Thanks for prompt replies.
I tried out all the above solutions but still not able to solve the problem.
Any other suggestions are welcome.

Thanks
Susmitha
 

eydeak

Reputable Poster
Hello Susmitha,



What are you viewing/printing the CSV file with?



To verify that you are getting the correct output in a CSV file, you
should open it up in a text editor. If you can see the whole value
(000012345) in the text editor, then the data is being generated
correctly.



What's probably happening is that you are opening the CSV file in Excel
and Excel is trying to be too smart. Excel looks at something like
000012345 and thinks it is a number so it drops the leading zeroes. Once
it has dropped those leading zeroes, you can't really get them back
inside Excel.



So, one question I would have for you is where is the CSV data supposed
to end up? If it is for an interface, then you probably don't really
need to do anything, just be aware of how Excel changes the data when it
reads it. If Excel is the target of the data, then you need to trick it
into believing that the LITM is text and not numeric. To do this, rename
your file to .txt instead of .csv. Then open the .txt file in Excel and
it will take you through the Text Import Wizard. On Step 3 make sure you
select the LITM column and specify that it is Text. When you finish
opening the document, it should treat LITM as text and display the
leading zeroes.



Good Luck,



Ellen Deak

Senior Systems Developer

Cooper Standard Automotive

JDE EnterpriseOne (OneWorld XE Update 7 SP23_M1), AS400 DB2 V5R3M0,
Citrix Clients, Excel 2003 SP2
 

eydeak

Reputable Poster
Hi Gene,



I've never had any luck changing the column properties to text after
opening the CSV in Excel. It's as if Excel has already removed the
leading zeroes and can't get them back. That's why I go through the
process of changing the extension to TXT and importing the file.



Have you actually been able to get the leading zeroes back by simply
changing the cell format from number to text?



Ellen Deak

Senior Systems Developer

Cooper Standard Automotive

JDE EnterpriseOne (OneWorld XE Update 7 SP23_M1), AS400 DB2 V5R3M0,
Citrix Clients, Excel 2003 SP2
 

eydeak

Reputable Poster
Hi Susmitha,



I've never had that TXT solution not work for me, so now I'm wondering
if I really understand your problem. Could you give us some more details
about what you are trying to do?



1. How are you producing the CSV file?
2. Exactly how are you trying to open the CSV file?
3. How are you trying to print the CSV file?



Please include all of your version information as well (see my signature
for an example). So far, I've been assuming you are trying to open the
file in Excel, because that is usually the default for opening CSV
files. So, please include the version of Excel you are using and if you
aren't using Excel, please tell us what application you are using and
its version information.



Also, if you could post a sample of the CSV file you are trying to work
with, I would like to try opening it myself to see what happens. Please
note that you need to include the raw CSV file. Do not open the CSV file
and do a save-as somewhere, because once you've opened it in excel you
lose the leading zeroes and the new file won't contain the leading
zeroes any longer.



Ellen Deak

Senior Systems Developer

Cooper Standard Automotive

JDE EnterpriseOne (OneWorld XE Update 7 SP23_M1), AS400 DB2 V5R3M0,
Citrix Clients

Excel 2003 SP2
 

Wendell

Active Member
Hi Susmitha,

I know this is a very simplistic recomendation, but I have been fooled by looking at the wrong file thinking it was my test output. Try searching for and discarding or renaming all the CSV files with the name you are generating with your UBE or application.

Secondly, Ellen had a very good point about using a simple text editor to view the CSV (Notepad is a common choice).

Regards,
Wendell
 

craig_welton

Legendary Poster
I think Jiju had this right ...

To stop Excel from stripping of the leading zeros the data needs to look like this

"=""000123"""

so one CSV record containing AB#, ALPH and ZIP would look like

10001,"SIMPSON, HOMER","=""01234"""

You are using LITM so try:
RV LITM = concat(concat('="',rtrim([BC 2nd Item Number],' ')),'"')

The first series of character is single quote, equal sign, double quote, single quote.
The last series of characters is single quote, double quote, single quote. (I noticed that is really hard to read when previewing the post)

Ugly but it worked when I had the same issue.
 

susmitha

Member
Hi All,

I am working on 8.11.1 and tools release 6.10

Setup for this UBE in F93081 is "UTF8".
When trying to run the UBE in CSV mode it doesn't open in .txt instead opens in .csv directly.
Zeros are truncated and displayed but i checked in the PDF it shows complete number.
I tried solution RV Item number = concat(concat('="',rtrim([PC 2nd Item Number (F553002W) (LITM)],' ')),"")
but then output has =" included for ex: ="000481201

Larry i am not able to open the link that you sent.
Any other link??

Thanks
Susmitha
 

BBritain

VIP Member
Susmitha,

Try going to Explore and hold the Shift key down while right mouse clicking on the .csv file. You will see and "Open with...." to select and then you can specify Notepad. This should bypass the "....instead opens in .csv" problem.

I am still trying to wrap my brain around "Zeros are truncated and displayed...." statement. Perhaps you can attach the "UNMODIFIED" csv file, if it is not too big, to your post.

Ben again,
 

Larry_Jones

Legendary Poster
You never answered Ellen's question, what application/program are you using to open/view the CSV file with? We're all assuming Excel, but that isn't necessarily so. I you are not using Ecel then all bets are off.

Here's an excerpt from the Link I posted earlier:
--------------------------------------------------------
Excel will always remove leading zeros from fields before displaying them. It will also always remove leading spaces. It insists on helping you, even if removing these leading zeros will, for example, cause your numerically keyed records to sort out of order.

There is a solution

If you have a table for export in which leading zeros and spaces have significance AND that file will only be viewed in Excel, then you can use an Excel specific convention in those fields where leading zeros or spaces should be preserved (in Excel).

To implement this fix, double quote each field with leading zeros or spaces and place an equals sign (=) directly ahead of the first quote, like this:

="08075"
-------------------------------------------------
 

susmitha

Member
Thanks Larry it opened for me today.
I was trying to open the .CSV in excel.
My problem got solved.
variable equals ="string"
RV variable = concat('="',concat(ltrim(rtrim([String],' '),' '),'"'))
Thanks a lot for all valuable suggestions.
Jiju suggested me this before but didn't exactly understand what he meant.

This is the second time i got my issue solved through JDELIST
smile.gif


Thanks,
Susmitha
 
Top