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

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.

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?

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.