I have a Oracle database I am importing from and placing the data in Excel.
When Excel imports the data in, some of data is re-formatted. For example, I
have data that is like 04-01-01 and it converts it to 4/01/2001. Is there a
way to turn the auto formatting feature off so I can import the data as is?
I have looked everywhere and I can not find out how to shut it off.

Thanks in advance.

Unfortunately no, unless you can import the date fields as text

Regards,

Peo Sjoblom

"Chris Davidson NGSS" wrote:

I have a Oracle database I am importing from and placing the data in Excel.
When Excel imports the data in, some of data is re-formatted. For example, I
have data that is like 04-01-01 and it converts it to 4/01/2001. Is there a
way to turn the auto formatting feature off so I can import the data as is?
I have looked everywhere and I can not find out how to shut it off.

Thanks in advance.

That was the answer I thought I would get. Can I create a template to use
when I import from the database?

"Peo Sjoblom" wrote:

Unfortunately no, unless you can import the date fields as text

Regards,

Peo Sjoblom

"Chris Davidson NGSS" wrote:

I have a Oracle database I am importing from and placing the data in Excel.
When Excel imports the data in, some of data is re-formatted. For example, I
have data that is like 04-01-01 and it converts it to 4/01/2001. Is there a
way to turn the auto formatting feature off so I can import the data as is?
I have looked everywhere and I can not find out how to shut it off.

Thanks in advance.

If you can save the data as a .txt file, you could start a new workbook, then
record a macro when you open the .txt file.

Record your actions as you import each field (choosing the correct date format).

In fact, continue recording when you're adding headers/filters/page setup/etc.

Then save that workbook with the recorded code. Next time you need to import a
similar text file, just open the macro workbook and tools|macro|macros... and
run that macro.



Chris Davidson NGSS wrote:

That was the answer I thought I would get. Can I create a template to use
when I import from the database?

"Peo Sjoblom" wrote:

Unfortunately no, unless you can import the date fields as text

Regards,

Peo Sjoblom

"Chris Davidson NGSS" wrote:

I have a Oracle database I am importing from and placing the data in Excel.
When Excel imports the data in, some of data is re-formatted. For example, I
have data that is like 04-01-01 and it converts it to 4/01/2001. Is there a
way to turn the auto formatting feature off so I can import the data as is?
I have looked everywhere and I can not find out how to shut it off.

Thanks in advance.

--

Dave Peterson

OK That will not do. The data comes as an Excel Spreadsheet. So can Excel
ne made to open with a template? If I can do that then the problem is solved
and the game is over. I have tried placing the template in the C:\Program
Files\Microsoft Office\Office11\XLSart and the template is named Sheet.xlt.
When I open up Excel it opens like there is no template. What am I doing
wrong?

Thanks so far with the help.

"Dave Peterson" wrote:

If you can save the data as a .txt file, you could start a new workbook, then
record a macro when you open the .txt file.

Record your actions as you import each field (choosing the correct date format).

In fact, continue recording when you're adding headers/filters/page setup/etc.

Then save that workbook with the recorded code. Next time you need to import a
similar text file, just open the macro workbook and tools|macro|macros... and
run that macro.



Chris Davidson NGSS wrote:

That was the answer I thought I would get. Can I create a template to use
when I import from the database?

"Peo Sjoblom" wrote:

Unfortunately no, unless you can import the date fields as text

Regards,

Peo Sjoblom

"Chris Davidson NGSS" wrote:

I have a Oracle database I am importing from and placing the data in Excel.
When Excel imports the data in, some of data is re-formatted. For example, I
have data that is like 04-01-01 and it converts it to 4/01/2001. Is there a
way to turn the auto formatting feature off so I can import the data as is?
I have looked everywhere and I can not find out how to shut it off.

Thanks in advance.

--

Dave Peterson

If you mean you get the data as a workbook, then the dates have been converted
already and it's too late.

I'm not sure how a template would work to resolve this.

Chris Davidson NGSS wrote:

OK That will not do. The data comes as an Excel Spreadsheet. So can Excel
ne made to open with a template? If I can do that then the problem is solved
and the game is over. I have tried placing the template in the C:\Program
Files\Microsoft Office\Office11\XLSart and the template is named Sheet.xlt.
When I open up Excel it opens like there is no template. What am I doing
wrong?

