Import and special characters

Hubertus

sorry to bother you again but there seems to be a bug when importing data containing special characters.
Given an attribute containing characters like äöüß and propably any other special characters used in european languages.
When working with these entries everything works fine and they are also stored perfectly correct in a mysql database (on linux).
Now when I export such a BO to a csv file these characters are ok but re-importing them changes all these characters to '?'

aware_support

This should be fixed now starting from build 848

Hubertus

many many thanks for that !

But I'm afraid that I'm having another "problem" with the import functionality. I should import data coming from a M$SQL server. Therefore I created a task on that server that writes the desired data to a csv file from time to time. I've said that strings should be enclosed in ". The problem is that the M$SQL Import/Export Tool also quotes the field names on the first line !
Would it be possible to accept field names in a csv file not only in the form BO.Attribute but also "BO.Attribute" ? :roll:

Thanks a lot for your efforts,
Hubertus

ab042

If I could suggest, two additional items on the IMPORT if you get time.

#1) Is it possible to remove the Case Sensitive requirement from the IMPORT field name. We never create fields with the same name different case and I'm not sure why someone would. Does AwareIM the underlying DB even support it?

#2) If the import file has too much data in any one field just import the size we have defined in our attribute. Please do not stop the import and display the "truncation: Data too long for column" error message.

Please note if your trying to importing thousands of records and the export system didn't allow you to truncate the length and the import system won't take the length then you are forced to manually breakdown the file (because excel can't deal with a file this size) and then load each file in excel and go thru and manually resize any fields that are too long. To add to this fun the error message in AwareIM gives the field name correctly but always shows the message as if the problem is in Row 1 and even when its not in Row 1. (ie: "Data too long for column 'RegLicName' at row 1")

Maybe for some users an option to truncate or not would be necessary, but in our case we don't care. Just truncate it.

This is not an urgent issue but would be nice. Thanks

aware_support

Hubertus wrote:
Would it be possible to accept field names in a csv file not only in the form BO.Attribute but also "BO.Attribute" ?
I am not sure I understand the entire message, but basically do you just want Aware IM to accept column names in the first row with quotation marks, like "BO.Attribute"? If so, I believe we can do it.

ab042 wrote:
Is it possible to remove the Case Sensitive requirement from the IMPORT field name. We never create fields with the same name different case and I'm not sure why someone would.
I'm not sure I understand this (maybe it's my unlucky day). What IMPORT field name are you talking about here? Do you mean the names of the attributes of business objects?

Maybe for some users an option to truncate or not would be necessary, but in our case we don't care. Just truncate it.
This is the error message given by the underlying database. I suppose we could check for truncation ourselves before giving the data to the database, put the message into the log and carry on. Not sure if everyone will be happy with this solution. We need to think about this one.

Hubertus

do you just want Aware IM to accept column names in the first row with quotation marks, like "BO.Attribute"? If so, I believe we can do it.

This is correct - I only want to be able to use the import functionality with quoted Fieldnames like "Customer.Matchcode"

Thanks,
Hubertus

aware_support

Headers in CSV files now accept quotation marks starting from build 850.

Hubertus

Thanks a lot for that !

ab042

I believe I've discovered another IMPORT BUG.

If your doing an import and it has numeric fields with space(s) before the number they appear to be importing as BLANK.
For example: "201234","06/01/06"," 1859"," 1","99.10"," 7.00"

The 1859, 1 and 7.00 dollars in the above line imported as blank. Note: The "1" above had three blanks before the 1 but doesn't show above.

Since we have no control over the export programs. This example being EXCEL it makes for a lot of bad imports.

Can you please fix this for the next release. Thank you

aware_support

This is fixed starting from build 857

ab042

THANK YOU. That did take care of the numberic field issue.

I believe I may have found another IMPORT issue. If you create an export file and select "Export ID's of the instances" then it creates the EXPORT file just fine.

And if I understand it correctly. The Export ID is used to update existing records in a file on import. But what if the ID is non-existing, shouldn't it create the record?

For example: We want to export TWO files that have relationships to each other and want the relationship rebuilt on import. We can't figure out how to do it. If we export the file with Export ID's then we can't import the file and if we export leaving Export ID's blank then how does the relationship file associate itself?

aware_support2

