VBA Data Import Routine (multipe XLS into Access) with duplicate fields/values into DISTINCT tables

[skydivetom]

Orange:

I responded to your questions and feedback after 10pm last evening. This morning, I checked out the latest version incl. the [fldtxt], [fldNum], [fldDub], [fldCur], and [FldMemo] and completed some additional testing. First though, the last field [FldMemo] is particularly important to me given that the legacy data includes various LONG TEXT fields describing organizational incidents.

So, just to make sure I follow every correctly, I did some additional testing incl.:
1. I changed the table name "BobsUrUnkl" to "OrgE". All else remained unchanged.
2. Opened "frmTest" and executed the 3 command button top-down.


3. Opened "FieldNamesQ" and noticed I have 17 records (see attached JPG).
4. Then, I compared those 17 records and tried to see whether or not I have LK for each of them. Low and behold, I noticed the routine (apparently) did NOT create an LK for [FldMemo].

Again, besides update the table name to now reflect "OrgE" as the one that contains the new field type, no changes were made whatsover.

Do you have any thoughts as to why the memo field did NOT results in the LK creation?

Thank you,
Tom

[orange]

I mentioned in post 27 or 28 that Memo would result in an error.


If you look at the intro in the latest version:

 ----------------------------------------------------------------
' Procedure Name: CreateLKTables
' Purpose: Create the LK_ tables and populate with data from the Org tables.
'
' Will create and populate datatypes text, number, long, double, currency
' Memo and others are not supported and will result in a Custom Error if found
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 17-Mar-21

I do not handle Memo, attachment datatypes with this set up.

I was suggesting that some datatypes don't work with various constructs.
For example, table data macros don't work with memo, attachment, multivalue...

You'll see I have coded to display Custom Errors for Memo and other data types

  ....
190               Case "Memo"  'unlikely in the application
                      'Raise custom error --unsupported can't have memo as pk
200                   Err.Raise 5095, , "LK_" & rsFields!Field_Name & "    LKRelated: Memo datatype not supported"
210                   GoTo GetNext
220               Case Else        'other non supported type  -allow for anything else
                      'Raise custom error unsupported datatype
230                   Err.Raise 5096, , " LKRelated: Some unsupported datatype " & rsFields!Data_Type
240                   GoTo GetNext

I was testing with a few datatypes and the code supports text, number, double and currency.

A Memo/Longtext field can not be a primary key. And long text without some qualifying info makes little sense.
A single field, memo datatype file would be like a series of unrelated paragraphs or descriptions.

If you need Memo support, then I suggest you give me some representative test data and we can design a solution.

I have used memo fields for Company Descriptions, Product/Service descriptions, NAICS descriptions, Marketing description- in English, French and in some case Spanish. But they were always identified with a Type, Language and related Company/Product etc.

[skydivetom]

Orange:

I apologize for the misunderstanding. Yes, I do recall having read it. At the same time, based on the example table [BobsUrUnkl] which includes the following two (2) memo records, I was under the impression that you may had come up with a work-around. Again, I sorry for misinterpreting it.

"In this SQL script, the Field1 column contains a COUNTER data type. The COUNTER data type stores a long integer value that automatically increments whenever a new record is inserted. Notice the number 4 is defined immediately following the COUNTER data type. This number causes the Field1 column to default to 4 and increment thereafter. If you do not specify a number with the COUNTER data type, the column will begin incrementing with the number 1."

"The Field2 column contains a TEXT data type. The TEXT data type is a variable-length data type that stores a combination of text and numbers up to 255 characters."

While I'd like to share some actual representative examples of memos, I'm a bit hesitant to post them in this forum given the sensitivy of the underlying data. Ultimately though, those "memos" are merely long text. Some of them are up to 2000 characters in length; others are even exceeding 2000 characters. In the long run, we'll have to handle to very long (greater than 2k characters) but that a different story.

So, for now, I took your [BobsUrUnkl] table and called it [OrgE]. I then went to the web and copied some arbitary text/story. The text really doesn't matter. More importantly though is that cell E4 contains text with a character length = 1,614. Similarly, cell E5 has a character LEN = 1,815. And then just for DISTINCT testing purposes, I copied text value cell E4 into E6.

// Break break //

Now, prior to posting this follow-up, I ran the latest DB with OrgE (incl. the long text). This time around, [LK_Source_Memo] was created. However, the text was chopped off just after a few characters. At the same time, it only shows the 4 distinct records (which is great).

All that said, maybe I can get by w/o the "memo" field as long as Access imports those columns as regular text AND where the LK creation routine OR table generation won't chop off text after, e.g., 30 or 50 characters.

Please find attached the XLS "OrgE" w/ those arbitrary long sample records. Any additional feedback/info would be greatly appreciated.

Happy Friday to you, by the way!

Tom

[orange]

Tom,

I have updated the routine

'Updated:
'19-Mar-21 - a few spelling corrections and comments
'19-Mar-21- adjusted to also accept , create and populate Memo, Date and Boolean(YesNo) datatype

Just doing some testing and cleanup.

I'll also add some comments to DocumentTables stuff.

UPDATED:
I am attaching another zip

