Saving a .csv file when cell is formatted?

[UKSBD]

I am trying to save a .csv file where one of the cells starts with a zero.
I know I can format the cell as text so the zero stays there, but when I
save it changes the format and removes the zero.
any ideas?

just to add:
using Excell 2007 (saves as .xlsx OK)

[ozbon]

I'm going to ask the stupid question - does it have to have that first zero?

If so, make the column a text column, so it'll be saved within quotes, rather than as a number.

[FireFleur]

The zero could be seen as a null value, and then transformed on the spreadsheet display. Could try 0.0 or actually typing 0.

[ozbon]

I suspect, FireFleur, that it's not a cell with just a zero in, but something like a product code or whatever that can start with a 0 - or even a phone number, where it starts with a 0. They're the ones that are always a pain in the arse when it comes to spreadsheets/CSV files etc.

[DataWise]

I'm no Excel expert, but are there any formulas which need that cell? If so, then the program may be overriding your instruction to format the cell as text, and saving it as a number. Just a guess.

Paul Reed

[DataWise]

In StarOffice (on Windows), I can format a cell as a number, and also specify the number of 'Leading zeroes' for that cell. So, for example, if I format A1 as 'Number' with the 'Leading zeroes' option set to 3, and then enter '2' into A1, I get '002'. Then if I use A1 in a formula, it behaves as a number to give the correct numeric answer - as you would expect.

When I save the file and close StarOffice, then open it again, A1 retains the leading zeroes. That is, A1 = 002. This is also the same for OpenOffice on Linux.

Are you sure Excel doesn't do this?

Paul Reed

[UKSBD]

it has to have a zero in front of it as it is 01
Excel does it fine when saving as a .xslx file, it's only when saving as
.csv that it removes the zero.
Strangely though, if opening the .CSV file in Notepad the Zero is there.

[FireFleur]

Yes quite right Ozbon, I just reread it :)

There are two ways to do it internally, one is as the OP says convert to a string, or if there is a datatype which can specify length for zero padding as well as the number that could be used.

Anyway, looks like he got it sorted.

[UKSBD]

Anyway, looks like he got it sorted.


I don't know if I have or not :|

I open it with excel and it sjust says 1
I open it with notepad and it says 01

I don't know if it is the right format for the people who want it (HMRC) :(

[FireFleur]

Well somewhere it is storing that it is meant to have a leading zero or is padded zero to a certain length.

Try copying the CSV file to another name and then import into Excel and see what you get, then export it again, and check if the zero remains.

[UKSBD]

Already tried that, it just strips the zero every time (still there if opening with notepad though)

[UKSBD]

Would you mind looking to see what you see?

http://www.uksmallbusinessdirectory.co.uk/test.csv

2B should be 000
2D should be 01

they are displaying as 0
and 1 when opening in Excel for me.
but as 000
and 01
if I open in notepad

[FireFleur]

So the zero is not shown in Excel, but even so when exporting CSV it is there?

If so then, it depends on the display setting in Excel, it certainly looks like it knows that there is a leading zero (unless the export function to CSV is adding it).

Yes, it shows that as you say in the CSV, if you have to send the CSV then it is there (text is just that really ASCII text is pretty universal).

[johndon68]

Excel will strip the leading zero (even though it appears when you open the file in Notepad), as far as I know, there is nothing you can do to stop it.

As long as you can see the zero when the file is opened in Notepad then the file is OK...

John

[UKSBD]

As long as you can see the zero when the file is opened in Notepad then the file is OK...

John

Will HMRC accept it as a EC sales list how it is?
(zero doesn't appear to be there when opening in Excel)

[johndon68]

I don't see why they shouldn't - the zero is in the actual CSV file, the problem is with Excel not the file itself.

I generally try to avoid Excel at all costs when dealing with CSV files :)

John

[JohnGrove]

