Unwanted formating (excel 2000)

[Marius Horak]

We have to import a file (CSV) where data in a column is in "999:99" or
"99:99" format.
For unknown to me reason the built in logic treats that data as Time
and Excel converts some data in this column into wrong format.


For example

23:25 will stays 23:25
25:00 will be converted into 25:00:00
120:00 will be converted into 120:00:00

When I format column as text

23:25 will be converted into 0.975695
25:00 will be converted into 1.041667
120:00 will be converted into 5

How can I disable this madness?

Thanks

MH

[Dave Peterson]

If you rename your .csv file to .txt, then when you open the file, you'll see
the data import wizard.

You'll be able to specify each field the way you want--including using Text for
this field.

Marius Horak wrote:
>
> We have to import a file (CSV) where data in a column is in "999:99" or
> "99:99" format.
> For unknown to me reason the built in logic treats that data as Time
> and Excel converts some data in this column into wrong format.
>
> For example
>
> 23:25 will stays 23:25
> 25:00 will be converted into 25:00:00
> 120:00 will be converted into 120:00:00
>
> When I format column as text
>
> 23:25 will be converted into 0.975695
> 25:00 will be converted into 1.041667
> 120:00 will be converted into 5
>
> How can I disable this madness?
>
> Thanks
>
> MH

--

Dave Peterson

[Marius Horak]

Dave Peterson wrote:

> If you rename your .csv file to .txt, then when you open the file,
> you'll see the data import wizard.
>
> You'll be able to specify each field the way you want--including
> using Text for this field.

Thank you Dave.
Now please teach about 50-60 poeple how to import txt files into Excel.
Plus, the first three rows of the file have different number of columns
than the rest of the data.

I can find a workaround of this problem by using a semicolon instead of
colon but it upsets the users. They want a colon.

In XX c computers were desiged and created to help people.
In XXI c computers will dictate people how to run the business and life
(with a little help from M$).

MH

[Dave Peterson]

If the file is always laid out the same way, you could record a macro when you
do it once.

Add in everything you need to do.

Then distribute the workbook with the macro to each of the users.

Running a macro that adds formatting, headers, filters, print setup may be even
better than keeping the file as .csv.

Marius Horak wrote:
>
> Dave Peterson wrote:
>
> > If you rename your .csv file to .txt, then when you open the file,
> > you'll see the data import wizard.
> >
> > You'll be able to specify each field the way you want--including
> > using Text for this field.
>
> Thank you Dave.
> Now please teach about 50-60 poeple how to import txt files into Excel.
> Plus, the first three rows of the file have different number of columns
> than the rest of the data.
>
> I can find a workaround of this problem by using a semicolon instead of
> colon but it upsets the users. They want a colon.
>
> In XX c computers were desiged and created to help people.
> In XXI c computers will dictate people how to run the business and life
> (with a little help from M$).
>
> MH

--

Dave Peterson

[Marius Horak]

Thank you Dave.

Your solution won't work as...
The CSV file is being genereted from a reporting tool and depends on
criteria selected by user(s).
The reporting tool can export data in CSV format only.
There could be any number of "999:99" columns and they can be at any
position.
In case of any change to the report we will have to change the macro on
every PC.

There reporting tool is a bit limited but costs next to nothing, is
very easy to use and so far there were no requests/reports that it
could not handle. It replaced Crystal Reports that was declared as too
difficult to use by users and bosses.

MH

[Dave Peterson]

If there's that much variation in the input, maybe you could read each line of
the text file and do your own parsing.

Chip Pearson has some code that can show you how to open a text file and read
that text file:
http://www.cpearson.com/excel/imptext.htm

You could look at each field and try to determine if it should be treated as a
number, as text, as a date, as time....



Marius Horak wrote:
>
> Thank you Dave.
>
> Your solution won't work as...
> The CSV file is being genereted from a reporting tool and depends on
> criteria selected by user(s).
> The reporting tool can export data in CSV format only.
> There could be any number of "999:99" columns and they can be at any
> position.
> In case of any change to the report we will have to change the macro on
> every PC.
>
> There reporting tool is a bit limited but costs next to nothing, is
> very easy to use and so far there were no requests/reports that it
> could not handle. It replaced Crystal Reports that was declared as too
> difficult to use by users and bosses.
>
> MH

--

Dave Peterson