I frequently have to read .csv files into MS excel. These are textual
files, containing text fields, floating point numbers, integers and
times. The first item on each row tells me what kind of data is on the
row, and so when the files has been read into excel I can autofilter
on the first column to separate out whatever rows of data I want.

That's all very well, apart from the Dates which are interspersed
throughout the file (in predictable columns for a given data type, but
all over the place in practice as there are many data types) The
format of the Data is something like:

19/04/2005 15:23:04.1214567231

(yes, a uk date!) i.e. they are to a very high accuracy in time, which
I need

If I could only get these dates into excel without rounding them, I
would work with them by converting them to floating point seconds
after midnight (I need the accuracy, but don't really care what day
they are). However when excel reads in the .csv file it rounds the
date to accuracy of 1000ths of a second, which is not high enough. I
have no control over the format of dates in the input file sadly.

The unsatisfactory fixes I have come up with so far a
a) I can rename the .csv file to .txt, read it in, and then do
text-columns setting the column type (of every column) to text. This
stops excel messing up the dates, but means that all the numbers in
the file become text, so I can't do any calculations on them.
b) I can rename the .csv file to text and then read it in as a
delimited file, with delimiters set to "," and ":" - then any time
gets split up and excel doesn't intepret it as a date. But the
alignment of the data with all the column headers (explaining the
contents of each column) is lost.

Is there a better way? The only idea I have so far is writing a
preprocessor in some language like perl which goes through the file
putting a space before any item that looks like a date. A single space
on the front will stop excel interpreting it as a date on input. But
I'd really like a way of doing this simply in excel, eg by somehow
overriding the excel behaviour on reading a date in a .csv file. Can
anyone help?

There may be a method using the Excel user interface to do this- but if
you're able to write Perl script, you may be able to write a similar
VBA script within Excel to
~ read the CSV file to import it
~ during such import process, evaluate the first item on each row and
segregate the information into separate tabs
~ perform any text-to-numeric or numeric-to-text conversions as needed
~ maintain ultimate control over treatment of data (UK dates,
significant digits within a time stamp, etc.)

Are you familiar with VBA? The code for this would look something like
Sub CSVImport()
dim Lyne as string 'variable to hold entire line of data from csv
'declare other variables here
Open "c:\filename.csv" for input as #1
do while not eof(1)
line input #1, Lyne
'much data parsing he pick out data between commas, assign to
variables
'Write to relevant tab in the workbook, depending on the first
value in each row
loop
close #1
end sub

Depending on other responses here, this may be a viable option for you,
albeit somewhat painstaking up front.