I'm working on an OCR correction list that involves all the mistakes an OCR
app would make on a date: mm/dd/yy. That is, say, 01/01/06; O1/01/06,
Q1/01/06, etc. The list has to be in a format where fields are separated by
|, so I save as CSV, open in notepad and replace. The file is always saved
with a .txt extension so I can import into Excel as text. Word processing
programs don't have ability to show so many columns, hence the need for excel
when doing find and replace on the numbers.

However, when I need to add to it, Excel of course wants to see the dates as
dates and numbers as numbers -- that is, 01/01/06 is changed to 1/01/06, and
02101106 (where the OCR reads the / as 1) drops the leading zero. I can work
around this by importing the text file and selecting "TEXT" as the column
format. However, the import function is limited to about 64 columns. Beyond
that, I can't specify import as text, and it winds up importing columns
64-200 as general format (dropping the leading zero) and messing with dates.
How can I get Excel to import AS IS?

I've never seen excel not be able to import more than 64 fields as text.

Maybe you could incorporate something like:

Dim myArray() As Variant
Dim iCtr As Long
Dim maxFields As Long

maxFields = 256 '256 columns maximum

ReDim myArray(1 To maxFields, 1 To 2)
For iCtr = 1 To maxFields
myArray(iCtr, 1) = iCtr
myArray(iCtr, 2) = 2
Next iCtr

Workbooks.OpenText Filename:="C:\somefilename.txt", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
Comma:=False, _
Space:=False, Other:=True, OtherChar:="|", FieldInfo:=myArray

sbp wrote:

I'm working on an OCR correction list that involves all the mistakes an OCR
app would make on a date: mm/dd/yy. That is, say, 01/01/06; O1/01/06,
Q1/01/06, etc. The list has to be in a format where fields are separated by
|, so I save as CSV, open in notepad and replace. The file is always saved
with a .txt extension so I can import into Excel as text. Word processing
programs don't have ability to show so many columns, hence the need for excel
when doing find and replace on the numbers.

However, when I need to add to it, Excel of course wants to see the dates as
dates and numbers as numbers -- that is, 01/01/06 is changed to 1/01/06, and
02101106 (where the OCR reads the / as 1) drops the leading zero. I can work
around this by importing the text file and selecting "TEXT" as the column
format. However, the import function is limited to about 64 columns. Beyond
that, I can't specify import as text, and it winds up importing columns
64-200 as general format (dropping the leading zero) and messing with dates.
How can I get Excel to import AS IS?

--

Dave Peterson