Hi There,

I have a procedure that imports a .csv file.
All was going well until some of the cell values being imported were 17DEC,
28DEC and that made Excel think they are dates and puts into the cell 17-Dec
& 28-Dec, which is wrong, these are actually stock codes.
How do I make sure that when I am processing the data that it stays exactly
as it is in the .Csv file.
Do I have to format the column to be Text before hand? When I tried that it
changed 17DEC into something like 38334

Any help or advice would be appreciated.

Best Regards,

Steve Wilson.

The OpenText method allows you to specify the data type of each field. You
would have to specify that this field is text.

To see how this is done first temporarily change the file's extension to TXT
(just so long as it is not CSV). Then record a macro as you open the file
in Excel. The File Import Wizard will appear and you use the third screen
of the wizard to change the field type of the "date" field to Text from
General.

The resulting macro will demonstrate using the FieldInfo parameter to
control data type.



--
Jim Rech
Excel MVP
"Always Learning" <NoMoreSpam@MyEmail wrote in message
...
| Hi There,
|
| I have a procedure that imports a .csv file.
| All was going well until some of the cell values being imported were
17DEC,
| 28DEC and that made Excel think they are dates and puts into the cell
17-Dec
| & 28-Dec, which is wrong, these are actually stock codes.
| How do I make sure that when I am processing the data that it stays
exactly
| as it is in the .Csv file.
| Do I have to format the column to be Text before hand? When I tried that
it
| changed 17DEC into something like 38334
|
| Any help or advice would be appreciated.
|
| Best Regards,
|
| Steve Wilson.
|
|

Hi Jim,

I tried your suggestion but 17-Dec gets converted to 38338
Do you have any other ideas.

Thanks,

Steve Wilson.

"Jim Rech" wrote in message
...
The OpenText method allows you to specify the data type of each field.
You
would have to specify that this field is text.

To see how this is done first temporarily change the file's extension to
TXT
(just so long as it is not CSV). Then record a macro as you open the file
in Excel. The File Import Wizard will appear and you use the third screen
of the wizard to change the field type of the "date" field to Text from
General.

The resulting macro will demonstrate using the FieldInfo parameter to
control data type.



--
Jim Rech
Excel MVP
"Always Learning" <NoMoreSpam@MyEmail wrote in message
...
| Hi There,
|
| I have a procedure that imports a .csv file.
| All was going well until some of the cell values being imported were
17DEC,
| 28DEC and that made Excel think they are dates and puts into the cell
17-Dec
| & 28-Dec, which is wrong, these are actually stock codes.
| How do I make sure that when I am processing the data that it stays
exactly
| as it is in the .Csv file.
| Do I have to format the column to be Text before hand? When I tried that
it
| changed 17DEC into something like 38334
|
| Any help or advice would be appreciated.
|
| Best Regards,
|
| Steve Wilson.
|
|

I tried your suggestion but 17-Dec gets converted to 38338

Are you sure you did this right? It works fine for me.

This is what my text file had in it:

123,abc,17-Dec

and the last item came into Excel as text: 17-Dec

--
Jim Rech
Excel MVP
"Always Learning" <NoMoreSpam@MyEmail wrote in message
...
| Hi Jim,
|
| I tried your suggestion but 17-Dec gets converted to 38338
| Do you have any other ideas.
|
| Thanks,
|
| Steve Wilson.
|
| "Jim Rech" wrote in message
| ...
| The OpenText method allows you to specify the data type of each field.
| You
| would have to specify that this field is text.
|
| To see how this is done first temporarily change the file's extension to
| TXT
| (just so long as it is not CSV). Then record a macro as you open the
file
| in Excel. The File Import Wizard will appear and you use the third
screen
| of the wizard to change the field type of the "date" field to Text from
| General.
|
| The resulting macro will demonstrate using the FieldInfo parameter to
| control data type.
|
|
|
| --
| Jim Rech
| Excel MVP
| "Always Learning" <NoMoreSpam@MyEmail wrote in message
| ...
| | Hi There,
| |
| | I have a procedure that imports a .csv file.
| | All was going well until some of the cell values being imported were
| 17DEC,
| | 28DEC and that made Excel think they are dates and puts into the cell
| 17-Dec
| | & 28-Dec, which is wrong, these are actually stock codes.
| | How do I make sure that when I am processing the data that it stays
| exactly
| | as it is in the .Csv file.
| | Do I have to format the column to be Text before hand? When I tried
that
| it
| | changed 17DEC into something like 38334
| |
| | Any help or advice would be appreciated.
| |
| | Best Regards,
| |
| | Steve Wilson.
| |
| |
|
|
|
|

Sorry Jim,

It is 17DEC in its original form, not 17-Dec

Thanks,

Steve Wilson.

"Jim Rech" wrote in message
...
I tried your suggestion but 17-Dec gets converted to 38338