Thanks so far with the help.

"Dave Peterson" wrote:

If you can save the data as a .txt file, you could start a new workbook, then
record a macro when you open the .txt file.

Record your actions as you import each field (choosing the correct date format).

In fact, continue recording when you're adding headers/filters/page setup/etc.

Then save that workbook with the recorded code. Next time you need to import a
similar text file, just open the macro workbook and tools|macro|macros... and
run that macro.



Chris Davidson NGSS wrote:

That was the answer I thought I would get. Can I create a template to use
when I import from the database?

"Peo Sjoblom" wrote:

Unfortunately no, unless you can import the date fields as text

Regards,

Peo Sjoblom

"Chris Davidson NGSS" wrote:

I have a Oracle database I am importing from and placing the data in Excel.
When Excel imports the data in, some of data is re-formatted. For example, I
have data that is like 04-01-01 and it converts it to 4/01/2001. Is there a
way to turn the auto formatting feature off so I can import the data as is?
I have looked everywhere and I can not find out how to shut it off.

Thanks in advance.

--

Dave Peterson


--

Dave Peterson

I got the template to work. The problem is now when the data gets imported
it does not use the template. There has got to be a solution to this
problem. Is there a way to take the date and convert it back to the original
format? I wonder why Microsoft has Excel developed to where you can not turn
this feature off.

Thanks for your help so far.

"Dave Peterson" wrote:

If you mean you get the data as a workbook, then the dates have been converted
already and it's too late.

I'm not sure how a template would work to resolve this.

Chris Davidson NGSS wrote:

OK That will not do. The data comes as an Excel Spreadsheet. So can Excel
ne made to open with a template? If I can do that then the problem is solved
and the game is over. I have tried placing the template in the C:\Program
Files\Microsoft Office\Office11\XLSart and the template is named Sheet.xlt.
When I open up Excel it opens like there is no template. What am I doing
wrong?

Thanks so far with the help.

"Dave Peterson" wrote:

If you can save the data as a .txt file, you could start a new workbook, then
record a macro when you open the .txt file.

Record your actions as you import each field (choosing the correct date format).

In fact, continue recording when you're adding headers/filters/page setup/etc.

Then save that workbook with the recorded code. Next time you need to import a
similar text file, just open the macro workbook and tools|macro|macros... and
run that macro.



Chris Davidson NGSS wrote:

That was the answer I thought I would get. Can I create a template to use
when I import from the database?

"Peo Sjoblom" wrote:

Unfortunately no, unless you can import the date fields as text

Regards,

Peo Sjoblom

"Chris Davidson NGSS" wrote:

I have a Oracle database I am importing from and placing the data in Excel.
When Excel imports the data in, some of data is re-formatted. For example, I
have data that is like 04-01-01 and it converts it to 4/01/2001. Is there a
way to turn the auto formatting feature off so I can import the data as is?
I have looked everywhere and I can not find out how to shut it off.

Thanks in advance.

--

Dave Peterson


--

Dave Peterson

I still don't understand how the template works if the imported data doesn't use
the template...

But if the cell contains a valid date, maybe you could use a formula to try to
convert it:

=DATE(YEAR(A1),DAY(A1),MONTH(A1))

But I don't know what:
04-01-01
is
2005-Jan-01
04-jan-2001
March 01, 2001
or what???

