MS ignores my cell definition 2

[grndpa]

Is there a way I can get Microsoft Access 2003 to respect the cell definitions I provide?

The symptoms -- as bizarre and indictable as it would seem -- suggest that Microsoft has decided I mistakenly defined the cells as text when they are clearly numeric.

Specifically, I'm importing a file that contains a column entitled HomePhoneNumber. In the source spreadsheet and in the target Access 2003 table, I've defined the cells in this column as "Text". During the import (and Link for that matter) Microsoft rejects any cell in that column that isn't numeric. (I know these are the cells causing the symptoms as Microsoft provides the column name and row number in the "import errors" table).

Granted, most of the cells in this column are 10 digits or null. However, and I have no control over this, a half-dozen or so of the cells are formatted with parentheses and dashes such as (111) 111-1111.

Access rejects these formatted cells.

Is there any way, short of sorting the file or inserting dummy rows, that I can get Microsoft to respect my definition of the cells in this row?

 

[dhookom]

IMHO, "(111) 111-1111" is clearly text and should be defined as text. Why would you want to make this into numeric?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[grndpa]

Thank you.

But read it again.

In the source spreadsheet and in the target Access 2003 table, I've defined the cells in this column as "Text".

It is Microsoft that, during the import, is apparently overriding my definition as it finds that the majority of the data is in number form (i.e. 10 digits).

The same symptoms occur when I link.

I have no control over the quality or format of the source data.

 

[SkipVought]

Hi,

"...a column entitled HomePhoneNumber. In the source spreadsheet ..."

In the source spreadsheet, convert the NUMBERS in this column to TEXT.

What is happening is that the import manager analyzes the data and where you nave NUMBERS, its a TILT !

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[grndpa]

Thanks Skip,

By "TILT" I gather you mean that I cannot override the import manager?

 

[dhookom]

I had never seen a Microsoft message like "when they are clearly numeric". This made me think this was your opinion.

One workaround is to add a new row at the top with text in it.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[grndpa]

Thank you, dhookom,

Again, I apologize for giving the impression that Microsoft produced that "clearly..." message. I was editorializing in what SkipVought confirms above -- to wit:

What is happening is that the import manager analyzes the data and where you nave NUMBERS, its a TILT !

 

[MichaelRed]

Albeit somewhat tedious, save t he spreadsheet as a CDF file and import that beastie. If there are still issues, at least you can look at the (CDF) file and perhaps modify either it or the import spect, or at least have SOME influence with the import manager during the import itself.




MichaelRed

 

[jkl0]

I've found that dhookum's suggestions works best.

One workaround is to add a new row at the top with text in it.

The import manager looks at the first row of information and sets up the columns based on that information.

In trying to import from excell, I had to create a dummy row of text, import the file and then delete that first record that had the dummy information in it.

 

[grndpa]

Thanks folks ... I really appreciate your responses.
As jkl0 points out ...

One workaround is to add a new row at the top with text in it.

And in the cases where I have only one column with this issue, that's what I do.

