Import Excel into Access DB

IThema,

Peter is correct.

You should be using the "Data Validation" utility in Excel to PREVENT incorrect values from being entered in the first place.
(Thanks Pete)

Column by column, select all the cells you wish to validate, typically A2:A65536 (A1 is reserved for the "text" field names)
Click: Data-->Validation
Under "Allow", select one of the "Number" types (Whole Number or Decimal)
Then specify the Minimum and Maximum values.
Click: OK

Now if a user types in text, they will get an error message.

That being said... if you ALREADY have unvalidated sheets ready to import, you could copy this formula down the entire column next to the column in question:
=IF(ISTEXT(A2),"Bad","Number")
Then do a Search for:    Bad
Remember to set "Look In" option to "Values"

On the other hand you could use:
=IF(ISNUMBER(C2),"Incorrect","OK")
To find all the Text Fields that might contain numbers.

Hope this helps as well

Hi,

Thank you both for your responses,

Pete,
>Importing directly into the destination table is a bad idea if you don't have complete control over the data
You're right: we don't have complete control over the data, but having the data types right has his some nice benefits from this point on (the import needs to be fast and accurate, because the actual import will start AFTER the excel data is transferred to a temporary table).
> And you cannot expect a fast import if you have to validate every field in every record programmatically
You're right again: Validating every cell before continueing isn't an option. It's slow and requires the user to have Excel installed.
> Can you not validate the data in Excel before you start the import? Is it not possible to use the Data Validation settings in XL to prevent duff data entering in the first place?
No, we deal with a great number of customers who all have different backoffice/ERP-systems and different ways for transferring data. One uses txt, the other csv. There's also people using excel files or want to import their current database. We simply cannot validate the data using formula's etc, because we don't know what the data in their files represent.

Boag2000,
See my comment on the last quote I made to Pete

Let me explain how a client defines their import:
1 - User indicates what kind of media should be used to import their data. They can choose between TXT (txt, csv), Excel (xls), Database (Access or SQL) and XML.
2 - Users may use existing files as an example to quickly configure their import definition
3 - User fine tunes/creates the definition by entering field information for the import file(s). Later, he can create a mapping between his file, and our own database. A user can include formulas in this mapping. For example, their column "Unit" could be mapped 1 on 1 with our field "Price unit", but he can also use a formula for it, like: CASE [Unit] WHEN 'g' THEN 'GRM' WHEN 'kg' THEN 'KGM' END

Now, let me give you some more information about step 3:
Part of the field definitions, he can indicate what type of data is located in each field. He may choose between "Text", "Numeric", "Memo", "Date/Time" and "Yes/No". This could be importand, because this is the data type that is being used when they add a formula for the mapping; If the import field is numeric, the field is being treated as numeric in the mapping formula. This is one of the benefits for not just declaring them all as "Text".