> But what if the ID is non-existing, shouldn't it create the record?
Attribute ID is managed internally by the system. You should include ID values in the import file ONLY if they were originally exported from the system, so that the system will update exactly the same records. You cannot supply arbitrary ID values on import.

Instead of attribute ID you can use any other attribute that uniquely identifies an instance, for example CustomerNumber (you will need to mark it with * as described in the User Guide). Then the logic will be as you described: if CustomerNumber is found the instance will be updated, otherwise a new instance will be created.

> We want to export TWO files that have relationships to each other and want the relationship rebuilt on import.
If you mean re-importing existing data, there is no need for re-building relationships because they are preserved in the system. You can simply import both files with (previously exported) ID values and values of other changed attributes, but not the relationship attributes. The system will find existing instances and update the changed attribute values.

If you mean importing new data, attributes in the second file should reference values imported in the first file. For example, if import file for Customer includes value "12345" for Customer.CustomerNumber, then import file for Order may include value "12345" for Order.Customer.CustomerNumber. In this example Customer file should be imported first.

ab042

I believe I understand the response and I did read the section on marking with an "*" but the problem I have is with a simple but important relationships and there is nothing to link it to.

I have one BO called Division and two attributes in it.
Division and Description of Division.

The other BO called Customer and in that I have an attribute for Division.

I've done the import a few different times and it doesn't the Division in the Customer file is always blank. Unless I'm missing something there is no way other than the ID to link the correct Division to the Customer.

aware_support2

> I've done the import a few different times and it doesn't the Division in the Customer file is always blank.
You have full control over the values you put in an import file, so I am not quite sure why there would be blank values in that file. Your import file for Division could look like this:

Division.Name, Divison.Description
"Marketing", "Some description for the division"

You import file for Customer could look like this:

Customer.Name, Customer.Division.Name
"John Brown", "Marketing"

You would first import the file for Division, then for the Customer.

ab042

The EXPORT file is from AwareIM and unless I select "Export Relationships" it does look like it places anything to do with the Division in the Customer export File.

If I select "Export Relationships" then it does place a reference to the Division in the file something like "Division#3431" which must be the Instance ID of the record in the Division file, which is what started this line of questions.

Maybe the export should routine in AwareIM should be programmed to place the actual value of the link in the export file if "Export Relationships" is not checked. Then the file would be as you described in your previous post and we could do with it what we need.

aware_support2

So the question is about export - not import? What do you intend to with exported data and why do you need relationships information there? If you are going to change the data and re-import it back into the system, you do not need to re-import relationships as described earlier. Otherwise, there are alternatives to the export feature if you want to extract data from the system in a specific way.

Relationships are written into a separate file because a single exported instance may be related to several others, which are represented by separate entries in the relationship file.

ab042

My original question was on the import because I was unable to IMPORT and get the relationships. But after your post maybe if the EXPORT was changed then it would put the correct data in the CSV file and would solve the import relationship issue I originally raised.

->Your import file for Division could look like this:
Division.Name, Division.Description
"Marketing", "Some description for the division"

You import file for Customer could look like this:
Customer.Name, Customer.Division.Name
"John Brown", "Marketing"

I'm trying to take TWO files as in your example, with Division in the Customer file as an attribute to the Division file, just like you have and get AwareIM to export it and then re-import it into a new BO with the Division in-place.

A very basic backup routine if you will. AwareIM should be able to export to a CSV file and import the same CSV and get the database as it was without a user having to re-key data.

I believe if the user Doesn't Select "Export relationships" on the export and you simple place a column in the file with the VALUE of the relationship (not a link to it) this will create a file like you've shown and will solve the issue. The issue then becomes what fields from the related file do you need or want in the export file.

I'm just trying to get data out of the system in a CSV file and then imported back in without a user having to figure out how to recreate the broken links.

Of course I'm open to all suggestions and corrections.

aware_support2

> I'm just trying to get data out of the system in a CSV file and then imported back in without a user having to figure out how to recreate the broken links.
As far as relationships are concerned, the export and import are not symmetrical, nor are they supposed to be. From the practical point of view, if you need to re-import existing data, you do not need to re-created relationships because they are preserved in the system (as discussed earlier).

As for backup, this can be done on the database level. The new "Export/import business space" feature will allow a backup on the business space level as well as moving business space data into a different database.