Formatting issue with numbers when saving as csv

  • Thread starter Thread starter skinnybloke
  • Start date Start date
S

skinnybloke

Hi - I am using excel 2000. I have a spreadsheet that is an export
from ms access which contains prices of products in one column. The
number format of these cells is general.

When I save this spreadsheet as a .csv file, the prices that showed as
95.00 now show as 95 and prices that showed as 95.99 still show as
95.99 which is exactly as I want - if there are no pennies then drop
the decimal places.

I have a problem that when this .csv file is imported into a thrid
party application it is rejected.

The 3rd party application developers have taken this spreadsheet,
saved it as a .csv file and loaded it without any problems.

I have compared my .csv file against their .csv file in notepad and
found that mine displays 95.00 in the price column and theirs displays
95.

I have llooked hard and cannot see why there should be a difference.

What do I need to change to achieve the same as the 3rd party
developers?
 
Hi - I am using excel 2000. I have a spreadsheet that is an export
from ms access which contains prices of products in one column. The
number format of these cells is general.

When I save this spreadsheet as a .csv file, the prices that showed as
95.00 now show as 95 and prices that showed as 95.99 still show as
95.99 which is exactly as I want - if there are no pennies then drop
the decimal places.

I have a problem that when this .csv file is imported into a thrid
party application it is rejected.

The 3rd party application developers have taken this spreadsheet,
saved it as a .csv file and loaded it without any problems.

I have compared my .csv file against their .csv file in notepad and
found that mine displays 95.00 in the price column and theirs displays
95.

I have llooked hard and cannot see why there should be a difference.

What do I need to change to achieve the same as the 3rd party
developers?
Click to expand...

Ensure that the data in your spreadsheet (prior to export) is NUMERIC and not
TEXT. This will not be affected by the format. You can check using the
worksheet function =ISTEXT(A1).


--ron
 
Hi ron - I have tested this and the spreadsheet is TEXT. I have looked
at the prices and they are prefixed with an apostrophe.

How do I change this, or is there something that I can do in my access
SQL query to ensure that this is correct when output?
 
Hi ron - I have tested this and the spreadsheet is TEXT. I have looked
at the prices and they are prefixed with an apostrophe.

How do I change this, or is there something that I can do in my access
SQL query to ensure that this is correct when output?
Click to expand...

I would think the most efficient change would be to do something in the SQL
query. Unfortunately, I don't know anything about that.

So far as Excel is concerned, the leading apostrophe is telling Excel that the
entry is TEXT. To change it from TEXT to a NUMBER:

1. Select a Blank Cell
2. Edit/Copy
3. Select the values (hopefully a single column) to be converted to
numbers.

4. Edit/Paste Special/(Operation) ADD

Then double check to make sure the data is not TEXT. If it is still TEXT,
there may be some hidden characters that need to be removed.

If this is a frequently repetitive task, and there is no SQL solution, then the
process can be automated using a macro.

HTH,

--ron
 
Bloke,

You say that your csv written from Excel shows 95. If you're reopening it
in Excel to determine that, it might appear as 95, but may well actually
have 95.00 in the csv file (as verified by Notepad). You may already be
aware of this, but let me mention, since it's a crucial part of this
problem: NotePad is a reliable way to see the actual characters written.
Reopening csv in Excel is not. What does it look like in the sheet before
you saved the sheet as csv? That's the way Excel will write it as CSV, I
think.
 
thanks ron - I'll give that a go.

I would think the most efficient change would be to do something in the SQL
query. Unfortunately, I don't know anything about that.

So far as Excel is concerned, the leading apostrophe is telling Excel that the
entry is TEXT. To change it from TEXT to a NUMBER:

1. Select a Blank Cell
2. Edit/Copy
3. Select the values (hopefully a single column) to be converted to
numbers.

4. Edit/Paste Special/(Operation) ADD

Then double check to make sure the data is not TEXT. If it is still TEXT,
there may be some hidden characters that need to be removed.

If this is a frequently repetitive task, and there is no SQL solution, then the
process can be automated using a macro.

HTH,

--ron
Click to expand...
 
Back
Top