Windows CSV.

[MrRatt]

right, i didnt know what section to put this in but i need some help with a CSV file...

i have got a big CSV file to edit, so i have imported it into Excell (easier to use and to edit)
the problem i have is when i open the file in one of the boxes there are a set of digets that are 15 charecters long.

eg.
350123425125512

but Microsoft Excel shows it as
3.50E+14

when i save the file after editing and then close it, the next time i reopen it the number is then shown as the following

(in microsoft Excel)
3.50E+14

but when i open the file in notepad for instance it shows as

350000000000000

how can i get it to stay as the original?
is there anyway to turn off this excel feature? where it shortens the number?


Thanx to anyone who can help

[MrRatt]

also i forgot to say, it also takes off the leading '0'
eg.
if the number is 045135 (it has got to be that number)
excel changes it to 45135, ive tried changing the whole spreadsheet to 'Text' Mode (using Format Cells)
but it wont let me save the file with that feature as it isnt supported by CSV.

[MrRatt]

Anyone?

[drizzle]

Expand the column that those numbers are in and it should display the number just fine. If you double-click on the divider line between the columns it will automatically resize it properly.

I'm not sure about the second problem though.

[MrRatt]

naa tried that still stays the same

[drizzle]

Right-Click on the cell or cells. Go to Format Cells. Change the Category to 'Custom' and then select '0' as the type. That seems to work here. Not sure if it is the best way to do it but it does work.

Or

Right-click on the cell or cells. Go to Format Cells. Change the Category to 'Number' with 'Decimal Places' set to '0', 'Negative Numbers' set to '-1234'.

[MrRatt]

but the prob is when i save the document (it has got to stay in CSV format) when it saves it will only save it as a excel document if you make changes to Cell Formats...
anyone know of an option to just turn this 'shortining' thing off?

[drizzle]

OK. Back to the drawing board.

[vass0922]

MrRatt, it WILL save as CSV but you have to go through a few "Warning you're not going to use the good Lord MS's format if you continue!" messages.

When you save as CSV it will pop up a message are you sure, make sure to click YES so it will continue to save as a csv file.


--- edit ---
btw, it will probably still have an excel icon even when its a CSV file so don't go by that.

[MrRatt]

yea that is what i done, it says you will loose any incompatable features you have used... etc..
then when i reopen the file (for example to do more editing)
all the number are 350000000000000
when ever i use some kind of formatting on the cells it looks like it corrupts the CSV

[vass0922]

what SHOULD the value be?
cut and paste it into here so I can play with it.

[MrRatt]

it should be...

350138203117803

but it is showing as

3.50138E+14

once saved and reopened it shows as

3.50138E+14 (but when i click on it to see the whole value it shows as 350138000000000)

[vass0922]

header,row,is,important
350138203117803,asfasdfas,350138203117803,kfalskdf jasdlfk

This is my output... now for some reason (probably a moronic bug in excel). I formatted the cell's as TEXT, but it wouldnt' update the display to show the actual number. So I clicked on the cell and it showed the necessary information in the value bar on the top, so I clicked in teh value bar and it showed properly in the cell...

When I saved using format as text, it worked for me?
what version of excel are you using?
are you formatting as text?

[MrRatt]

i can format it as text but when i reopen it, it will only give me the first few digets.
shall i give you the first few lines of the CSV file?
so you can mess with them?

[vass0922]

even after you expand the column?! Sounds like you may have a bug.. have you checked for updated versions?

you can do go ahead and post some of the data.

[MrRatt]

yea the same even after i expand

JPG attached

[vass0922]

what happens when you actually click in the value bar of the 'odd' cell?
Does it show the correct value in the bar?

[MrRatt]

CSV file as follows...

LineNumber,Type,ServiceCentreCode,BatchReference,JobReference,PurchaseOrderNumber,PriceCategory,SerialNumber,OldSerialNumber,RepairDate,DateIn,AuthorisationCode,EquipmentType,ProdCode,ProdSerialNo,HardwareID,SoftwarVer,ManufactDate,FaultType,Module,Circuit,PartNumber,Quantity,CustomerFaultText,FaultFoundText,WhoBooked,DateBooked
0,J,62,1050,9000,WAR,4,449308106882941,,10/09/2001,10/09/2001,N_IWR,8210,0504451,123456789,1234,12.34,200202,,,,,,360,LCD,FA,37587
,P,,,,,,,,,,,,,,,,,17,RM7L,I003,9490095,1,,,,
0,J,62,1050,9001,WAR,4,448898408713585,,18/09/2001,18/09/2001,N_IWR,3210,0503977,123456789,1234,12.34,200202,,,,,,1599,SWUG,FA,37587
,P,,,,,,,,,,,,,,,,,45,N/A,N/A,ADJUSTMENT,1,,,,
0,J,62,1050,9002,WAR,4,350111101750659,,16/09/2001,12/09/2001,N_IWR,8850,0502940,123456789,1234,12.34,200202,,,,,,360,ELASTOMER,FA,37589
,P,,,,,,,,,,,,,,,,,32,,I003,,1,,,,

hope this displays properly

--edit--

nope, but just paste all that and name it a CSV then open it in excel

[MrRatt]

Originally posted by vass0922
what happens when you actually click in the value bar of the 'odd' cell?
Does it show the correct value in the bar?

yes before i save and close it, it does, but after i reload it, it shows the value as 350000000000 etc..

[vass0922]

it worked fine for me...
1. Put it in text file (cleanup extranneous carriage returns stupid html)
2. save it as junk.csv
3. open it in excel
4. format serial number column as Text
5. Save - YES I want incompatible features saved.
6. Close excel, check csv file.. looks OK to me
7. Open Excel and check values .. serial number in 3rd row '448898408713585' straight from Excel ...

I'd suggest checking for updates for Excel it sounds like you hit a bug.