Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
leinad512
 
Posts: n/a
Default Stop Excel Rounding Dates

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?
  #2   Report Post  
Dave O
 
Posts: n/a
Default

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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re-formatting of dates from Excel to CSV GLS Excel Discussion (Misc queries) 2 February 7th 05 01:42 PM
Changing Dates in Excel Jackie Excel Worksheet Functions 3 January 14th 05 09:16 PM
Excel opens my file and a book1.xls, make it stop? Bob Excel Discussion (Misc queries) 6 January 7th 05 01:20 AM
How do I stop the autopaste function in Excel? Jules Excel Worksheet Functions 1 November 1st 04 10:54 PM
How do I stop automatic page breaks in excel Lesley Hutchinson Excel Worksheet Functions 0 October 31st 04 01:49 PM


All times are GMT +1. The time now is 10:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"