Excel converts text value to date - urgent

[vkarthik]

I'm opening a csv file with excel using vbscript, and reading the values in the cells. There is one phone number field in the csv, and it has some values like 1-2321 etc. Excel interprets this column as date and replaces it with Jan-2321. When this is read as text, I get a value 1/1/2321.

Is there anyway to stop excel from acting smart?



LOL A ship is safe in the harbour, but that's not what it is meant for!!! LOL

 

[mrmovie]

this a vbscript forum therefore cant answer on excel questions...in short, dont use excel, open the csv using some other method, TextStream or ADO recordset

 

[vkarthik]

OK. After some research, I've figured out how to do it. Though this isn't excel forum, I did the work using vbscript, so those of u who might be interested, read ahead:

The trick is, don't do the following -

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open("data.csv")

Doing this will make excel assume a lot of things about the file. Instead, import the file using OpenText method. Here, you get a full control on what you want excel to do with the file (import wizard). There is one parameter in OpenText that lets you specify that the fields imported are plain text fields and not dates. Now the catch is, even if you do that, excel will convert text to date. Problem is, excel takes too much liberty with .csv extension. So just rename it to something like .txt, .myname or whatever. Now, OpenText will work magic.

A full description of OpenText method can be found here -

http://msdn2.microsoft.com/en-us/library/microsoft.office.interop.excel.workbooks.opentext.aspx

 

[mrmovie]

open the file as a TextStream then....

 

[vkarthik]

The pain in opening the file normally is, you need to parse it yourself. For example, if you have an entry like

"last name, first name","my fone no",,,,"abc@efg.com",,,,

Its hard to split this string into various fields because you cannot trust any delimiter here. You need to use some special logic to find the commas that appear within the values (like last name, first name) and replace them with something else and then split it based on comma. I tried that method too but it was rather painful. Easier thing would be to make excel open it, and read the values from the cells. Its slower, but works fine.

 

[mrmovie]

yeah, sorry i was splitting hairs ;-) by changing the .csv to a .txt in some respects you were asking excel to open it as a text stream,, i was teasing