Stop Excel from changing numbers to dates in CSV file

When opening a .CSV created from a third party software, one of the columns
is converted to date format. The numbers are imported in #/# format. and
Excel changes most of the numbers to 2-Jan, 3-Jan etc... This would not be
that big of an issue if I could simply set it back. Clearing format or
changing to text converts it to a 5 digit number.

The only way I have found to get it to work properly thus far is to open a
blank sheet and import data and setting the column to text prior to import.

I would like to find a way of opening the CSV file without all the extra
steps. Is there a setting that can be turned off?

Thanks in advance,

You can change the file extension to .txt and then open in Excel, which will
trigger the import wizard, and you will then have the option of specifying
that that particular column be imported as text.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Curt L." <[email protected]> wrote in message
news:[email protected]...
> When opening a .CSV created from a third party software, one of the
columns
> is converted to date format. The numbers are imported in #/# format. and
> Excel changes most of the numbers to 2-Jan, 3-Jan etc... This would not be
> that big of an issue if I could simply set it back. Clearing format or
> changing to text converts it to a 5 digit number.
>
> The only way I have found to get it to work properly thus far is to open a
> blank sheet and import data and setting the column to text prior to
import.
>
> I would like to find a way of opening the CSV file without all the extra
> steps. Is there a setting that can be turned off?
>
> Thanks in advance,

"Ken Wright" wrote:

> You can change the file extension to .txt and then open in Excel, which will
> trigger the import wizard, and you will then have the option of specifying
> that that particular column be imported as text.
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ----------------------------------------------------------------------------
> It's easier to beg forgiveness than ask permission :-)
> ----------------------------------------------------------------------------
>
> "Curt L." <[email protected]> wrote in message
> news:[email protected]...
> > When opening a .CSV created from a third party software, one of the
> columns
> > is converted to date format. The numbers are imported in #/# format. and
> > Excel changes most of the numbers to 2-Jan, 3-Jan etc... This would not be
> > that big of an issue if I could simply set it back. Clearing format or
> > changing to text converts it to a 5 digit number.
> >
> > The only way I have found to get it to work properly thus far is to open a
> > blank sheet and import data and setting the column to text prior to
> import.
> >
> > I would like to find a way of opening the CSV file without all the extra
> > steps. Is there a setting that can be turned off?
> >
> > Thanks in advance,
>
>
>

I have a similar problem: I have (many different types of) logging devices which provide the data as .csv files. Here is an extract from one of them (causing me trouble)
Sample interval= 60sec;CO2 ppm;CO ppm;RH %;T øC;Patm;
2433;2013/10/29 10:40:20; 977; 0; 46.5; 23.1; 959
2433;2013/10/29 10:41:20; 996; 0; 46.7; 23.2; 959
2433;2013/10/29 10:42:20; 996; 0; 46.6; 23.2; 959
2433;2013/10/29 10:43:20; 1001; 0; 46.4; 23.2; 959
2433;2013/10/29 10:44:20; 1014; 0; 46.5; 23.3; 959
2433;2013/10/29 10:45:20; 1029; 0; 46.7; 23.3; 959
2433;2013/10/29 10:46:20; 1042; 0; 46.6; 23.3; 959

I open this .csv file in Excel (2010) using VBA; it opens fine with the date+time shown ok in one column, followed by the numerical values. However when I select in VBA the columns 2-6 which I want use rng.PasteSpecial Paste:=xlPasteValues, the CO2 level 1001 on row 5 is transformed into a date 10 Jan; through the file this occurs a large number of times.

Interestingly, when I copy and paste special from the keyboard exactly the same data (from the same file that I still have open after my VBA script), these numbers are pasted correctly; thus the problem occurs only when I do the rng1.copy, rng2.paste Paste:=xlPasteValues.

Does anybody any explanation? How can I turn of the automatic date recognition and correction?

Thank you for any help in advance

Otto, Kuopio, Finland