Datadictionary has been cleaned up a little, added some comments
the database has been tested with other datatypes and some data. I copied your OrgE to another table OrgJack
and added a few fields, different datatypes and some data.

It specifically rejects Attachment data type. It does handle Date and yes/No and Memo.

Give it a try and see how it goes. Let me know if there are issues. Or more test data.

[skydivetom]

Orange -- first of all, I want to thank you being so very patient (with me) and continuing to provide help w/ this activity. I downloaded the new file and starting testing.

Having the ability to create a Lookup for Boolean, Date, and Memo is fantastic. I do NOT deal with *attachments*... so we're good on this one.

Were changes made across all four functions/subs in the 2 modules? Just curious...

WRT to testing, I was glad to see that the LK_SourceMemo is built as part of the routine. At first glance though, it appears text is still chopped off. Pls see attached JPG where the source table (OrgE) stores a few (nearly 2000 characters) records; however, in the LK_SourceMemo table, they're stopped off (probably around 255 chars).

Did I miss something or should I execute the routine differently? So, just making sure I have full understanding about the process. THANK YOU!

[orange]

Thanks for testing.
I have found and fixed the issue.



I am attaching a zip with a revised database--still named TomProposed3J.accdb.
Module1 has the revised code.

You should use these versions of DataDictionary and Module1. Don't try to copy pieces.

You can run a query such as below to get the length of a memo field.

SELECT len(Source_Memo) AS LenOfMemo
, Source_Memo
FROM LK_Source_Memo;

**** NOTE*** To get a memo to append all characters and not just the first ~255, I had to remove the DISTINCT
keyword from the SQL of the INSERT statement. And in the test data where I copied data from Orge to OrgeJack
I have the same 5 records in each table--so I get 10 records in LK_Source_Memo ( 2 copies of the 5 records).
This was a quick fix, but I couldn't find another method that loaded more than 255 chars of a Memo field. There may be
a different approach, but I haven't pursued it.


Also, I noticed your post re SQL DROP tables in a loop.
There is an example of that in the Deletes procedure in Module1.
That's how I remove the LK_ tables.

Check it out and t me know how it goes.

[skydivetom]

Jack:

First of all, the last version (with MEMO data type not being chopped off) works fantastic!!

Allow me to recap what I achieved today:
- I've done plenty of testing w/ the actual data set (at the present time, 5 XLS where some of them had 20k+ rows/records).
- Please keep in mind the total # of DISTINCT fields across the 5 XLS are nearly 150. Further, there will be additional spreadsheets (and based on preliminary analysis of the other XLS), I am confident that the total # of DISTINCT fields will grow past 200.
- When first processing the relatively large amount of XLS data, I quickly ran into an error (see attached JPG). However, your "Data Dictionary" came in really, really handy. In there, I noticed that in a few cases, the various different organizations stored data for the **same field** in **different data formats**. For example, OrgA may stored data in field [XYZ] as "double" where OrgB stored data in [XYZ] as "text". (Let's not ask why... it is what it is!)
- Anyhow, due to this error (and for testing purposes only), I manually deleted, e.g., the "text" version and kept the "double" format. Obviously, this will have to be addressed as part of data cleansing but for now, it's ok.
- So, once those ~15 cases dual data type cases were addressed, I re-ran the function...

... VOILA... it worked PERFECTLY!!!!!

So, words cannot describe the level of positive excitement (and joy) that this extremely handy database tool will offer me/us.

Naturally, I will keep exploring/testing the DB given that the remaining (untested) XLS may throw new errors (due to data inconsistencies; so outside the modules/functions).

Now, at the present time, we have exchanged quite a few posts. Also this thread has reached nearly 400 views (not really sure if that's large or an average #). From my perspective though, I am leaning towards "closing" the thread. Again, you have gone way beyond what anyone could ask; your patience to my MANY, MANY questions and follow-up were always promptly addressed by you. That's truly amazing in and of itself.

In the event I will run into a programmatic error/issue, I hope you'd be ok if I follow up. If so, please let me know if you prefer me opening a new post or simply add to the existing one.


//

Btw, you may recall an earlier question where I asked about deleting certain LK tables. I opened a new question @ https://www.accessforums.net/showthread.php?t=83262

Micron responded and I can truly appreciate his position. Naturally, w/o any specific background, his point of "Things can go real bad in a split second" is absolutely correct.

In case you think it may be worth a shot, I'll provide more background here:
- You already know the situation about the various XLS.
- Obviously, you are fully aware how the many LK are created.
- Not, what I haven't really gotten into is how we'll utilize the LKs.
- Ultimately, the current DB (the one we have been working with) will be a "staging DB". That is, I can rather quickly combine all alike fields from various orgs into separate tables.
- Now, once we copied all LKs from the staging DB into a "data transformation DB", I plan to add additional fields to each LKs. Here's where I'll place the corresponding values from the target system.
- As for now, I envision that this process will allow me to "detect" every existing value from the source systems (my LKs) and then map it to the new target system values.

... now here's where the DELETE LK function will come in.

- Based on discussion, some of the existing fields will a) NOT be converted whatsoever into the target system and b) some fields will not require a data translation. That is, for the latter, a Boolean field will remain a Boolean field.
- So, ultimately, I will populate another table and mark which fields do NOT require migration OR do not require data transformation. Those are the LKs that I automatically want to DROP (not in staging DB but in data transformation DB). So, even if I were accidentally delete an LK, you know a can be quickly re-generated w/o anything "going bad".

