'NORTH ' <> 'NORTH' Import issues CSV

[Jaymer]

Title should read

Code: Select all

'NORTH      ' <> 'NORTH'     Import issues CSV

but php is stripping my whitespace.


If you're making CSV from Excel/Access, for example, its going to "properly" make the CSV.
But when I make an export from Magic, my data might be

Code: Select all

1,NORTH     ,Tom
2,SOUTH     ,Bill

**** This is acceptable as long as I know I don't have quotes or commas inside my data. Its basically a fixed length export which almost any CSV import program will handle.

If it was "proper" CSV, it would be

Code: Select all

1,"NORTH","Tom"
2,"SOUTH","Bill"

I found out the hard way that Aware doesn't trim the string in the 1st example.
So I banged my head for an hour trying to find out why a simple test wasn't running the correct code:
If BO.Region='NORTH' THEN something <--- will fail using my data import. Mac version.
If you edit that field you will see its padded with 5 spaces. Removing them makes my test evaluate correctly.

Yes, if I was doing a "final" import after lots of testing, I could load my data into/out of Excel to make it Proper then I wouldn't have the issue.


PS _ Just found this in my logging output. I guess I have to go modify all my export routines to get these fields trimmed. Look at the last line.

Screen Shot 2016-03-24 at 11.24.52 PM.png (22.16 KiB) Viewed 6739 times

[BenHayat]

I suggest, best thing to do is to add a rule to the receiving BO that trims every attribute.

[Jaymer]

Initial thoughts are that what you suggest is a good idea.
1) its faster [adding trim rules] than modifying a bunch of magic programs to make one long export field with TRIMS, etc.
2) that strategy would also work if you have a fixed length export given to you with no control over that format
3) If you have a one-off, you could always load it into excel and have it produce the proper CSV

BUT

4) If I add a bunch of rule lines to trim all the text fields, do i have to remove that rule(s) for normal operation so all that doesn't get executed each time?
5) and if so, and I have to trash data and re-import (or just import more data next month), I have to add them all back in to scrub the data ... again?
6) there doesn't seem to be a way to duplicate a BO, so if I have 50 fields, I have to manually create another BO with the same 50 fields... an intricate process given that UI. I thought I may be able to import into a "dup" BO, scrub the data there and move it into the production BO.

[BenHayat]

On # 4, if you could always check of the BO IS NEW to trim the extra space. Or you could have the trimming as part of your process and not rule.

On # 6, yes you can duplicate a BO. On the left panel tree, right click on a BO, do a "copy" and right click on "BusinessObject" and do a "Paste". This will duplicate your BO plus a 1 after it, which you can rename.

[Jaymer]

On # 6, yes you can duplicate a BO. On the left panel tree, right click on a BO, do a "copy" and right click on "BusinessObject" and do a "Paste". This will duplicate your BO plus a 1 after it, which you can rename.

I knew that !!!

[Jaymer]

On # 4, if you could always check of the BO IS NEW to trim the extra space. Or you could have the trimming as part of your process and not rule.

SO I would change my import statement to be "in batches of 1" and then
have a bunch of assignment statements after that for the string fields?

[BenHayat]

I knew that !!!

You had just said:

6) there doesn't seem to be a way to duplicate a BO,

And after I told you how to do that, you say "You knew that?"

[Jaymer]

You had just said:

6) there doesn't seem to be a way to duplicate a BO,

And after I told you how to do that, you say "You knew that?"

I claim "It was the middle of the night in my time zone" for my error