Options

Delimiter in Text Field

aztiguen24aztiguen24 Member Posts: 8
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..

Comments

  • Options
    krikikriki Member, Moderator Posts: 9,096
    [Topic moved from 'NAV Tips & Tricks' forum to 'NAV/Navision Classic Client' forum]

    And for import/export and how (dataport,xmlport,other)?
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Options
    aztiguen24aztiguen24 Member Posts: 8
    anyone can help me about this.....plsssssssssssss [-o<
  • Options
    DenSterDenSter Member Posts: 8,304
    Clearly you need to choose a different delimiter
  • Options
    aztiguen24aztiguen24 Member Posts: 8
    but, that's the current delimiter of the customer..and they don't want to change it to any..
  • Options
    dansdans Member Posts: 148
    I'm a bit loss on what you're trying to do. Export ? Import ?
    Microsoft Certified IT Professional for Microsoft Dynamics NAV

    Just a happy frood who knows where his towel is
  • Options
    aztiguen24aztiguen24 Member Posts: 8
    umm..Import, co'z I'm creating a report..
  • Options
    dansdans Member Posts: 148
    how come the 2nd value has quotation, but not the 4th ?

    "3335"
    1234567
    Microsoft Certified IT Professional for Microsoft Dynamics NAV

    Just a happy frood who knows where his towel is
  • Options
    aztiguen24aztiguen24 Member Posts: 8
    Sorry, typo error... actually it have quotation..
  • Options
    DenSterDenSter Member Posts: 8,304
    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....
  • Options
    krikikriki Member, Moderator Posts: 9,096
    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 ";".
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Options
    SogSog Member Posts: 1,023
    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.
    |Pressing F1 is so much faster than opening your browser|
    |To-Increase|
  • Options
    DenSterDenSter Member Posts: 8,304
    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?
  • Options
    SavatageSavatage Member Posts: 7,142
    edited 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.
  • Options
    davmac1davmac1 Member Posts: 1,283
    Or you could just write code to read and process the file as a text file.
  • Options
    aztiguen24aztiguen24 Member Posts: 8
    Thanks for the answers that you've given to me...

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


    Untitled.jpg

    Again, hope u'll help with this..
  • Options
    AlbertvhAlbertvh Member Posts: 516
    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.
  • Options
    aztiguen24aztiguen24 Member Posts: 8
    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
  • Options
    AlbertvhAlbertvh Member Posts: 516
    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;
    
Sign In or Register to comment.