All that said, if you have any thoughts (again, see example @ https://www.accessforums.net/showthread.php?t=83262), I'd certainly welcome your ideas.

So, it's Friday evening past 9pm and I'm calling it quits for tonight. Thousand thanks for everything. YOU ARE MY HERO!!

[orange]

Well as mentioned long the way - there can be a number of glitches when merging organizations and their data.
I have asked about a strategy and plan, and while I think the use of the LK_ tables may be helpful, there are other concerns.
For example what do the various spreadsheets really mean? Do the different incoming systems/applications serve different audiences? Do you have a data model or business model of the "NEW design? Mapping multiple spreadsheets into normalized tables and then reconciling the meanings and representations is not trivial. A plan and model can serve as a good guide for project planning and estimation.

Where you have "fields" with same names but different datatypes, you could build separate tables. It may help reconciliation efforts. If you just ignore one ( or more) sets of values, you may be causing more harm than good--BUT you know your environment and requirements better than readers.

Glad things are moving forward. It's been a good diversion to do a little programming.

[skydivetom]

Jack -- 100% agree with your comments/feedback.

The target will will server the same audiences (it's merely a cloud-based version vs. previous legacy systems via XLS.) Yes, there's tons of more work that we need to consider. Having baseline with all possible values shall enable us to better address data cleansing, etc.

Your help has been superb! Thanks again for the many ways to improve the solution... as well as the feedback along the way.

I'll keep you posted. Cheers!

[skydivetom]

Jack:

One quick follow-up... the LK DELETE routine does show me "how many" LK tables were deleted. Alternatively, the CREATE/LOAD does not include a counter. See attached JPG (btw, I slightly modified how the messages are being displayed).

Also, see code below where I commented out the 1st (CREATE) msgbox AND then combined the messsage as "CREATE & LOADED"...

My question: Is there an easy way to include and iCreate counter and include it in msgbox "LK tables were created & loaded with data."

GetNext:
        rsFields.MoveNext
    
    Loop
    'Skipping msgbox (next line); insted, combine via MsgBox "LK tables were created & loaded with data."
    'MsgBox "LK tables created with PKs.", vbInformation, "Create LK Tables"
    
...
...
...
    
    
Loop                                                                
RefreshDatabaseWindow                                               
    
    'MsgBox "LK tables were loaded with data.", vbInformation, "Data Loading"
    MsgBox "LK tables were created & loaded with data.", vbInformation, "Data Loading"

[orange]

I updated the code in Module1.
It will show tables created in variable iCreate
and tables rejected in variable iReject.



The updated database(same name as before) is in the attached zip.

You are aware that detail of activity is logged in the immediate window. (sample log.)

create table LK_Salary ( Salary  NUMBER   CONSTRAINT Salary PRIMARY KEY );
 create table LK_Source_Currency ( Source_Currency  Currency   CONSTRAINT Source_Currency PRIMARY KEY );
 create table LK_Source_Double ( Source_Double  DOUBLE   CONSTRAINT Source_Double PRIMARY KEY );
 create table LK_Source_Integer ( Source_Integer  DOUBLE   CONSTRAINT Source_Integer PRIMARY KEY );
 create table LK_Source_Memo ( Source_Memo  MEMO );
INSERT into LK_Age   SELECT  DISTINCT Age from OrgA WHERE  Age Is Not Null;
INSERT into LK_Age   SELECT  DISTINCT Age from OrgB WHERE  Age Is Not Null;
INSERT into LK_Age   SELECT  DISTINCT Age from OrgC WHERE  Age Is Not Null;
INSERT into LK_Age   SELECT  DISTINCT Age from OrgD WHERE  Age Is Not Null;
INSERT into LK_Attach   SELECT  DISTINCT Attach from OrgeJack WHERE  Attach Is Not Null;
Error - Table  Attach Is Attachment datatype and can 't be processed

Good luck. Don't forget to work on the plan to use these files.

[skydivetom]

Totally awesome!!! Thank you, Jack.

[skydivetom]

Jack -- Happy Tuesday...

I posted a related (yet different) post at the following: https://www.accessforums.net/showthread.php?t=83287

I always welcome your ideas! Thank you,
Tom

[orange]

Tom,

Here's a version with additional fields and a new button/query.

This is a replacement for the previous version --but maintains same database name.

Working on your plan, right?
Good luck.

[skydivetom]

Jack -- I just downloaded the zip version. I'll check out how it works later on.

Working the plan? Yes, in fact, I've been on conference call for several hours today to discuss way ahead. Thanks for the reminder!

//

I just tested the provided solution (while still on call). Works great... I'll scan through the VBA to see if I can update the existing code (I made my own comments and don't want to re-copy it). Hopefully, it's only a few lines of code that were added, right?