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.
|
|

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/

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.
| | |
| | |
| |
| |
| |
| |
|
|
|
|

Hi Jim,

I know that if I name a file with a .Txt extension it will allow me to pick
the column to come in as text when using the import wizar but I am not doing
things this way.
A user is opening an Excel file with VBA coding. It shows them a dialogue
box for them to pick a .csv file to work on. That file is then opened and
the VBA code then adds borders, page breaks and the like to create a nice
printed report.
The user does not use the import wizard and I do not want them to. All I
want is for me to be able to select column C and make it's format to be text
and the 17DEC to stay as that. When I format the column it turns 17DEC into
38338.

If you open a worksheet and put 17DEC into a cell and tab off the cell it
automatically gets changed to 17-Dec. Now format the cell to be text and it
automatically gets changed to 38338. Very annoying.

Thanks for all your help Jim, you have been very patient.

I would appreciate any other ideas.

Best Regards,

Steve Wilson.

"Jim Rech" wrote in message
...
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.
| | |
| | |
| |
| |
| |
| |
|
|
|
|

Well, I was mistaken. I thought I knew something but I must have just
assumed it. Specifically, even if you use the Opentext method, with a CSV
file Excel still automatically formats as a date even if you specify another
format. How annoying! My apologies.

Here's another approach - Unlike opening the text file in a new workbook
this opens it directly into the active sheet. If you want a new workbook
you'd have to add WorkBooks.Add to the macro before the part that opens the
file. This code is specific to the little file I've been using - one with
only 3 items: 123,abc,17Dec. You'd have to create a CSV file with this in
it before running this macro.

What do you think?

Sub Macro1()
Dim FName As Variant
FName = Application.GetOpenFilename("CSV Files (*.CSV),*.CSV")
If FName < False Then
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FName & "",
Destination _
:=Range("A1"))
.Name = ""
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End If
End Sub


--
Jim Rech
Excel MVP
"Always Learning" <NoMoreSpam@MyEmail wrote in message
...
| Hi Jim,
|
| I know that if I name a file with a .Txt extension it will allow me to
pick
| the column to come in as text when using the import wizar but I am not
doing
| things this way.
| A user is opening an Excel file with VBA coding. It shows them a dialogue
| box for them to pick a .csv file to work on. That file is then opened and
| the VBA code then adds borders, page breaks and the like to create a nice
| printed report.
| The user does not use the import wizard and I do not want them to. All I
| want is for me to be able to select column C and make it's format to be
text
| and the 17DEC to stay as that. When I format the column it turns 17DEC
into
| 38338.
|
| If you open a worksheet and put 17DEC into a cell and tab off the cell it
| automatically gets changed to 17-Dec. Now format the cell to be text and
it
| automatically gets changed to 38338. Very annoying.
|
| Thanks for all your help Jim, you have been very patient.
|
| I would appreciate any other ideas.
|
| Best Regards,
|
| Steve Wilson.
|
| "Jim Rech" wrote in message
| ...
| 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.
| | | |
| | | |
| | |
| | |
| | |
| | |
| |
| |
| |
| |
|
|
|
|

Hi Jim,

Yes Perfect. It works.
I have just got to get it to work in my stuff now.

Thanks a lot you have been a great help.

Best Regards,

Steve Wilson.

"Jim Rech" wrote in message
...
Well, I was mistaken. I thought I knew something but I must have just
assumed it. Specifically, even if you use the Opentext method, with a CSV
file Excel still automatically formats as a date even if you specify
another
format. How annoying! My apologies.

Here's another approach - Unlike opening the text file in a new workbook
this opens it directly into the active sheet. If you want a new workbook
you'd have to add WorkBooks.Add to the macro before the part that opens
the
file. This code is specific to the little file I've been using - one with
only 3 items: 123,abc,17Dec. You'd have to create a CSV file with this in
it before running this macro.

What do you think?

Sub Macro1()
Dim FName As Variant
FName = Application.GetOpenFilename("CSV Files (*.CSV),*.CSV")
If FName < False Then
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FName & "",
Destination _
:=Range("A1"))
.Name = ""
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End If
End Sub


--
Jim Rech
Excel MVP
"Always Learning" <NoMoreSpam@MyEmail wrote in message
...
| Hi Jim,
|
| I know that if I name a file with a .Txt extension it will allow me to
pick
| the column to come in as text when using the import wizar but I am not
doing
| things this way.
| A user is opening an Excel file with VBA coding. It shows them a
dialogue
| box for them to pick a .csv file to work on. That file is then opened
and
| the VBA code then adds borders, page breaks and the like to create a
nice
| printed report.
| The user does not use the import wizard and I do not want them to. All I
| want is for me to be able to select column C and make it's format to be
text
| and the 17DEC to stay as that. When I format the column it turns 17DEC
into
| 38338.
|
| If you open a worksheet and put 17DEC into a cell and tab off the cell
it
| automatically gets changed to 17-Dec. Now format the cell to be text and
it
| automatically gets changed to 38338. Very annoying.
|
| Thanks for all your help Jim, you have been very patient.
|
| I would appreciate any other ideas.
|
| Best Regards,
|
| Steve Wilson.
|
| "Jim Rech" wrote in message
| ...
| 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.
| | | |
| | | |
| | |
| | |
| | |
| | |
| |
| |
| |
| |
|
|
|
|