Are you sure you did this right? It works fine for me.

This is what my text file had in it:

123,abc,17-Dec

and the last item came into Excel as text: 17-Dec

--
Jim Rech
Excel MVP
"Always Learning" <NoMoreSpam@MyEmail wrote in message
...
| Hi Jim,
|
| I tried your suggestion but 17-Dec gets converted to 38338
| Do you have any other ideas.
|
| Thanks,
|
| Steve Wilson.
|
| "Jim Rech" wrote in message
| ...
| The OpenText method allows you to specify the data type of each field.
| You
| would have to specify that this field is text.
|
| To see how this is done first temporarily change the file's extension
to
| TXT
| (just so long as it is not CSV). Then record a macro as you open the
file
| in Excel. The File Import Wizard will appear and you use the third
screen
| of the wizard to change the field type of the "date" field to Text
from
| General.
|
| The resulting macro will demonstrate using the FieldInfo parameter to
| control data type.
|
|
|
| --
| Jim Rech
| Excel MVP
| "Always Learning" <NoMoreSpam@MyEmail wrote in message
| ...
| | Hi There,
| |
| | I have a procedure that imports a .csv file.
| | All was going well until some of the cell values being imported were
| 17DEC,
| | 28DEC and that made Excel think they are dates and puts into the
cell
| 17-Dec
| | & 28-Dec, which is wrong, these are actually stock codes.
| | How do I make sure that when I am processing the data that it stays
| exactly
| | as it is in the .Csv file.
| | Do I have to format the column to be Text before hand? When I tried
that
| it
| | changed 17DEC into something like 38334
| |
| | Any help or advice would be appreciated.
| |
| | Best Regards,
| |
| | Steve Wilson.
| |
| |
|
|
|
|

Same good result<g. By selecting Text on the third screen of the wizard
for the third data field in my example it tells Excel to bring the item in
"as is". If you're sure you're doing this right I don't know what to tell
you.

--
Jim Rech
Excel MVP
"Always Learning" <NoMoreSpam@MyEmail wrote in message
...
| Sorry Jim,
|
| It is 17DEC in its original form, not 17-Dec
|
| Thanks,
|
| Steve Wilson.
|
| "Jim Rech" wrote in message
| ...
| I tried your suggestion but 17-Dec gets converted to 38338
|
| Are you sure you did this right? It works fine for me.
|
| This is what my text file had in it:
|
| 123,abc,17-Dec
|
| and the last item came into Excel as text: 17-Dec
|
| --
| Jim Rech
| Excel MVP
| "Always Learning" <NoMoreSpam@MyEmail wrote in message
| ...
| | Hi Jim,
| |
| | I tried your suggestion but 17-Dec gets converted to 38338
| | Do you have any other ideas.
| |
| | Thanks,
| |
| | Steve Wilson.
| |
| | "Jim Rech" wrote in message
| | ...
| | The OpenText method allows you to specify the data type of each
field.
| | You
| | would have to specify that this field is text.
| |
| | To see how this is done first temporarily change the file's
extension
| to
| | TXT
| | (just so long as it is not CSV). Then record a macro as you open
the
| file
| | in Excel. The File Import Wizard will appear and you use the third
| screen
| | of the wizard to change the field type of the "date" field to Text
| from
| | General.
| |
| | The resulting macro will demonstrate using the FieldInfo parameter
to
| | control data type.
| |
| |
| |
| | --
| | Jim Rech
| | Excel MVP
| | "Always Learning" <NoMoreSpam@MyEmail wrote in message
| | ...
| | | Hi There,
| | |
| | | I have a procedure that imports a .csv file.
| | | All was going well until some of the cell values being imported
were
| | 17DEC,
| | | 28DEC and that made Excel think they are dates and puts into the
| cell
| | 17-Dec
| | | & 28-Dec, which is wrong, these are actually stock codes.
| | | How do I make sure that when I am processing the data that it
stays
| | exactly
| | | as it is in the .Csv file.
| | | Do I have to format the column to be Text before hand? When I
tried
| that
| | it
| | | changed 17DEC into something like 38334
| | |
| | | Any help or advice would be appreciated.
| | |
| | | Best Regards,
| | |
| | | Steve Wilson.
| | |
| | |
| |
| |
| |
| |
|
|
|
|

Have you tried renaming the file with a .txt suffix so you get th
wizard when importing ? You can then set the column format
individually. You can record macro code and change to .csv in that

--
Message posted from http://www.ExcelForum.com

Thanks Guys,
I am trying stop too much user intervention and allow them to just select
the file they want to import.

all the best,

Steve Wilson.

"BrianB " wrote in message
...
Have you tried renaming the file with a .txt suffix so you get the
wizard when importing ? You can then set the column formats
individually. You can record macro code and change to .csv in that.


---
Message posted from http://www.ExcelForum.com/