Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text file import using ADO - XL converts fields to dates
I am using Office 2003 on Windows XP with ADO 2.8.
I am using ADO to import the contents of a text file into several Excel worksheets. The program works great, BUT one field is a number separated by a forward slash followed by another number. Examples: 12/0, 11/2, 24/5, 15/60. For most rows (there around 128,000+), this field is imported correctly. For several others, Excel converts them to dates and once converted, the original values are lost. The raw text file is comma delimited and all fields are surrounded by double quotes. This behaviour occurs even when I import the first 65,536 rows using the built-in parser when opening the text file manually. BEFORE import I can see that the fields are correctly displayed, AFTER import they are changed - even if I set all cells in the destination sheet to text type fields. How can I fix this problem? I really prefer to use ADO if at all possible since I have a whole program already dedicated to this project. Thanks much in advance for your input. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text file import using ADO - XL converts fields to dates
The .Value property of an ADO Field is a variant data type. If you are not
doing so, I would suggest first that you explicity reference the .Value, for example: Range("A1").Value = MyRs.Fields(2).Value instead of Range("A1").Value = MyRs.Fields(2) Then, to help it be recognized as text and not as a date, it might help to assign it to a string variable: Dim StrVar as String StrVar = MyRs.Fields(2).Value Range("A1").Value = StrVar I haven't tested this so can't say if it will solve the problem, but hope it might help. -- - K Dales "quartz" wrote: I am using Office 2003 on Windows XP with ADO 2.8. I am using ADO to import the contents of a text file into several Excel worksheets. The program works great, BUT one field is a number separated by a forward slash followed by another number. Examples: 12/0, 11/2, 24/5, 15/60. For most rows (there around 128,000+), this field is imported correctly. For several others, Excel converts them to dates and once converted, the original values are lost. The raw text file is comma delimited and all fields are surrounded by double quotes. This behaviour occurs even when I import the first 65,536 rows using the built-in parser when opening the text file manually. BEFORE import I can see that the fields are correctly displayed, AFTER import they are changed - even if I set all cells in the destination sheet to text type fields. How can I fix this problem? I really prefer to use ADO if at all possible since I have a whole program already dedicated to this project. Thanks much in advance for your input. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
opening text file converts numbers to scientific notation | Excel Discussion (Misc queries) | |||
Import a txt file with text fields that have meaningful leading sp | Excel Discussion (Misc queries) | |||
I'm trying to import fields from one Excel file to another | Excel Worksheet Functions | |||
converts numbers to dates | Excel Discussion (Misc queries) | |||
Import Pipe Delimited File, Parse out certian Fields, create new f | New Users to Excel |