Sage 100

Normal VI export delimited to get perpetual history detail. Client had reversed some checks and when this data comes out it puts the negative sign behind the amount like 450.00- in stead of -450.00. Seems to be causing some problems calculating the column totals. Does anyone know how to fix? Actually it would do that to any field that has a negative number. Second has anyone figured out how to keep the leading zeros in the csv, like the direct deposit table on an export. I know a txt file keeps it.

One way to keep the leading zeros in the csv file is to not open it once it is created. if you need to view it make a copy and open the copy.

I try not to use csv files. I always use either tab delimited or pipe delimited or fixed field width text.

Makes sense, they are trying to verify data. Sending out to third party to implement a new PR system.

You can also rename a csv file to a txt file before opening it, then import the .txt as a csv. For example, rename abc.csv to abc.txt and import abc.txt and specifying the import file type as comma delimited. You can then open the txt file without suppressing leading zeros.

You can also change the program associated to csv file type to notepad or wordpad so that it doesn't open in Excel which strips the leading zeros.

I played around in Excel but couldn't find a way to change 450.00- to -450.00 with formatting or formulas. I think the problem is Excel doesn't see it as a number. How many negatives are in the file?

I've always opened the CSV's for editing with Notepad, using ""Open With"" command after right clicking on file name. That'll preserve the original formatting.

@JaneCavanaugh way too many, plus file for just a month is huge. Very large payroll million$ plus

In some of the exports they needed to add additional information requested by their new PR system, which is not in Sage. They may just have to put that info in another file and not tag it to the one I created for them.

Kelly, I'm assuming from your description that your using legacy export from Payroll if so, you can change the mask field when exporting numeric values to move the minus sign to the front of the number. if you move it in the mask the export will do the same (see screen shot and sample export from Payroll data entry attached)

@DavidOverholt that is exactly what I am looking for. Never paid attention to the masking. THANKS!!

If the 450- is in cell B2 then put the follow formula in another cell =LEFT(B2, LEN(B2)-1)*-1

If some of the numbers are positive you may then have to use an if statement in another column to only apply the formula to those values with the - sign after the number.