You might be able to use another formula to parse your data that was brought in
and kept as text (since it didn't look like a real date to excel).

You could use a combination of mid()'s and right()'s, left()'s to extract the
pieces and plop them together in the correct order using the =date() function.

But I'd check those dates very closely to make sure that they are what they
should be.


Chris Davidson NGSS wrote:

I got the template to work. The problem is now when the data gets imported
it does not use the template. There has got to be a solution to this
problem. Is there a way to take the date and convert it back to the original
format? I wonder why Microsoft has Excel developed to where you can not turn
this feature off.

Thanks for your help so far.

"Dave Peterson" wrote:

If you mean you get the data as a workbook, then the dates have been converted
already and it's too late.

I'm not sure how a template would work to resolve this.

Chris Davidson NGSS wrote:

OK That will not do. The data comes as an Excel Spreadsheet. So can Excel
ne made to open with a template? If I can do that then the problem is solved
and the game is over. I have tried placing the template in the C:\Program
Files\Microsoft Office\Office11\XLSart and the template is named Sheet.xlt.
When I open up Excel it opens like there is no template. What am I doing
wrong?

Thanks so far with the help.

"Dave Peterson" wrote:

If you can save the data as a .txt file, you could start a new workbook, then
record a macro when you open the .txt file.

Record your actions as you import each field (choosing the correct date format).

In fact, continue recording when you're adding headers/filters/page setup/etc.

Then save that workbook with the recorded code. Next time you need to import a
similar text file, just open the macro workbook and tools|macro|macros... and
run that macro.



Chris Davidson NGSS wrote:

That was the answer I thought I would get. Can I create a template to use
when I import from the database?

"Peo Sjoblom" wrote:

Unfortunately no, unless you can import the date fields as text

Regards,

Peo Sjoblom

"Chris Davidson NGSS" wrote:

I have a Oracle database I am importing from and placing the data in Excel.
When Excel imports the data in, some of data is re-formatted. For example, I
have data that is like 04-01-01 and it converts it to 4/01/2001. Is there a
way to turn the auto formatting feature off so I can import the data as is?
I have looked everywhere and I can not find out how to shut it off.

Thanks in advance.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

What I have done (and it really is not a good workaround but I suppose it
will have to do) is format the cell to Spanish(Puerto Rico) so I can have the
date in xx-xx-xx format. Then change the data to text. I really wish there
was a way to shut the auto format off. I hope that Microsoft will consider
adding that feature in a future version of Excel. Thanks for the
assistance. I will just have to live with the lack of this feature.

Chris Davidson
NGSS

"Dave Peterson" wrote:

I still don't understand how the template works if the imported data doesn't use
the template...

But if the cell contains a valid date, maybe you could use a formula to try to
convert it:

=DATE(YEAR(A1),DAY(A1),MONTH(A1))

But I don't know what:
04-01-01
is
2005-Jan-01
04-jan-2001
March 01, 2001
or what???

You might be able to use another formula to parse your data that was brought in
and kept as text (since it didn't look like a real date to excel).

You could use a combination of mid()'s and right()'s, left()'s to extract the
pieces and plop them together in the correct order using the =date() function.

But I'd check those dates very closely to make sure that they are what they
should be.


Chris Davidson NGSS wrote:

I got the template to work. The problem is now when the data gets imported
it does not use the template. There has got to be a solution to this
problem. Is there a way to take the date and convert it back to the original
format? I wonder why Microsoft has Excel developed to where you can not turn
this feature off.

Thanks for your help so far.

"Dave Peterson" wrote:

If you mean you get the data as a workbook, then the dates have been converted
already and it's too late.

I'm not sure how a template would work to resolve this.

Chris Davidson NGSS wrote:

OK That will not do. The data comes as an Excel Spreadsheet. So can Excel
ne made to open with a template? If I can do that then the problem is solved
and the game is over. I have tried placing the template in the C:\Program
Files\Microsoft Office\Office11\XLSart and the template is named Sheet.xlt.
When I open up Excel it opens like there is no template. What am I doing
wrong?

Thanks so far with the help.

"Dave Peterson" wrote:

If you can save the data as a .txt file, you could start a new workbook, then
record a macro when you open the .txt file.

Record your actions as you import each field (choosing the correct date format).

In fact, continue recording when you're adding headers/filters/page setup/etc.

Then save that workbook with the recorded code. Next time you need to import a
similar text file, just open the macro workbook and tools|macro|macros... and
run that macro.



Chris Davidson NGSS wrote:

That was the answer I thought I would get. Can I create a template to use
when I import from the database?

"Peo Sjoblom" wrote:

Unfortunately no, unless you can import the date fields as text

Regards,

Peo Sjoblom

"Chris Davidson NGSS" wrote:

I have a Oracle database I am importing from and placing the data in Excel.
When Excel imports the data in, some of data is re-formatted. For example, I
have data that is like 04-01-01 and it converts it to 4/01/2001. Is there a
way to turn the auto formatting feature off so I can import the data as is?
I have looked everywhere and I can not find out how to shut it off.

Thanks in advance.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson