Excel CSV files

[R1Geezer]

Here's what I'd like to do. I have a CSV file that is generated by another program, the problem is that when I open it in excel, Excel applies it's formatting based on what it thinks the data is, eg I have text fields that can also be numbers but I want them to be text and retain leading zeroes etc but excell is determined they are numeric and strips off the leading zeroes. Is there some sort of inbedded command I can use to tell it what type each colum is for example? The result must open correctly without any need to fancy importing etc as the target audience are IT iIliterate. thanks

[scotman]

I don't think there is anything easy. You need to use the Data | Import function to open the file. You get the option at one stage to select the type of data for each column. If you set this to 'text' leading zeroes will be retained.

[scotman]

In XL 2003 that is Data | Import External Data | Import Data

[LeMarchand]

Could you not click the top of each column, and change the cell format to text/date/number/whatever. If this works, save as in Excel format and test that it re-opens OK. (Unless you mean the users get the CSV and need to open it themselves, in which case: don't know!)

[Old_Geezer]

Can you do a global edit in a text editor first ? Maybe find numbers somehow, and put an apostrophe in front of each ?

[scotman]

R1G

What you suggest is probably possible but sounds more complicated than learning how Data Import works.

[R1Geezer]

I can do all the things you suggest but this has to be idiot proof so it should just open and be correct. I am well aware of the import manipulations etc but what I really want is some way of telling excel, in the data, to leave it alone. Eg I can put double quotes around text and that works I can even imbed commas inside quotes but when I put quotes around a number it ignores them and interprets it as numeric eg I have one field like this "000123" and it becomes 123 in general format.

[ABerrant]

If the CSV is for Excel use only there is a trick which works but other apps would treat as corrupted data... place an = sign before the number in quotes thus:

data1,data2,="000001",data4,data5

[scotman]

Thanks for that tip ABerrant, I have tested that and it works OK in XL 2003.

[R1Geezer]

ABerrant, pure genius, you get life time membership of the Loyal order the Geezer club, many thanks! It's only going to excel so that's perfect!

[scotman]

R1G

Glad that ABe has fixed it for you but I am still unsure how you intend to make this feature appear in the CSV file.

[R1Geezer]

Spoc, I just generate the numbers with leading 0's and then wrap the quotes and = sign round, when the file is openned the leading 0's are still there, eg ="000123" gets put in it's cell as 000123, perfect, where as before I was getting 123 without the leading 0's and annoyingly it was left justified. The CSV file is generated from a database and then uploaded via email and sent to the users who should just be able to open it and it will look right.

ABerrant are there any other "magic" characters that do things when loaded from a CSV?

[R1Geezer]

sorry the above was for scotman.

[scotman]

R1G

Fair enough if you can arrange for the = & "" to be there automatically. I am keeping an eye on this for ABerrant to supply more tips too.

[R1Geezer]

Yes I can generate anything I like into the CSV so no problem, works a treat now!