CSV file output...

I am having a problem with a field that I am formatting and displaying on a CSV file

Basically I need to concatenate 2 string fields (JBCD and JBST)

If the combination of JBCDJBST = Alphanumeric - I do nothing and print the value out to the strig

But if the combination of JBCDJBST is Numeric - then I need to do JBCD.JBST.

Everything works great except the fact that if I have a 0 on the end of my string, it is being removed.

eg. 702055420 shows as 702055.42 rather than 702055.420

The only solution that seems to work at the moment is if I concantenate a single quote to my string '702055.420 - the problem is that my users don't want to see this single quote and I don't know how to get the CSV to accept the fact that even though it looks like a number, it is actually a string.

Any thoughts would be appreciated.

Thanks

The issue is not with the CSV output, but with the way it is read by the other application (Excel, perhaps). Try opening the CSV with notepad or some other program and see if you are having the same problem.

Do a search on the forums and you will find solutions to this problem.

Brad

Pam,

JdeInNJ has identified the issue you are facing, the way numbers are formatted by the application used to view/process the CSV file. Presuming that you are using Excel, as JdeInNJ suggested, the single inverted comma at the front of the number tells Excel to treat it as text and not a number. Another option would be to completely enclose the number in double inverted commas (ie. "702055.420"). This will also cause Excel to treat it as text and not a number.

Apart from the above, other options require more effort when importing the CSV file:

Once the CSV file is imported, define the column as text. The drawback with this is that you will have to enter the column and save the value again with the trailing zero(s).

Another option is to import the file using the Excel import wizard and format the column as text. This will display the number as you wish, without any inverted commas (single or double.

If however you are happy to have Excel treat the value as a number, you can format the collumn with four decimal places to show the trailing zero(s). This is generally what we do.

Then you can get into using formulas to reformat the data in original column. Or write a macro to import and format the data as you need.

Formatting a column as text is also required when you are going the other way. If you are creating a spreadsheet to upload journal entries using import to grid, it is necessary to format the account number column as text. Otherwise Excel treats account 50.1220 as the number 50.122 and then the import fails because 50.122 is not a valid account. JDE recommends formatting the subledger and subledger type columns as text as well in a spreadsheet used for importing to the grid.

Brad