On Sat, 7 May 2005 02:26:01 -0700, "Magius00"
wrote:
You see that it does assume this is in US-en format. I'm currently trying to
work on getting the csv and saving it as an xml importing the data and then
deleting it. Other than this i could either do as you sujest and copy all the
csv's rename them to txt files, split the text and then import it but even
easyer would be if i could get it to import the data as text with no
formatting. I'm sure there was a way for me to do this in excel 97 but i
can't seam to get it to work in 2003.
Hope this clears things up a bit and if i get some free time next week i'll
knock up an example csv file
Well, the problem is not really Excel but rather VBA and how it handles some of
the data. If your application is exporting the data in English(UK) format, and
your regional settings are English(UK), it should be possible to import the
data with messing up the dates. But your VBA code may be responsible for the
issues you are having.
Reading the article makes it even more clear that in order to come up with an
optimum solution, it is necessary to see exactly what your .csv file contains;
and how you want the data to appear in your Excel workbook. It is likely that
some minor changes in your importing macro may be all that is required.
In one scenario, both changing the suffix (or copying and changing the suffix)
to .txt and then importing using the Data/Text to Columns wizard to properly
parse the date data can be easily automated using VBA.
But, for example, my file Book1a.csv contains the following text strings (dates
in UK format):
6/5/2005,
7/5/2005,
8/5/2005,
9/5/2005,
10/5/2005,
11/5/2005,
12/5/2005,
13/05/2005,
14/05/2005,
15/05/2005,
16/05/2005,
17/05/2005,
18/05/2005,
19/05/2005,
20/05/2005,
21/05/2005,
If I just open it in Excel, with my US settings, I get the following:
6/5/2005
7/5/2005
8/5/2005
9/5/2005
10/5/2005
11/5/2005
12/5/2005
13/05/2005
14/05/2005
15/05/2005
16/05/2005
17/05/2005
18/05/2005
19/05/2005
20/05/2005
21/05/2005
where 6/5/2005 through 12/5/2005 are US style dates, and the remainder are text
strings.
However, if I use the following macro:
============================
Sub foo()
Workbooks.Open Filename:= _
"C:\Book1a.csv"
Range("A1:A16").TextToColumns Destination:=Range("A1"), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
FieldInfo:=Array(1, 4)
End Sub
===========================
then the dates get converted from UK to US style and are all true dates:
5/6/2005
5/7/2005
5/8/2005
5/9/2005
5/10/2005
5/11/2005
5/12/2005
5/13/2005
5/14/2005
5/15/2005
5/16/2005
5/17/2005
5/18/2005
5/19/2005
5/20/2005
5/21/2005
I suspect something similar can be done with your data. But the details depend
on the details of exactly what you have, and what you are trying to accomplish.
--ron
|