Sage 100

When trying to import parts into CI_Item in 4.40, if a part number already exists that has a "" (double quote) mark in it, a CSV file source makes VI appear to have TWO double-quotes. How can you tell VI that a double-quote is just that and not try to turn it into two double quotes? The import/update of that part number is failing otherwise.

You can't. You have to use temp fields and sub functions to get it to come in correctly.

Dawn - I had the same issue. I had to use single quotes (two of them) to get it to work properly. The single quotes worked famously.

Single quotes around the double quote?

Will you have the same issue if you import a tab delimited file? I've started using tab files because of the commas and apostrophe issues. Seems to result in a cleaner import for me.

I tried saving an excel file into tab-delimited and it still exported double quotes around the whole thing.

Dawn, what Kathleen is describing is to search and replace the double quote "" with two '' (apostrophes). The user can't tell the difference unless they go to search for it. I just checked v4.5 you STILL can NOT do an ALE search on a string containing double quote which was causing issues with descriptions such as 1.5"" (inches). Not sure why this was such a big issue for Sage to fix.

They want a regular double-quote not two single quotes, as this isn't easy to change (this is part of the part number, not just part of the description.)

BL, using Dawn's example, what would the VI look like to make this happen?

Sorry to jump in and disappear. I have a client who uses pipes and fittings of many different sizes. Therefore all of their Item #'s are like 1/2'' flange, 3/4'' brass fitting and so on. The .csv file kept failing, until I replaced each apostophe with a '' (two single quotes). There was no need to change the part #. The search feature in MAS90 works eaxactly as if we entered them as 1/2"" - in fact I entered a couple manually just to be sure both ways worked the same.

I submitted a ""bug"" report for v4.5. Considering this issue has been festering for nearly two years now, it will probably take another two years before anything is done.

Don't waste your time on the bug report. WAD. Not gonna change. You would need to use the same logic that Scott Brady laid out for importing files with quotation marks when the source fle was required to be a csv file.

BL, do you have that logic handy?

Response back from tech support (As BL predicted) - This is how it is designed and occurs in 4.30 as well all modules. Any changes would be an enhancement request. There are all ready some other ALE changes that people would like to see under out Product Enhancement link. I encourage you submit your request at the link below. Scary how they hide behind the WAD curtain. Imagine the type of vehicle we'd be driving if the car manufacturers said everything they built was designed to roll over when the tire blew at 45 MPH..... At least they could acknowledge that they will work on it. At it stands now, we can VOTE on whether the defect is life threatening!!!! Heaven Help Us All..................

Slow day so I responded to Tech Support with this: So what you are saying is that it worked the way the end user expected it to through v4.2. Then it was broken in v4.3. Since it is working as it was broken (designed) in v4.3 and v4.4 and v4.5, we now have to VOTE to get it fixed? What ever happened to meeting the customer expectations? I can't count the number of clients that use the "" as part of the description (e.g. 1.5"" X 2.0'). Some have resorted to changing the "" to a '' just so the searches would work. Can you explain (beside it being a WAD) why the program will allow you to search for an '? This would seem to be inconsistent! How would you feel if Boeing designed a new jet where the seats on the left hand side of the plane did not recline while the seats on the right had side did? And when you brought this to the attention of the flight attendant, the reply was that it was working as designed and please go to a web site to vote on the ENHANCEMENT!!!!! This is not an enhancement, this is a BUG which should be FIXED!!!!!!!

I must be missing something. The Part number is: 4"" PART. This is a double quote mark after the number 4. When excel exported it, it exported as: ""4"""" PART"" with double quotes around the whole thing, but TWO double quotes after the 4. I tried using VI to import this and it failed, as it saw two double quotes after the 4 and the part number only has one. I then tried replacing the two double quotes with two single quotes and that didn't work.

Okay, I gave up on those suggestions and came up with my own because using two single quotes next to each other did not work. It still thought the item number didn't exist because in MAS 200, the part number had a double-quote, not two single quotes. VI seems to take two single-quotes as just that so I'm not sure what everyone else was doing to make it work. It's times like this that I feel dense.

So what was the answer????

It was mostly manual -- in the Excel file they replaced the double-quote ("") with a character they never use in their part numbers which for them was the carat (^). Then in the VI job I added the item code a second time with a calculation to replace the ^ with a double-quote. SUB({CI_Item.ItemCode$}, ""^"", CHR(34))

I thought there was a write up in the KB (when it's up) the gave the coding on how to do this.

I had that issue and fixed with: SUB(IMP$[3],quo+quo,quo)

this is what I used last time I had this issue: SUB(IMP$[3],quo+quo,quo)

sorry for the double post but the new socialcast for outlook required me to reauth again and I didnt' realize it cached my post.