Resolved xml import to custom table not working

Hello,
Debug log shows two issues

1. Not going to check if row exists because we have no value for the key field reference_number
2. Cannot add custom table data. Error: Field 'reference_number' doesn't have a default value

It looks like you are importing based on reference_number field and this field has no value. What do you have set in reference_number field? Can you post screenshot of import preview page? Also post template XML and sample of import XML file to check. To get the template XML file from RO CSVI templates list page select the import template and click on Backup templates button. Post the downloaded XML file.

The reference_number field has a value.
Attached are a couple screenshots from the analyzer, csvi_templates... is the template XML, and JobPortalXml3.xml is the import XML file.
Also, NewDb.txt is the destination database of the import.

Hello,
Thank you for the files.

Two things

1. In custom import template on File tab set Use file for configuration to No and in Options tab set Import based on field to reference_number. Save the template.
2. order_start_date is not in the database format so you need to change that to database supported format. To do that use RO CSVI Multireplace rule and custom date format operation like in attached screenshot. Apply this rule on order_start_date.

See attached screenshots for reference. Run import with these changes and check. Let me know on how that goes.

Thank you that worked!
The other question I had was: I would like to read in XML formatting from a URL, but it doesn't have a ".xml" extension, is there a way to disable the check for ".xml" or a way to have the XML copied to a .xml file so this check works?

Hello,

The other question I had was: I would like to read in XML formatting from a URL, but it doesn't have a ".xml" extension, is there a way to disable the check for ".xml" or a way to have the XML copied to a .xml file so this check works?

In import template on File tab set Use file extension field to XML as in attached screenshot and save the template. That would force RO CSVI to use .xml extension. Click on Advanced button in toolbar if you don't see this option in template. Try and let me know on how that goes.

Attached is the screenshot of the file tab, there is no "Use file extension" field.
Just to reiterate, the URL I am using returns XML formatted data, it is not a URL that contains a file with a .xml extension.

The RO CSVI - import returns the following error:
No extension has been found on the uploaded file /home/specodev/public_html/sustainable/tmp/com_csvi/3197235051/JobPortalXml_organizationCode_SUS, now I Do not know what to do with your file. Please check your file if it has a valid extension. Valid extensions are csv, tsv, txt, xml, xls and ods.

Hello,

Attached is the screenshot of the file tab, there is no "Use file extension" field.

Click on Advanced button in toolbar to see the field. This was mentioned in my previous reply as well.

Just to reiterate, the URL I am using returns XML formatted data, it is not a URL that contains a file with a .xml extension.

The RO CSVI - import returns the following error:
No extension has been found on the uploaded file /home/specodev/public_html/sustainable/tmp/com_csvi/3197235051/JobPortalXml_organizationCode_SUS, now I Do not know what to do with your file. Please check your file if it has a valid extension. Valid extensions are csv, tsv, txt, xml, xls and ods.

Try with Use file extension option and if you still have any problem post the import URL so we can check.

Wow, not sure how I skipped over that additional statement.
Yes, thank you that worked!

Is there a plugin that can parse text out of one field and apply it to another field? Or a way to customize an existing plugin?
I would like to perform the following Excel action on a field and apply the result into another field:
=IFERROR(REGEXEXTRACT(R2, "<p>Pay Rate: ([^<]+)</p>" ) , "NULL" )

For example, a field contains multiple HTML elements, one of them being "<p>Pay Rate: $15.00/hr</p>", which I would like to parse out and place in another field. I would leave the field it came from untouched.

Hello,
You can use RO CSVI rules to do this.

1. Create RO CSVI Field copy rule to copy text from one field to another. See attached screenshot. Here Field1 is the field from where text has to be copied. Field2 is field where text is copied. Apply the rule on Field1 field in RO CSVI template fields page.
2. Using RO CSVI Multireplace rule and Find and replace operation create a rule to select text Pay Rate: $15.00/hr using Regular expression. See attached screenshot. Apply this rule on Field2 field in RO CSVI template fields page.

Run import and check. Let me know on how that goes.

The copy rule works, but the find/replace does not.
I created that find/replace rule and applied it to field2 and it didn't have any affect on field2.
I decided to try it on field1 and it did find, but the Replace "$1" does not work. I changed it to Replace "Test $1" and it just added "Test" within the full original string.

Field1:
<p>Job Position: Press Operator</p><p>Location: Woodstock, IL 60098</p><p>Test Pay Rate: $15.00/hr</p><p>Shift: 1st shift, Mon-Fri, 4:30 a.m. to 4:30 p.m.</p>
Field2:
<p>Job Position: Press Operator</p><p>Location: Woodstock, IL 60098</p><p>Pay Rate: $15.00/hr</p><p>Shift: 1st shift, Mon-Fri, 4:30 a.m. to 4:30 p.m.</p>

Hi Scott,

I have altered the regex a little, can you give this one a try:

/<p>Pay Rate: ([^<]+)<\/p>/

In case it doesn't do what you need, post a backup of your template and import file and I will have a further look.

Let us know how it goes.

That didn't work. I think the previous regex was fine, it just isn't working on Field2.
Field1 is coming in from the XML file and going into the database. Field2 is only in the database.
What I am trying to do is copy the Pay Rate html element out of the PortalDescription field and place it into a database field by itself:
Field1(portal_description):
<p>Job Position: Press Operator</p><p>Location: Woodstock, IL 60098</p><p>Pay Rate: $15.00/hr</p><p>Shift: 1st shift, Mon-Fri, 4:30 a.m. to 4:30 p.m.</p>...
Field2(payrate_full):
$15.00/hr

Attached is the template backup and XML import.

Hi Scott,

Field1 is coming in from the XML file and going into the database. Field2 is only in the database.

That is some very important information. This is important because RO CSVI doesn't operate on database fields, only on data passing through the import. As you have posted the import file and your template, it makes things a lot clearer for me.

What I see is that you have the payrate_full field with the replacement rule but there is no XML field linked to this field so nothing happens as there is no data going through that field. However I see that you copy the content of the PortalDescription to the payrate_full field except the PortalDescription is placed after the payrate_rule field. So when RO CSVI processes the payrate_full field, it has no content yet.

There are a few changes needed:

• Move the PortalDescription field above the payrate_full field
  
• Update the regular expression for the payrate_full field. Since you are extracting and not really replacing data, the regex must be different. The new regex is
  

/^(.*?)(<p>Pay Rate: \$\d+\.\d+\+?\s*.*?<\/p>(.*?)$/s

You can see that in my test in the attached image that it is replaced now.

Let me know how it goes.

Yes, I thought having Field2 only in the database may be an issue, thank you!
Your regex is missing a parenthesis after the closing </p>:
/^(.*?)(<p>Pay Rate: \$\d+\.\d+\+?\s*.*?<\/p> )(.*?)$/s
and the Replace now needs to be "$2" instead of "$1".
Actually I only needed the value between the paragraph markers, so I used:
/^(.*?)<p>Pay Rate: (\$\d+\.\d+\+?\s*.*?)<\/p>(.*?)$/s

Hi Scott,

the Replace now needs to be "$2" instead of "$1".

Oh gosh, I completely forgot to mention that.

Actually I only needed the value between the paragraph markers

Yeah, I wasn't sure what exactly you needed but at least it is working now.

All questions are answered now I think or do you have more regarding this topic?