.CSV files

[padshore@optonline.net]

Can you post examples of the data that is in alpha fields, from within the CSV file include quotes and comma's Thanks in advance Alan

[Guest.Visitor]

.CSV files

deanski wrote: > I am using the CPYTOPCD command to copy a .CSV file to a folder, then > emailing this in Excel format. When bringing this up in Excel, it is > deleting the leading 0's (Zeros)from alpha fields. What do I need to > do to correct this? Thank you I would strongly suggest you consider using MS Query to directly download the data from the original table to the spreadsheet. It's quite simple and will always handle situations like your problem correctly. Bill

[deanski]

.CSV files

I am not familiar with MS Query. Where can I go to get info. This file is being created on the AS/400. Please answer to dean.hedberg@bfpna.com as I don't get on Mid-Range that often. Thank you

[deanski]

.CSV files

Will get to it today. May I send it directly to padshore@optonline.net? Thank you dean.hedberg@bfpna.com

[Guest.Visitor]

.CSV files

Will the users be doing any math in the spreadsheet with these numbers? If they aren't, then another way to keep leading or trailing zeros is to put a '"' before and after the field when you write it on the iSeries. I discovered this method at a former job where I was writing 7-digit House Bill numbers out of the Air Freight Forwarding system that my company marketed. I was writing a number with several trailing zeros and the spreadsheet showed an exponential formula instead of the House Bill number. This is an assigned number from the system and no math operations would be performed. So, I was free to put '"' around it so that it would display as we expected it to.

[padshore@optonline.net]

.CSV files

Just cut and paste and post it to Mid-range. That way other people can gain from this thread, as well as answer if they see something. Alan

[bharder@nlrha.ab.ca]

.CSV files

I have lots of experience with this type of problem. The issue is with Excel. The problem is that Excel is simply too smart. It's a spreadsheet and it's designed to work with numbers. In a "real" number, leading zeros are not logically significant, so Excel feels free to remove them, so it does. The same problem occurs with dates, by the way. This issue is not important if you (or your clients) are not modifying the spreadsheet and saving it back to the CSV again. Of course, that's not often realistic. There is a way, however. Excel is performing a transparent import of the file based upon it's file extension (.CSV). I recommend using NotePad as the safest way of manipulating CSV files. NotePad is admittedly less convenient and powerful. However, you can use Excel too, although it requires more steps. If you change the extension to .TXT for instance, Excel will open the file and place every line, completely, in Column A. Then you use Data | Text to Columns and choose Delimited (by Commas). When the wizard gives you the chance to format the columns, choose Text rather than General. This is the crucial step!! With a column setting of General, Excel examines every field in that column, on an individual basis, and decides it's data type (essentially Numeric, Date, or Character). When you choose Text, you force Excel to use Character every time, no matter what the cell actually contains. That will retain your leading zeros on your numbers. Then when you save it, save it as CSV again.

[Guest.Visitor]

.CSV files

deanski wrote: > I am not familiar with MS Query. Where can I go to get info. This > file is being created on the AS/400. Please answer to > dean.hedberg@bfpna.com as I don't get on Mid-Range that often. Thank > you Here's a previous discussion: http://mcpressonline.com/mc?13@157.j...283@.f01eb74/7 Bill

[Guest.Visitor]

.CSV files

The "smartness" of Excel can cause problems when going from an .XLS to a .CSV and vice-versa. One possible alternative is utilizing the Java HSSF classes for reading from and writing to .XLS files. Here is a link for information about POI and HSSF. http://jakarta.apache.org/poi/

[deanski]

.CSV files

CPYTOPCD FROMFILE(&TXTFILE) TOFLR(&USER) + TODOC(&CSVFILE) REPLACE(*YES) TRNFMT(*TEXT) SNDDST TYPE(*DOC) TOINTNET((&EMAILADDR)) + DSTD(*NONE) MSG(&MESSAGE) DOC(&CSVFILE) + FLR(&USER) SUBJECT(&SUBJECT) AS/400 File .csv file CUST# PART# Cust# PART# 1. 032601 150015-JV 32601 150015-JV 2. 032601 150022 32601 150022 3. 11905 100002615 11905 1E+08 4. 11905 100012649 11905 1E+08 5. 11905 100021110 11905 1E+08 NOTE: THESE ARE ALPHA FIELDS Lines 1&2 "0" stripped Line 2 Part# right adjusted Lines 3-5 Part#'s completly changed ??? Thank you

[CWAdams]

.CSV files

Here is a method to format Numbers that will allow you to include leading zeroes as well as making a number appear in a more readable format: Custom Formats for Excel Numbers • Select the column or cell • Select the FORMAT Menu • Select CELLS • On the NUMBER tab, select CUSTOM • On the right under the TYPE list, select the single zero (0) • Now you will see an input area between the heading TYPE and the list from which you selected the single zero. Position the cursor to this input area. • Key in the number of zeroes equal to the length of the field. You can also use the CUSTOM format to apply a mask (edit word) to any number such as a telephone number. Example: Number in cell: 8005551234 Customer Mask: (000) 000-0000 {Note space after (000)} Formatted cell: (800) 555-1234 International Phone number: 00 (0) 0000 00 0000 Formatted cell: 44 (0) 3333 22 9999

[deanski]

.CSV files

My problem is not numbers but alpha fields. It is stripping leading zeors. Ex: 03286 is 3286 100002615 & 100012649 = 1E+08

[padshore@optonline.net]

.CSV files

CWadams is correct in what you should do as far as the excel file is concerned. The fields you mentioned may be alpha in the AS400 file, but within the excel spreadsheet, they become numeric. Alan

[CWAdams]

.CSV files

Excel automatically determines the type of data for a column based on the first few rows of data. Even though you consider values like "03286" and "100012649" as character, Excel found only digits and has made the data numeric. Numbers that are shown in numeric format (1E+08) are usually shown in this format because the column is not wide enough. Try increasing the width of the column and you should see the actual numeric value. I have used the method described in my previous note (Custom Formats in Excel) to add leading zeroes to dates (DDMMYYYY format) that had been written to a tab-delimited file by MS Access DB. If you have small and large values in the same column, you may have problem if you want to limit the number of leading zeroes in the small values.

[deanski]

.CSV files

Thank you both