But what do I do when I have multiple columns. For example, in this same spreadsheet I have a column for "termination date". Only a few cells contain a date (that's a GOOD thing <grin>). And none of them are on the same rows with the formatted telephone number cell.

If I may be allowed an editorial ...

My overriding concern is that all of this requires manual intervention -- which, if I were to write a dictionary, I would define as "formula for disaster".

It just blows me away to think that Microsoft can get away with this. I might be willing to agree that, should the cell format be the default "general", Microsoft might be justified in protecting itself against sloppy programmers. But I don't understand how, on the basis of the first few rows in a column, Microsoft presumes it knows better than I do what the cell characteristics are. Particularly when there is no workaround.

Does anyone know an attorney who would want to take on Microsoft for this? I have some pre-crumpled paper for the subpoena. <smile>

 

[MichaelRed]

sometimes the glass is so close to half or something it is just as easy to call it either way ... you seem prefer to have it YOUR way, but when give the option you'd rather complain than consider the several options? Which way would YOU design the interface to import spreadsheets (which really fo not have much structure), would you presume upon each user to MANUALLY define the type content of each column EVERY time they reference the object as opposed to at least TRYING to smooth the transition?

Personally - as a PROGRAMMER, I try to understand the options available and plan the work according ton those options and the situation as it occurs. If this means actually writing a PROGRAM to accomplish the routine task ... well that's why they call it work .... and pay me to do the job.




MichaelRed

 

[SkipVought]

from one grandpa to another...

garbage in; garbage out.

We both know that the current generation of applications do allot of CYA for the user. As programmers, we ought to be aware of the pitfalls and gotchas that other people are blissfully ignorant, like behind the scenes conversion from numeric to text. IMHO, it is almost always better to beat the odds and try to anticipate these situations.

One thing that I do is educate my users regarding the difference between NUMBERS (stuff that you do arithmetic to) and NUMERIC IDENTIFIERS (stuff that LOOKS like numbers, but is REALLY functionally TEXT values)

The pity of it is that you often do not have control over the source data. So, like Michael stated, they pay is to get it right. Which often means analyzing new source data and taking appropriate measures to insure that it will be processed correctly.


Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[grndpa]

Michael Reed says

Personally - as a PROGRAMMER, I try to understand the options available and plan the work according ton (sic) those options.

As a "programmer" are you in the habit of delivering systems that require your manual intervention every time the client runs your application?

Skip Vought says

One thing that I do is educate my users ...

Unless the source system (over which I have no control) enforces that literacy, is not "garbage" inevitable? In any case, the client's employees who input this data are spread throughout the world.

Here's my workaround.

After each import, immediately run an export of that imported file and do an external comparison (in a non-Microsoft tool), routing the differences back to the client for their review

This option (unlike Microsoft) insures that the client is aware of this discrepancies. And that the repair decision regarding "garbage" and the authority and ability to repair it remains with the client who owns it. Unless I'm mistaken, this is among the few approaches "Best Practices" would endorse given the limitations. I hope you agree.

Again, this issue is that I am not aware of any other vendor arrogant enough to ignore the programmer's field specifications.

 

[PHV]

I am not aware of any other vendor arrogant enough to ignore the programmer's field specifications
In fact I believe that ms-access is a desktop database, not a professional RDBMS.

 

[grndpa]

PHV says

... ms-access is a desktop database, not a professional RDBMS

I suspect that Andy Rooney might say "I wonder if Microsoft knows that."

But based on many of the above comments, after 35 years in the business, I apparently became a dottering old fool.

Am I mistaken that Microsoft agreed to the W3C standards for databases? Is there something in those standards allowing exceptions for "desktop" databases?

I can be convinced that Microsoft is justified in deciding that it is better qualified that I am to specify what my cells may contain. And then reject anything that confirms my definition of cell type.

But if there is a compelling argument to that effect in this thread, I apparently missed it.

Or did I simpy miss the announcement that Microsoft achieved technological singularity.

 

[lameid]

Personally it hacks me off too. If the import wizard can assume the datatype on data why can't it assume the datatype based on the target table? Why do an implicit conversion to a number to do an implicit conversion to text?

Although there aren't too many good options to fix it. I have seen people play with columns for about 15 minutes to get the data updated so Access will see text as text. I never took the time to memorize the 5-15 things that can effect it as I find the workarounds consistent and faster.

Although on the flipside I think what most of the posters are saying is "here is how I would work around it". Whether you add a line to the spreadsheet to force text or save to a comma separated file, these can both be accomplished via Excel automation.

No matter how you slice it, in order to import data, the source has to be in some sort of consistent format. So yes MichaelRed, I would have the Excel import work like the text import. The software would make all kinds of assumptions about the data and then let me fix and save the import specification. MS could even turn off the change specifications part at first and make us dig around in the options sub menu to turn it on. Also, I read somewhere that MS made some improvements in this area in Access 2007 which takes the datatype from the cell format. So even MS in its Goliath snail pace has recognized this issue. Too bad Office 2007 is bloatware waiting for 2GB+ of standard desktop ram not to mention the usual list of (unacceptable) bugs. This issue is definitely in my top 5 of worst Access behaviors ever... right next to Access 97 exporting the saved version of a query without warning when you had made design changes without saving. Of course they fixed that one... by making you save the query before exporting in Access 2000. They never squashed it in Access 97.

Access is a Professional product. It is not as robust as say SQL Server or Oracle on the Database Engine side but it is a lot less hassle to use for small systems. The real issue here is people trying to use Excel as a database. Excel is not a database. Access is.

Now back to the issue at hand... Below is some modified code I wrote that puts the word text under text fields that may otherwise be messed up by an excel import. That said, I am manually determining whcih fields are which and hard coding it. And since the word text is text, the import will treat the field as text. If teaching a power user this method manually, I find they want to put every databype in the excel file on successive attempts <sigh>.

Other than that note the comments, modify to suit your application.

Sub ImportXLFile(strFile As String)
    'Requires Refernce to Microsoft Excel <Version> Object Libray
    Dim App As Excel.Application
    Dim strMessage As String
    Set App = CreateObject("Excel.Application")
    strMessage = ""
    With App
    .Workbooks.Open FileName:=strFile
    .ActiveWindow.WindowState = xlMaximized
    
    'My particular example had a stupid line at the top
    'that needs deleteing as the column headings are in row 2
    If .Range("B1").Value = "Test1" And .Range("B3").Value = "Test2" Then
        .Rows("1:2").Select
        .Selection.Delete Shift:=xlUp
        'Insert a Row after the delete
        .Rows("2:2").Select
        .Selection.Insert Shift:=xlDown
              
        'Validate Format
        If .Range("A1").Value = "FieldName1" And _
            .Range("B1").Value = "FieldName2" And _
            .Range("C1").Value = "FieldName3" And _
            .Range("D1").Value = "FieldName4"
        Then
            'Identify text fields
            .Range("C2").Select
            .ActiveCell.FormulaR1C1 = "text"
            .Range("D1").Select
            .ActiveWorkbook.Save
        Else
            strMessage = "Sheet is not in the expected format.  The sheet will have to be processed manually."
        End If
    Else
        strMessage = "Sheet is not in the expected format.  The sheet will have to be processed manually."
    End If
    .ActiveWorkbook.Close True 'Close and save the file
    If .Workbooks.Count = 0 Then
        'Should be able to close here
    End If
    End With
    Set App = Nothing
End Sub

 

[grndpa]

Send me your address and the name of the beverage of your choice <grin>.

Seriously, this is wonderful stuff.

I need to study it, bu this appears to be exactly what I was looking for on the first post.

It may be too late for the current application.

But this is wonderful stuff.

Thank you.

 

[SkipVought]

Check out

http://support.microsoft.com/kb/281517.

This refers to the registry setting that determines the number of rows that the import manager uses to GUESS the data type.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[JoeAtWork]

Basically it is the price you pay for using someone else's tool for importing the data. If you want to use it, you must learn to live by it's rules, and work around it's quirks.

If I were in your shoes, where everything must be completely automated for the user and you need strict control over how the information is interpreted, I would be using automation to read the data (just like lameid's code).

Access is basically designed for the layperson to use. The typical user becomes uncertain when faced with many options. More "grown-up" databases like SQL server give you much greater control over data transformations.

 

[lameid]

Skip,

Great link.

Although there is caveat... In my experience the import will treat 'text fields' that only have numbers in it as a number even if the data does have leading zero's. The price you pay for dealing with counterparty account numbers based on old mainframe.

Joe,

SQL Server is more grown up?

Using Wikipedia:
Access 1.0 released November 1992.
SQL Server 4.21 for Windows NT 1993 (hmm maybe it is older as I remember reading MS SQL and SYBEX had the same foundation code)

They are relatively contemporaneous products. SQL Server is more specific in its function and has a world of features that Access does not. Although the converse is true as Access doubles as Front End development which of course you can use (VB)(.Net) or other languaes instead. Besides Access, Progress is the only product I am aware of that has both a database and frontedn development in one package. If you are using products and want the most versatility then yes SQL Server and VB.NET are probably the way to go. I'd also like to point out that until SRS SQL did not have a cool reporting engine. And aside from scheduling and automatically deliverying reports Access has the more versatile reporting tool. Cost and time are definitly the leading reason to choose Acces over the other products. Access is excellent for RADD (rapid application development and deployment).

My point is Access has been around too long to argue it shouldn't work because it is less mature. It is simply less developed in certain areas which is why I tend to direct my frustration at Microsoft rather than try to accept it as reasonable. I fully accept that its database engine is weaker. But as an Office Application I don't buy the arguement that it should not be able to do a flexible import from an application that is in the same suite of products.

I do agree you are stuck living with whatever tool you use feature set.