Delimiter in Text Field

aztiguen24 · 2012-04-04

Hi,

I have a problem in the delimiter on m y text field...

for example the value in my field is "123,1235","3335","2351,23",1234567 include the double quotation mark..

what i want is the result is going to be like this

var[1] = "123,1235"

var[2] = "3335"

var[3] = "2351,23"

var[4] = 1234567

hope someone will help me..

kriki · 2012-04-04

[Topic moved from 'NAV Tips & Tricks' forum to 'NAV/Navision Classic Client' forum]

And for import/export and how (dataport,xmlport,other)?

aztiguen24 · 2012-04-10

anyone can help me about this.....plsssssssssssss [-o<

DenSter · 2012-04-10

Clearly you need to choose a different delimiter

aztiguen24 · 2012-04-10

but, that's the current delimiter of the customer..and they don't want to change it to any..

dans · 2012-04-10

I'm a bit loss on what you're trying to do. Export ? Import ?

aztiguen24 · 2012-04-10

umm..Import, co'z I'm creating a report..

dans · 2012-04-10

how come the 2nd value has quotation, but not the 4th ?

"3335"
1234567

aztiguen24 · 2012-04-10

Sorry, typo error... actually it have quotation..

DenSter · 2012-04-10

Dataports won't work like that, sorry. If you use " as field start and end delimiter, and , as field separator, dataports always get confused when field values contain a , it sucks but that's reality. For some reason, even though you put the , between " and " dataports still interpret the , as a field separator and it will fail.

The only reliable ones that I've found are <TAB> as field separator and <None> as field start and end delimiters.

I REALLY dislike dataports by the way....

kriki · 2012-04-10

DenSter wrote:

The only reliable ones that I've found are <TAB> as field separator and <None> as field start and end delimiters.

And even <TAB>'s are not 100% reliable. Sometimes a <TAB> creeps into the data to be imported. But at least it is not a problem of the import but the export must clean up its data to be exported.
But at least it is a lot better than "," or ";".

Sog · 2012-04-10

DenSter wrote:

The only reliable ones that I've found are <TAB> as field separator and <None> as field start and end delimiters.

I've always used ; as delimiter and [ ] as start and end delimiters. Works like a charm almost every time. (The only time it shouldn't work is if the data contains [ or ] which has never happened in my cases.

DenSter · 2012-04-10

I've found any character that I've attempted to use as a field separator as part of actual field values. Regardless of the field start and end delimiters, if the field separator is part of the value, it gets confused and fails. It makes no sense because you would expect the separator and delimiters to work together, but I've found that is simply not reliable.

Did I mention that I REALLY dislike dataports?

Savatage · 2012-04-10

aztiguen24 wrote:

umm..Import, co'z I'm creating a report..

So this is an Import report & not a dataport, correct?
and you are being provided the data by someone else, correct?
do you want to retain the commas? If not delete them once in excel and resave the file as text.
But I would suggest "importing" not just opening in excel & then saving it as TAB seperated.

davmac1 · 2012-04-10

Or you could just write code to read and process the file as a text file.

aztiguen24 · 2012-04-12

Thanks for the answers that you've given to me...

Below is the screenshot that i want to be in the report..

Again, hope u'll help with this..

Albertvh · 2012-04-12

Hi
You could try something like this

Pos := STRPOS(Text,'"');
WHILE Pos > 0 DO BEGIN
  i := i + 1;
  Var[i] := COPYSTR('"' + Text,1,Pos);
  Text := COPYSTR(Text,STRPOS(Text,'"') + 3);
  Pos := STRPOS(Text,'"');
END;

Where Text is your string that you want to break down.
Hope this helps.

aztiguen24 · 2012-04-12

Thanks Albertvh,

I tried your code and it works... but the output is like this:

array[1] =
array[2] = "
array[3] = "3456
array[4] = "123457
array[5] = "87648391234
array[6] = "4444

instead of:

array[1] = 123456
array[2] = 123457
array[3] = 87648391234
array[4] = 44444

Albertvh · 2012-04-12

What I gave you was just a suggestion.
Try this

Text := COPYSTR(Text,2);
Pos := STRPOS(Text,'"');
WHILE (Pos > 0) DO BEGIN
  i := i + 1;
  Approv[i] := COPYSTR('"' + Text,1,STRPOS(Text,'"') + 1);
  Text := COPYSTR(Text,STRPOS(Text,'"') + 3);
  Pos := STRPOS(Text,'"');
END;

Delimiter in Text Field

Comments

Categories