Say that his import not only contains "Unit" (which represents price unit), but also has a field called "Price", which is indicated as representing a numeric field. Note that we can't control what the user enters in his own Excel file! Say that he made a typo and there's a row in Excel that has "q23" as a price in stead of "123". When this file gets imported (and it takes about half a second for 2000 lines, which is the speed I'm looking for), the "q23" gets impoted as NULL, because it is not a valid number. So, user says in his import definition: "Price" is a numeric field. He believes it, otherwise he wouldn't be saying it. I want to be able to say to him: "Look, 1 of your prices isn't what you expected it to be. It is not numeric and I wasn't able to import it correctly." This is very important, because otherwise the user will believe that everything went well and no price update/insertion will eventually take place for that particular product.

This functionality is already included within Access itself. It's in the "Import data" wizard. It shows exactly what went wrong and is still very, very fast (also half a second for 2000 records). If I were to scan every cell in the excel file, it will take a considerable amount of time. That's just not an option. Does one of you know how Access does this, or how I could use Access' functionality?

Cheers

IThema

Your aim is clear.  Without some more thought I would'nt like to comment on whether your performance goals are reasonable or not.

Can you clarify what happens at present during the 'import'.  You appear to be saying that Access somehow knows what datratype to expect for each field.  How do you do that on the fly?

Pete

Hi,

boag2000,
Thanks for all your input.

I'm considering to import everything as Text, but need to investigate what impact this will have on the entire import.

Peter,
A client can create and store multiple import definitions with our program. He predefines the Excel-import before he actually does the import. He may then use this import definition more than once, every week for example, to update the data in our program. In this import definition, he states the data type per column in the excel file. It look something like this:

Worksheet name: MyData
Decimal symbol: .
Date format: mm/dd/yyyy

Field         Data Type        Decimals
-----------------------------------------
ProdID      Text
Unit          Text                
PriceDate  Date/Time
Price         Numeric          2

Now, when the user imports a excel file using this import definition, a table is being made to represent this "MyData"-WorkSheet. It is being made, using the information given in the import definition, so it will correspond to my example given above and the Excel-data is imported into this table using the query from my original question. So, I do not determine the data types on the fly. They are already determined.

Hi Pete,

You got me thinking. If I can call a VBA routine from within VB, I might also be able to do the DoCmd from VB as well:

Set objAccess = CreateObject("Access.Application")

objAccess.OpenCurrentDatabase strDBTemp
objAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "MyProducts", "D:\MyImports\Products.xls", True
objAccess.CloseCurrentDatabase
objAccess.Quit

Set objAccess = Nothing

This works well!!

However, now I've come to this point, I ask myself wether this is the right choice, because this approach has it's drawbacks:
- User MUST have Access installed in order to make the "DoCmd"-call
- Table "MyProducts" must have column names matching the column names from Excel for 100%. If no column names are specified, it shoud use F1, F2, ..., Fx as column names. This isn't really a problem, because I can easily make this work.

When it is completed, it created a file for me (it's in Dutch):
Fout (=error)      Rij (=row)      Veld (=field)
Typeconversiefout (=type conversion error)      540      Nt#gew (=Nt.gew, which is a column name in the Excel file)
Typeconversiefout      541      Nt#gew
Typeconversiefout      562      Nt#gew
Typeconversiefout      563      Nt#gew
Typeconversiefout      564      Nt#gew
Typeconversiefout      565      Nt#gew
Typeconversiefout      566      Nt#gew
Typeconversiefout      567      Nt#gew
Typeconversiefout      568      Nt#gew
Typeconversiefout      569      Nt#gew
Typeconversiefout      570      EAN
Typeconversiefout      570      Nt#gew
Typeconversiefout      571      Nt#gew
Typeconversiefout      970      Nt#gew
Typeconversiefout      971      Nt#gew
Typeconversiefout      972      Nt#gew
Typeconversiefout      973      Nt#gew
Typeconversiefout      1046      Nt#gew
Typeconversiefout      1047      Nt#gew
Typeconversiefout      1239      EAN
Typeconversiefout      1239      Nt#gew
Typeconversiefout      1240      Nt#gew

The stupid thing is, that this file doesn't seem to be right. There's only good values listed in the EAN and Nt.gew fields, so I don't know where this is coming from :S

Anyway, this is what I asked for and I guess I will just make the call to Access if I detected the user to have Access on his system and use late binding to realise this. Otherwise, I guess I'll have to use the query and tell people that if they need more detailed import results, they should just install Access. My job now, is to find out why it gave those type conversion errors, while those values are actually OK in Excel.

I have thought some more about just declaring everything as Text, but this will cause memo-fields to truncate.

I'll leave this question open for another day to see if anyone has any other ideas about this, but as for now, I consider this question as answered. Maybe someone knows what MS Access does underneath the hood and how Access detects a value to be inconsistent with a data type. This would allow me to better understand the technology behind all this.


Cheers,

Luc Derckx

Correction:
When it is completed, it created a file for me

I mean it's created a TABLE for me.

Just a note from a guy active in the Excel TA...

> You should be using the "Data Validation" utility in Excel to PREVENT incorrect values from being
> entered in the first place.
> (Thanks Pete)

This is very true.  Then again, Excel's Data Validation comes with significant limitations, first and foremost
that it *will not prevent invalid data from being pasted in*.  It will prevent invalid data from being manually
entered, but it offers no protection against a paste.

There are some ways to safeguard against a paste, but they are cumbersome.

Generally, I advise not to use Excel as a "UI" for an Access app unless you can really, really trust your users
not to misbehave :)

Patrick

Just thinking outloud,

How about running a SUMPRODUCT formula on numeric fields before the import.  If there is a none numeric value it will error and the field can then be searched for the offending cell?

Leon

Hi leonstryker,

Thank you for joining this question.

Both Peter and Boag mentioned something like that too. They said that I may first have to check the source itself for validation. Since you're the third one that tells me so, I think I should stop being stubborn and give it a try. I'm not sure if it's fast, but I think I'll just give it a try. Nothing to lose, have I?

I've never heard of the SumProduct formula before (actually it does ring a little bell to some maths class at collage, but that was a long time ago :), but I'm sure I can figure it out.

So I'll give it a try and check back on y'all after the weekend, cause this week is over for me :D

ps: Patrick, thanks for your input, but it's a little off topic after all that's been said. The Excel file is not at all an 'extension' or 'UI' for an Access application. We also agreed that the validation feature won't help me in this context. Do you have some good ideas about how to validate an Excel file while importing, without having to examine every cell at a time?

> ps: Patrick, thanks for your input, but it's a little off topic after all that's been said. The Excel file is
> not at all an 'extension' or 'UI' for an Access application. We also agreed that the validation feature
> won't help me in this context. Do you have some good ideas about how to validate an Excel file while
> importing, without having to examine every cell at a time?

A common technique is to import into a "holding table" with everything as text, and then use SQL to
validate the data there and move it to the permanent tables.

Patrick

Hmm, I have had some problems with that technique, where numbers from Excel were not placed into text field.  You may need to play around with some setting in Access to get it right.

Leon

Hi,

I've done some more thinking (it's a very complex feature; there's more formats to support and we'd like to have common code for that) and importing everything as text could cause problems with memo-fields. Also, Leon, I think that the problem you experienced can be solved by using the property IMEX=1 in the query; this forces the Excel ISAM to use the destination data types, in stead of scanning the excel file for a predefined number of rows. If, for example, the Excel ISAM treats column A as numeric and its destination is text, the numeric values are not being transferred (I think that's stupid, because numbers can implicitly be converted to text...).

Anyway, I don't want this question to turn into a discussion to what approach is best. Discussing it may be very usefull, but goes a little off topic, since my initial question was:
>Does someone know how to programmatically import an excel file into Access, using the speedy Jet Excel ISAM and displaying the individual cells that couldn't be >imported correctly?

This question has been answered by Peter:
Yes, it's possible to do that by calling an Access command. However, there seems to be a bug in the detection of wrong cells, but that seems to be something inevitable and is actually my fault, because I failed to detect that earlier.

For those who are interested in the approach that I will now use:
Data from txt, csv, xls, xml will be imported in tables that represent the data types from the source files, meaning that I will have the Excel worksheets imported into tables that also have numeric, date, memo and yes/no data types. This way, I will not be able to show the user what I'd actually like to show ("Look, 1 of your prices isn't what you expected it to be. It is not numeric and I wasn't able to import it correctly."), but enables me to have a structured and FAST code. I could explain to our clients why we can't show these errors; every file format has its benefits and drawbacks.

I'd like to thank everyone who helped me here.

? I seem to have done something wrong. Peter, I accept your answer. Boag, thank you very much for assisting. I will post a comment in CS to correct this.