TransferSpreadsheet - force all to text data type

[idbr]

Hi,

Does anyone know how to force Access to import all columns as text when using TransferSpreadsheet?

I want to import lists of 'dates' where I know that some will be invalid. Unfortunately, depending on the sort order Access does one of two things:

1)Imports as date data type and deletes the 'bad' data to an import errors table.

2)Imports as text, changing the date values to dateserials

eg:
Data looks like

01/01/2000
01/01/2000
Dave
Bill

imports as:

1)

01/01/2000
01/01/2000
[Null]
[Null]

Field data type = Date

2)

34589
34589
Bill
Dave

Field data type = text

What I want is

01/01/2000
01/01/2000
Bill
Dave

Field data type = text

I'm using IsDate to check if the value is a date and increment one of two counters depending on if it's false or true. IsDate(34589) = False

I've tried using CDate to convert the serials, unfortunately IsDate(CDate(Bill)) = True! (because CDate(Bill) = 0)

So does anyone have any suggestions? I'm using A2002 on an XP system.

Thanks, Iain

 

[Craig0201]

2 isn't a problem.

IsNumeric(34589) will return true and can be converted to a date using CDate.

Craig

 

[idbr]

Craig,

I'd considered this, but the problem then is where I have non valid numeric values in the field:

isnumber(3) = true

CDate(3) = 02/01/1900

I need to be able to handle these sorts of 'bad' values too.

Also, the choice Access makes for the field data type seems pretty random, so sometimes I'm not going to get the 'bad' values imported. It's essential that I import the list 'as is'.

Iain

 

[Craig0201]

Then you're going to have to use Excel automation to read the file cell by cell, i'm afraid.

Craig

 

[idbr]

Hmm, that's the last thing I want to do - sloooooooooow!

There must be a way to force the data to be imported as text - any takers?

 

[Craig0201]

Use the Advanced Options to create an import spec?

Craig

 

[idbr]

Craig,

Not sure what you mean there - perhaps you could explain a bit more?

In the meantime I'm working round by seeding the first 10 rows of the import file with text values to ensure that all fields are imported as text.

I'm then handling the different case scenarios with a series of If statements like this:

            Do Until rst.EOF
            
            'Debug.Print rst("ID")
            
                If ISNULL(rst(strDataPoint)) Then                 
                    intBlanks = intBlanks + 1
                
                    rst.MoveNext
                
                ElseIf IsDate(rst(strDataPoint)) Then
                
                    intGoodData = intGoodData + 1
                
                    rst.MoveNext
                
                ElseIf IsNumeric(rst(strDataPoint)) = False Then
                
                'Debug.Print "Value = " & rst(strDataPoint)
                
                    intBadData = intBadData + 1
                
                    rst.MoveNext
                
                ElseIf (Len(rst(strDataPoint)) = 5 And Val(rst(strDataPoint)) > 32873) Then
                
                    intGoodData = intGoodData + 1
                    
                    rst.MoveNext
                    
                Else: intBadData = intBadData + 1
                
                    rst.MoveNext
                
                End If
         
            Loop

This handles Nulls first, then any values where isdate(value) = true, then any non-numeric values (must be 'bad'), then finally any numeric five figure value which evaluates to a date after 01/01/1990 (I know that any dates before this are invalid).

There must be a better way though...

 

[jebry]

Hi!

On the Excel side you can create an import sheet in the Excel workbook where each cell in the new sheet is has the following formula:

="'" & OriginalSheet!SameCell

Access will read the ' as meaning the field should be considered text and import it that way.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[idbr]

Jebry,

An interesting idea there. Any ideas as to how to do it? I know that the number of columns in my import file is always going to be 38, so that's not a problem.

I can get the number of rows by going to a field that I know is 100% complete and using xlspecialkeysdown to get the number of the last row.

However, what I don't know how to do is put the formulas into the sheet. It's probably a d'oh moment ahead!

Iain