Stopping excel from converting numbers into dates on import

R

RW

This has been a long-time frustration to me. There are lots of instances
where I import delimited data - and dashes (eg, 1-2) & slashes (eg, 3/4) in
the data get converted to dates. I want excel to simply import what it
finds without converting to dates. I'm a sociology professor & import data
from many sources (eg, survey monkey, colleagues), and this really gets in
the way.

I've googled this, searched the MS discussion groups on the web, and don't
see a good answer.

I ALREADY KNOW the answers, "format the cell as text" or "insert an
apostrophe ' before the entry." THESE DON'T WORK when you are importing a
csv or other delimited file. Once the data are converted to dates, you
can't change them back. Excel has fully converted the cell, and if you
change it to another format (eg, the internal number), you still can't
restore the original data.

I need a PROGRAM OPTION to turn this conversion function off PRIOR to
import. THE OLD ANSWERS DON'T WORK.

Many thanks for any help on this.

RW
 
J

Joe User

How are you importing the data?

In the Text Import Wizard (Data > Import External Data > Import Data in
Excel 2003), there is an option to set the Column Data Format to Text.

Works for me. If that doesn't work for you, please explain why. Include
the Excel version and exactly how you importing the data.


----- original message -----
 
R

RW

It works in principle, but if I'm importing a large data set (say a survey
with 100-200 variables as columns), it's pretty hard to search out those
columns. Plus, sometimes someone has inserted data (1-2, 3/4) in a column
that is supposed to be numeric. If you have lots of cases (say, a survey
with N=1,500), it's almost impossible to find that stuff in there.

Really, the question should be on MS, why it converts unless you ask it to.

Doesn't really matter which version of excel. It's been doing this for
years & years and shouldn't.
 
J

Joe User

RW said:
Really, the question should be on MS, why
it converts unless you ask it to.
Click to expand...

I agree: it should be an option. But that is not very constructive.


----- original message -----
 
J

JVBeaupre

Just for the heck of it, try running this macro...
& select the textfile to be opened in the dialog box...

Sub text_import
fileNM = Application.GetOpenFilename
Workbooks.OpenText Filename:=fileNM, _
Origin:=437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=True, _
Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array
(4, 2))
End Sub

The "treat all columns as text " occurs in the fieldinfo:= arrays.
I recorded this for 4-column data and in Excel 2003, it applies the
text formating to a "1-2" in column 10.
No "2-Jan" entries observed.

The test file contained 2 rows:
1-2, 12, 1/2, 1-2
1 , 1-2, 1-2, , , , , , , 1-2

Regards,
Jim
 
J

JVBeaupre

Just for the heck of it, try running this macro...
 & select the textfile to be opened in the dialog box...

Sub text_import
    fileNM = Application.GetOpenFilename
    Workbooks.OpenText Filename:=fileNM, _
        Origin:=437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=True, _
        Space:=False, Other:=False, _
        FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2),Array
(4, 2))
End Sub

The "treat all columns as text " occurs in the fieldinfo:= arrays.
I recorded this for 4-column data and in Excel 2003, it applies the
text formating to  a "1-2" in column 10.
No "2-Jan" entries observed.

The test file contained 2 rows:
1-2, 12, 1/2, 1-2
 1 , 1-2, 1-2, , , , , , , 1-2

Regards,
Jim
Click to expand...

Well, it doesn't work all the time. Sorry.
Jim
 
R

RW

This looks heroic, even if it doesn't work!! Thanks for the try!

If it did work, would it make ALL cells text, even if they should be
numbers?

More's the pity MS keeps wanting to make up your mind for you!
Occasionally, I actually do know what I want ... :)

RW

Just for the heck of it, try running this macro...
& select the textfile to be opened in the dialog box...

Sub text_import
fileNM = Application.GetOpenFilename
Workbooks.OpenText Filename:=fileNM, _
Origin:=437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=True, _
Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array
(4, 2))
End Sub

The "treat all columns as text " occurs in the fieldinfo:= arrays.
I recorded this for 4-column data and in Excel 2003, it applies the
text formating to a "1-2" in column 10.
No "2-Jan" entries observed.

The test file contained 2 rows:
1-2, 12, 1/2, 1-2
1 , 1-2, 1-2, , , , , , , 1-2

Regards,
Jim
Click to expand...

Well, it doesn't work all the time. Sorry.
Jim
 
D

Dave Peterson

Rename the .csv file to .txt.

Then when you use file|open to open the text file, you'll be able to specify how
each field should be treated.

This will work in code, too--if the file does not have a .csv extension.
 
J

JVBeaupre

This looks heroic, even if it doesn't work!!  Thanks for the try!

If it did work, would it make ALL cells text, even if they should be
numbers?

More's the pity MS keeps wanting to make up your mind for you!
Occasionally, I actually do know what I want ...  :)

RW
Click to expand...

What I discovered it that if you want a column (say column 10) to be
read as text then you need "Array(10,2)" in the list following
FileInfo.
I tried recording opening a file selecting all 256 columns as text and
of course you don't get enough continuation lines to capture all the
FileInfo list.

--> But, and I think this is the real solution, when you import the
file at the juncture where you can make each column text, you can do
the shift select thing:
Select column 1 as text, hold down shift and scroll to the end of the
columns and with shift still selected, click on the text button. At
least that worked for me running Excel 2003 on a Windows partition on
a Mac.
 
J

JVBeaupre

If it did work, would it make ALL cells text, even if they should be
numbers?
Click to expand...
Here's a case where the MS folks actually choose the useful option:
Excel knows it's text but it also knows that it's a number in text
format. So if you have say, 12 as text in cell B1, then you get 24 as
a number in any cell containing "=2*B1". If you use macro processing
then you can recover the number with "Val(Cells(1,2))", but any real
text will return 0 or the number to the left of the 1st real text
character before. ie, Val("123pqr") yeilds 123, but Val("pqr123")
yeilds 0
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with Excel Dates 21
import external data as a List (not table) into excel 2010 with excel 2000-2003 list format 0
How to import Start/Finish Data from Excel 1
converting to dates... 14
Excel 2003, cannot import quoted fields with separators inside 2
Converting Dates after Import 6
Import dates from TXT 11
Excel Save As Options 2

Top