Resolved Custom import to custom table not inserting/adding new records

Hello,
Please post your import debug log and sample import file to check.

Here are links to the information:


The system reports that records were updated, and I can find where I modify the table and re-import the CSV and existing records are getting updated, but new records are not getting added. I even emptied the destination table and ran the import and got the message that 1,888 records were updated, but the table remained completely empty.

I have also tried some of the advanced options, but with the same result. I used the field mapper to build the template (wonderful, saves time!!) but in a couple of trial attempts I created a test table with the same field names...even though I deleted this later, CSVI still shows the columns/fields from that experiment. Does this cause problems having multiple projects with the same exact field name?

Just an FYI, I removed CSVI's tables and component from the test site, and re-installed, starting from scratch. No additional plugins, etc.

The log shows that a check is made for the primary key, and even detects that it is a new record...

2017-10-17 15:10:58 7 [QUERY] SELECT `MemberNbr` FROM `xxxxx_district_roster` WHERE `MemberNbr` = '563689999'
2017-10-17 15:10:58 7 [DEBUG] No record found for the given import based on field value

But shouldn't the next statement be an INSERT statement instead of UPDATE?

2017-10-17 15:10:58 7 [DEBUG] Query for Customtable
2017-10-17 15:10:58 7 [QUERY] SELECT `MemberNbr` FROM `xxxxx_district_roster` WHERE `MemberNbr` = '563689999'
2017-10-17 15:10:58 7 [DEBUG] Executed store
2017-10-17 15:10:58 7 [QUERY] UPDATE `jgs5u_district_roster` SET `DistNbr`='99',`ZoneNbr`='01',`ClubName`='TEST, MO',`ClubNbr`='36168',`RosterNbr`='2456',`Gender`='M',`Salutation`='Mr',`FirstName`='John Smith',`LastName`='Smith',`Address1`='333 N South St',`City`='TEST',`State`='AK',`PostalCode`='55512-9125',`Country`='US',`Language`='ENGL',` ... WHERE `MemberNbr` = '563689999'
2017-10-17 15:10:58 7 [DEBUG] Custom table data added

Hello,

But shouldn't the next statement be an INSERT statement instead of UPDATE?

The problem is when Joomla finds the primary key field it assumes that the row is already in the database and does an update. That is what is happening in your custom table import as your CSV file has MemberNbr column which i guess is the primary key of your custom table. You need to remove this column from your CSV file and then run the import so all records gets added. This is also explained in custom table import document under Adding and Updating section.

That doesn’t make any sense. Joomla can’t find a row in the database to update because the row with the primary key does not yet exist. So you are telling me I have to delete all the rows in the import table in Joomla! before using CSVI to import new and updated records?

Edit: again, the problem is with adding new records not updating existing records based a the primary key. And if I remove the primary key column from my import csv file, how is it going to know what the key is for the records???

Hello,

That doesn’t make any sense. Joomla can’t find a row in the database to update because the row with the primary key does not yet exist.

The primary key column is the factor with which a row in database is identified. When this column is in the import file you are telling that the row already exist and so do an update to the record.

So you are telling me I have to delete all the rows in the import table in Joomla! before using CSVI to import new and updated records?

I did not say to delete any rows from your table but i said you need to remove the primary key column in your import CSV file so new rows are inserted.

If I remove the primary key from the CSV source file...then I have no key to address the record after it is imported. The Primary key is the member account number, unique to each member. I can't add records without it otherwise its useless.

Besides, removing a column from a CSV text file would be impossible...unless we manually download, edit the file, and submit it for importation--which defeats the purpose of automating the import.

It appears that CSVI does not do a a typical record import--update existing records based on key match/add new records that don't exist in the destination table with that key. What am I missing here?

So the only way to use CSVI for custom tables is to first purge the destination table of all of its records so that all data in the source CSV file are imported (added)

Hello,

It appears that CSVI does not do a a typical record import--update existing records based on key match/add new records that don't exist in the destination table with that key. What am I missing here?

Can you tell me what field you have set Import based on field under custom table on Options tab in your custom table import settings? If nothing is set, can you try setting your member account number field in there and run the import? Does it then insert the record if the row does not exist already? If not, can you send me the latest import debug log to check?

Also, can you tell me which version of CSVI you are using?

Hello,

It appears that CSVI does not do a a typical record import--update existing records based on key match/add new records that don't exist in the destination table with that key. What am I missing here?

Can you tell me what field you have set Import based on field under custom table on Options tab in your custom table import settings? If nothing is set, can you try setting your member account number field in there and run the import? Does it then insert the record if the row does not exist already? If not, can you send me the latest import debug log to check?

Okay, I am really confused now, but here goes. I have the option set to MemberNbr (the field name of the Primary Key in the destination table). I have tried with it blank, which it defaults to the same field--that is the only key and the primary key in the table. So far I can't get CSVI to add any new records at all. It Updates just fine.

So to clarify for me on what you have said in previous posts...can you confirm CSVI's intended behavior for me?

The Primary key in the destination table is MemberNbr. The CSV source file to import has this field. I assume based on the documentation that if a row imported from the csv file has a value in the primary key column, it will do a look up on the destination table. If that record with that key already exists in the Table it will UPDATE the existing record. If a record with that key value cannot be found in the Table it should be ADDED to the destination table.

If this is a correct assumption then something is wrong. But If that is NOT how CSVI works then I understand that I need to find a different solution. It was my understanding that a typical import process is update existing records, add new ones from the same source file. What you have stated before is that it will only update records unless the primary key is removed from the source file. I would understand this only if the receiving table's primary key was auto-generated--such as the Joomla! users table with the Joomla UserId. But this is not the case here. I need the primary key to be imported along with the records data for non-existing/new records--you can't add a record with a NULL value for the primary key field/column.

There is even an option in the Advanced settings that states: "Skip new items will not create any new items if the item cannot be found" (which is set to No, assuming that it will add records that do not already existing based on the primary key in the destination table).

Joomla 3.8.1
CSVI Pro 7.3.0

The log and sample data are in my original post, but referenced here (hidden from the public)



Again, also as previously posted...the log debug entries do in fact show that it is performing the lookup based on the primary MemberNbr key. And where a record in the table with the primary key is not found CSVI issues an UPDATE statement--how can you update a record based on a WHERE clause that cannot be satisfied? It should be performing an INSERT statement if the key is not already in the destination table as I have asked.

Again, I'm being a bit verbose here because you told me that I have to remove the primary key from the import CSV file to add records. This would not work as prescribed because a primary key cannot be NULL...how would I add records to a table without its primary key?

Either CSVI can add new records or not. I just need to know how its supposed to work.

Thanks for clearing this up for me.

Hello,

Okay, I am really confused now, but here goes. I have the option set to MemberNbr (the field name of the Primary Key in the destination table). I have tried with it blank, which it defaults to the same field--that is the only key and the primary key in the table. So far I can't get CSVI to add any new records at all. It Updates just fine.

So to clarify for me on what you have said in previous posts...can you confirm CSVI's intended behavior for me?

Sorry for the confusion but i just wanted to clear again on the MemberNbr being used as primary key because of its value in import file. A primary key field is always an integer with auto increment and the value in your import file for MemberNbr seems to be like a string with - characters. As much as i know primary key field don't support these characters.

With latest version, CSVI will check for a row with the primary key value, if row exists the row will be updated else row will be inserted. This behaviour was not there in our older versions and that is one reason i asked for your CSVI version. I was wrong with my previous reply. I replied keeping the behaviour of old version in mind.

The Primary key in the destination table is MemberNbr. The CSV source file to import has this field. I assume based on the documentation that if a row imported from the csv file has a value in the primary key column, it will do a look up on the destination table. If that record with that key already exists in the Table it will UPDATE the existing record. If a record with that key value cannot be found in the Table it should be ADDED to the destination table.
So to clarify for me on what you have said in previous posts...can you confirm CSVI's intended behavior for me?

Let me make things clear now. A primary key field is always an integer field and with a unique value. CSVI will look for the field value set in import based on field, if the value already exists in database it will update the record else it will insert a new record. There was a small issue with this behaviour in the latest release and the attached patch file will fix it. Please load the patch file using CSVI maintenance menu and then run the import. Ofcourse this wont fix wont help with your MemberNbr field value, it wont insert the value of MemberNbr from import file but the value will be auto incremented.

Again, also as previously posted...the log debug entries do in fact show that it is performing the lookup based on the primary MemberNbr key. And where a record in the table with the primary key is not found CSVI issues an UPDATE statement--how can you update a record based on a WHERE clause that cannot be satisfied? It should be performing an INSERT statement if the key is not already in the destination table as I have asked.
Again, I'm being a bit verbose here because you told me that I have to remove the primary key from the import CSV file to add records. This would not work as prescribed because a primary key cannot be NULL...how would I add records to a table without its primary key?

I hope i cleared out all the confusion. One option to solve your issue is that you add a primary key field called id and set it as auto increment. Set MemberNbr field as a normal field. When you are running import for the first time set the import based on field to be id so new records will be inserted and when you want to do an update use MemberNbr field as import based on so the records gets updated.


patch_custom_2017_10_18.zip

Okay, the patch now causes CSVI to, at least, attempt to insert the records properly, thank you.

Understanding the primary key as defined by your developers better means I won't be able to utilize CSVI to update my custom tables though--not every database uses an integer auto-increment for a primary key I wish that the primary key could be a changed to allow for any unique non-null value include strings some day, then my investment wouldn't be wasted.

EDIT: also, is there a way to purge all rows in a destination table before importing new records? That way I can still update the membership tables from the 3rd party data and account for deletes as well. Thanks!

Hello,

I need to rectify something, the primary key as used by CSVI does not have to be an integer value. However if your primary key is an auto-increment field then it must be an integer otherwise the database can't increment the value. That is the behavior of the database and not CSVI.

Doing an update or an insert, CSVI uses the Joomla system for this and in Joomla if you specify a primary key value, it will do an update, if the primary key field has no value, it will do an insert. This is known as the Active Record approach. Since the record has a value for the primary key it assumes it exists.

Edit: again, the problem is with adding new records not updating existing records based a the primary key. And if I remove the primary key column from my import csv file, how is it going to know what the key is for the records???

Your setup is different from how the rest of Joomla works, you won't find a non-integer primary key field in Joomla because every table uses this as auto-increment value. You cannot auto-increment a string value.

not every database uses an integer auto-increment for a primary key

Not in your case but in Joomla it is the standard.

To make a long story short, you want a different behavior as how the system works now. In your case the primary key isn't really a primary key since it should insert the primary key even if it is already set. I will have a closer look at this and think about it.

is there a way to purge all rows in a destination table before importing new records?

There is no such option as you are the first to ask for it. However I do think it will be good to have such option, so we will build this and post a patch for you to test when it is ready. This should be ready on Monday.

Hope this clarifies a few things.

Roland,

Thanks for the clarification and the patience of you and tharuna.

In years past--long past--I developed nothing but SQL-based solutions and did CRUD type systems exclusively. Even COBOL if anyone is old enough to remember that So I just misunderstood that the custom table import would be based on Joomla!'s implementation of a primary key (integer/auto-increment). I just started to build a custom J! app and my transactional tables have the auto-increment, but the members master table uses an account number alpha based on where I get my data from. I can't change the source so that put me in the position I'm in now.

The purge-before-import feature will address my needs wonderfully, so thank you so much for that special add-on. I realize I purchased CSVI only for the custom table import and the ability to schedule those updates via CRON from the system.

Just an FYI, the patch now uses INSERT instead of UPDATE for new (primary key does not exist) but it inserts "0" (zero) for the column value instead of the data from the CSV source file. Without the patch, and if my destination table is empty, it properly inserts all new records with the unique key value from the CSV file. I'm not sure if that was intended or a bug. I realize with the J! method of an auto-increment you insert "0" to auto increment the table value. Shouldn't it use the key value from the source itself?

2017-10-20	16:47:30	1	[DEBUG]	Finding the matching row

2017-10-20	16:47:30	1	[QUERY]	 SELECT `MemberNbr` FROM `jgs5u_district_roster` WHERE `MemberNbr` = '36FOO-0126'

2017-10-20	16:47:30	1	[DEBUG]	No record found for the given import based on field value

2017-10-20	16:47:30	1	[DEBUG]	Query for Customtable

2017-10-20	16:47:30	1	[QUERY]	 SELECT `MemberNbr` FROM `jgs5u_district_roster` WHERE `MemberNbr` = '36FOO-0126'

2017-10-20	16:47:30	1	[DEBUG]	Executed store

2017-10-20	16:47:30	1	[QUERY]	 INSERT INTO `jgs5u_district_roster` (`DistNbr`,`ZoneNbr`,`ClubName`,`ClubNbr`,`RosterNbr`,`MemberNbr`,`Gender`,`Address1`,`Address2`,`City`,`State`,`PostalCode`,`Country`,`Language`,`Work`,`SponsorNumber`,`CharterMbr`,`LifeMbr`,`CollegeMbr`,`Status`,`Joined`,`FooMbr`,`SpouseMbr`,`HighFiveMbr`,`MeetDesc`,`NbrMbrSponsor`,`FullName`) VALUES  ('36','00','XXXX XXXXXXX #36','36FOO','0126','0','?','999 E. Scott Street','P.O. Box 307','CITY','MO','34730','US','ENGL','(999) 888-3135','36','','N-','','A','10/31/2013','X','','Didn\'T Specify','0','XXXXX XXXXXXXXX','XXXXXX XXX XXXXXXXX')

Thanks again for everyone's help!

Hello,

Please give me some time to re-evaluate how the storing of the data happens. You are not alone in this, if you see this thread.

Ideally, if the record doesn't exist it is inserted and if it does exist it is updated regardless of the primary key set in the table. That is what I am thinking now.

Shouldn't it use the key value from the source itself?

This is where the issue is. If we are inserting an invalid primary key value the storing fails. Users shouldn't be managing the primary key of a database table. If they are, it is kinda useless to have a primary key field set in the database.

Even COBOL if anyone is old enough to remember that

I do know COBOL, although never used it myself

Please give me some time to re-evaluate how the storing of the data happens. You are not alone in this, if you see this thread .

Will do.

Ideally, if the record doesn't exist it is inserted and if it does exist it is updated regardless of the primary key set in the table. That is what I am thinking now.

I think we, and the person in the other thread, would agree with you. In fact CSVI is doing what we expect up to the point of inserting 0 for new records that don't already exist. I mean, from the debug log, it is doing a lookup on the custom table using the primary key found in the source file. And is updating records it finds that already exist in the table, and (with the patch) inserting the data when the key is not found.

The only thing "I" would change is wherever the $pk is inserted, use the data read from the source file instead of assuming that the PK is an auto-incremented integer.

Maybe an option in the import template that would ask if the primary key should be generated automatically or is it included in the import file? Then insert a 0 if True, or use the value read from the file if false.

Hello,

The purge-before-import feature will address my needs wonderfully, so thank you so much for that special add-on. I realize I purchased CSVI only for the custom table import and the ability to schedule those updates via CRON from the system.

The feature for removing records in custom table before import is ready and can be used by loading the attached patch file. Once the patch is updated, you can find a new setting on Options tab as "Empty table before import", Set it to yes so the records in custom table are removed before the import. Let me know on how it goes.

Also, we are still working on the code to fix the custom table import so now when the record does not exist with the value of import based on field it gets inserted else the record gets updated. Will post the patch for the same when it is ready.

patch_csvi_2017_10_23.zip

Hello,
The patch for inserting new records when the record does not exist with import based on field value is ready and is attached with this post. Please load the patch file and give a try with your custom table import. Let us know on how it goes.

patch_csvi_2017_10_24.zip

Thank you! Got it installed and working with it. I have a few preliminary observations that I will be able to provide more detail later when I build a smaller sample set to try and help troubleshoot better, but I'll mention them now in case the details are more obvious to you.

If I use the new Empty table option in the template, the process will not import all of the records. In my production import I have 1896 records in the CSV file, but only 894 are imported. If I empty the table manually before the import.it works perfectly.

If I don't empty the table (and with the new import/update) it will update the 896 records and insert the missing ones on a second pass. I just did this for testing purposes. With the new update allowing the custom unique key I really don't need the purge table before import as often--I can work around this, but just wanted to mention it to you.

It does look like the Skip new items isn't working. I deleted records from the table and set the option Skip new items to NO and the records are still imported. The Overwrite existing works correctly. On the debug output I get messages saying "Item not added because ignore non-existing items enabled" but in fact the new records are being imported.

Hello,

If I use the new Empty table option in the template, the process will not import all of the records. In my production import I have 1896 records in the CSV file, but only 894 are imported. If I empty the table manually before the import.it works perfectly.

This cannot be possible, the code to remove the records is using the same query as manual delete option in mysql so not sure what is happening. Can you send me mysql dump of your custom table with its structure and records to my email at tharuna@csvimproved.com? I will run the import here and check. Will also look into the Skip new items options.