Excel tries to be clever when you load a csv file and converts what it thinks should be numbers to numbers. You can get around this by importing rather than loading the file.

Open a blank sheet and go to Data>Import External Data>Import Data. Select the file and you will have the option to set the column format. Select text for your the column containing the leading zeros and continue with the import. The numbers will then be imported intact.

Open Office gives you this option whenever you open a csv file.

HTH

John

[FireFleur]

Here is some more info that may help:

http://www.mrexcel.com/archive/General/14898.html

[UKSBD]

Excel tries to be clever when you load a csv file and converts what it thinks should be numbers to numbers. You can get around this by importing rather than loading the file.

Open a blank sheet and go to Data>Import External Data>Import Data. Select the file and you will have the option to set the column format. Select text for your the column containing the leading zeros and continue with the import. The numbers will then be imported intact.

Open Office gives you this option whenever you open a csv file.

HTH

John

That works fine until you save
Once you save, it strips it again :(

Hopefully it is fine as I have it, and it is just displaying wrong, rather than
being wrong

[UKSBD]

Here is some more info that may help:

http://www.mrexcel.com/archive/General/14898.html

see the date of the post ?
Think things might be a bit different since then :)

[FireFleur]

Yes, it is a few years old, but generally things don't change too much, especially macros.

But who knows, I think you are getting help from people who use open office, you might want to try that avenue.

Or put in leading zeroes or padding with zeroes (perhaps zero padding) into Excel help, those are generally the terms for the problem you are having.

[UKSBD]

Hopefully one of the accountants will read this and confirm (or not) that
the file will be acceptable.

[JohnGrove]

That works fine until you save
Once you save, it strips it again :(

Hopefully it is fine as I have it, and it is just displaying wrong, rather than
being wrong

What I'm trying to say is that the csv file is fine, it's just Excel converting it on loading.

[UKSBD]

What I'm trying to say is that the csv file is fine, it's just Excel converting it on loading.

Thanks, but what happens when the person opens it in Excel?
They won't see the zero, or doesn't that matter?

[FireFleur]

It depends on the display settings in the Excel instance.

If someone has it set to show leading zeroes then they should display, but if they have it set to not show leading zeroes then it won't display.

It sounds like you have it set to not display leading zeroes, but the zeros is there though, just invisible :)

[JohnGrove]

Thanks, but what happens when the person opens it in Excel?
They won't see the zero, or doesn't that matter?

Very little you can do about that other than prefixing the number with a letter and forcing it as text but my guess is that if it's required in csv format, it wont be going into Excel.

[KM-Tiger]

Will HMRC accept it as a EC sales list how it is?
(zero doesn't appear to be there when opening in Excel)

The information you require is on page 20 of this document:

http://www.hmrc.gov.uk/VAT/ec-sales-lists.pdf

and includes detailed instructions on how to deal with leading zeroes.

Funnily enough, it's top of page one if you Google "hmrc ec sales list file format".

Yes they are aware that some spreadsheets strip leading zeroes.

[UKSBD]

Thanks, yes that is the document that got me asking in the first place.
I did all it said, saved the file, but then when I oppened it later the zero
had gone.

I now realise it hadn't actually gone, just wasn't displayed

[ozbon]

I think HMRC prefer it that if a field is a 0 then they prefer you to say "ZERO" rather than "0". That's the case in forms, anyway.

If it's really causing you concerns though, make that one cell into a text field that just says 0 . That way, job done.

[UKSBD]

I think HMRC prefer it that if a field is a 0 then they prefer you to say "ZERO" rather than "0". That's the case in forms, anyway.

If it's really causing you concerns though, make that one cell into a text field that just says 0 . That way, job done.

you need to read the whole thread before posting :)

[ozbon]

Yeah, I had done - but as you said, it was the information from HMRC that got you concerned in the first place.

But so long as it's sorted now, that's what matters.

[UKSBD]

Yes, crazy thing is I only have 1 transaction a month :redface: