Hello,

Anyone noticed this issue with Excel 2010?

Open a new excel file, add two columns:-

1. Item
2. Item Description

Keep Item as XYZ
Description as X"YZ (Inch sign :- which we can't ignore)

Save it as .csv file.

If you check it now, the csv has "X""YZ" which will lead to problems when you are using such csv file for your data processing session.

Not yet started with the solution for this. Just wanted to know if people have faced similar problems or not.

Hi,

Yes, this is how Excel will process your "Save as CSV". Its not just comma separated values, but with Excel it is also quotes.

You might want to use another editor to edit the CSV or teach your session to read translate the quotes correctly.

You can also use a macro to clean all the quotes, or quote everything. check this link:
http://www.excelforum.com/excel-general/391024-save-as-csv-with-text-in-quotes.html#post1046395

Note that if the description was originally (in notepad): X"YZ it will open correctly in Excel.

- George

Hi George,

Well, have went through a lot of forums and discussions. But, the point where I am clueless is we can't clean the double quotes. And, well its dynamic in nature i.e. a field may have a single or multiple inch sign (") which will lead to more " in the .csv file which messes the field name completely.

About the "training the session part", well that is the only way out I see and I am onto in already. :cool:

And yes, the example you gave, I guess it's about the cleaning and not handling.

You can write a macro that will clean only the quotes that are in the beginning and end, and replace the double quotes ("") in the middle with 1 quote (").
So the macro is suppose to convert "X""YZ" to X"YZ

So whenever you want to save the CSV, you will run the macro instead of the "save as CSV".

I would prefer the baan way too :)

Yes,

Exactly. Because, user's never love the process changes. So, it would be best to keep it inside the box. And yes, I have had issues with running macros, so better to stick to Baan. :)

Baan Way!

function domain tcmcs.str30 remove_quotes(domain tcmcs.str35 m.field) |Passing fields with the double quotes
{

long m.len,i
domain tcmcs.str35 m.final |#temporary field for collecting clean string

i=1
m.final = " "
m.len = len(m.field)
while m.len > 0
if m.field(i;1) = chr$(34) then
if m.field(i+1;1) = chr$(34) then
m.final = m.final & m.field(i;1)
endif
else
m.final = m.final & m.field(i;1)
endif
m.len = m.len - 1
i = i + 1
endwhile
m.final = trim$(m.final)
m.field = m.final
}

Not sure if its understandable, but just posted if it might help. Worked smoothly for me.

Bhushan,

If you are on the latest tools versions, you can use -

string str.remove$() ( const string string$, long offset, long nchars )
void str.replace() ( const string string$, const string oldstr$, const string newstr$, ref string result$ )
string str.replace$() ( const string string$, const string oldstr$, const string newstr$ )

Hi Prashant,

The problem was, I cannot remove/replace the inch characters. Tried the str.replace$() but was of less help.

The other way is to create XML file and then it will nicely open. In some case quotes are part of description lie representing inches. I normally generate XML and give it is as ,xls extension and it opens nicely.

Arvind

Hi Arvind,

Yes, it is a way. But that would have required a lot of code changes. For now, the function works fine.