Excel insists text import is Text Date with 2-Digit Year

Steve Swift

Board Regular
Joined
Jan 18, 2004
Messages
187
Hi all,

This is driving me nuts can anyone help with this. I'm importing data from a txt file which is comma separated into excel 2007 using the wizard. I'm formatting all data as text during the import. The problem I'm having is that IF the data being imported COULD possibly be a date then excel insists it is and has the warning "Text Date with 2-digit year"

As a result these would have the error

11-06-41
30-11-75

and these would be fine because they could not possibly be a date.

40-47-79
08-92-86
30-94-04

I have spent hours searching for a solution. I have tried it on a variety of PC's with XP & Vista and Excel 2007 and all have the same problem. The closest I've come to a solution is it may be something to do with regional date settings but I have checked and these all use / as the separator and NOT the - I am trying to import as part of these numbers.

Any help or suggestions would be appreciated

I'm in the UK with UK regional settings.

Steve
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
I haven´t tested this, but found through Google.

edit: Just tested using the import function, which works fine if you specify the column as text.


You have to change the CSV to XLS to prevent the change. I would rename the
CSV file to TXT. then inport the TEXT file into excel using Data - Import
External Data - Import Data. You can then specify in the wizard to import
Text.

You can do the same thing using File - OPen - Text file (*.PRN,*.TXT,*.CSV)
 
Last edited:
Upvote 0
No, that is not a solution. I wish it were that simple. As I explained in the question I am starting from a txt file and formatting as txt during the import to excel before saving as a csv.

Unfortunately if the solution was to be found on a quick google I'm sure I'd have found it in the last 10+ hours that I have been looking.

Thanks anyway, any other ideas?

Steve
 
Upvote 0
I did test it , and it works for me ( 2003 though here ).

Happy to take a look with 2007, or send me an example of your txt file that your importing.
 
Upvote 0
Hi,

If it works for you in 2003 then it may be a quirk with 2007 but I am more thinking its about th eregional settings.

Here is some test data. Save as a txt file then import it. You should see the problem in AM2 only where below you can see 31-03-03 which unlike the other 2 lines can be misinterpreted as a date.

----------
12345678,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"Bank Name","City",,,,,"Members Name",40-02-00,87654321,,1,,
12345678,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"Bank Name","City",,,,,"Members Name",31-03-03,87654321,,1,,
12345678,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"Bank Name","City",,,,,"Members Name",32-02-00,87654321,,1,,
-----------

By the way, if it looks fine can you save it as a csv then close and reopen it. Is it still fine?

Thanks

Steve
 
Upvote 0
Again, on 2003 it works if I go "Data / Import external Data".
Opening the file normal it converts to dates as you experienced.

I tried with this sub, and that seems to work "ok".
I´ll try on 2007 tonight.

Code:
Sub GetTextFile()
    
    Dim sFile As String
    Dim sInput As String
    Dim lFNum As Long
    Dim vaFields As Variant
    Dim i As Long
    Dim lRow As Long
    Dim vaStrip As Variant
    
    Const sDELIM = "," 'Set the delimeter
    
    lFNum = FreeFile
    sFile = "C:\Documents and Settings\LocalUser\My Documents\Downloads\test.txt"
    vaStrip = Array(vbLf, vbTab) 'list the text to strip
    
    'Open the file
    Open sFile For Input As lFNum
    
    'Loop through the file until the end
    Do While Not EOF(lFNum)
        Line Input #lFNum, sInput 'input the current line
        
        'remove the unwanted text
        For i = LBound(vaStrip) To UBound(vaStrip)
            sInput = Replace(sInput, vaStrip(i), "")
        Next i
        
        'split the text based on the delimeter
        vaFields = Split(sInput, sDELIM)
        lRow = lRow + 1
        
        'Write to the worksheet
        For i = 0 To UBound(vaFields)
            Sheet2.Cells(lRow, i + 1).Value = vaFields(i)
        Next i
    Loop
    
    Close lFNum
    
End Sub
 
Upvote 0
I Finally figured out what is causing this problem. To be honest I'm amazed this isn't widely reported.

When you import a text file into excel using the wizard, Excel displays the data the way "IT Decides" you want to see it. (rather than what you asked for)

If you now save that file as a CSV and close excel all will be well. DESPITE what you saw on the screen.

Open the CSV file you just created with notepad (not excel) and you'll see the formatting is fine.

The problem comes when you open the CSV with Excel to review the data. Excel now assumes the excel format is correct and the next time you save that CSV file it will permanently reformat it.

So, it seems that if you import from text to excel you need to always check the resultant CSV file with notepad to make sure you have the correct and expected format.

Thanks for your help

Steve
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top