Import .csv file w/o deleting any of the characters

emina
emina Member Posts: 4
edited 2009-03-11 in NAV/Navision Classic Client
I am using dataport to import the csv file.
The problem that I am having is that there are too many (") characters in the file & I am not allow to open the file to remove it. :?

I have tried to set FieldStartDelimiter & FieldEndDelimiter to <None> but it is still showing me errors.
I think the decimal value is the cause of it. eg. "11,020.00" &

If I set the FieldStartDelimiter & FieldEndDelimiter to <"> then I will be having problem with the text field.
I have a text that contains lots of (") character eg. "GAUGE;PRESS;0-60PSI;2-1/2"" FACE;1/4"" NPT".
It will be interpreted as GAUGE;PRESS;0-60PSI;2-1/2.

Is there any other ways to import the whole file without clearing the (") ?

Comments

  • vijayanderson
    vijayanderson Member Posts: 207
    You cannot import file with " it will not allow you, instead remove " and use( ' ) twice i.e ( " ) is typed using shift , instead use use same key twice without using shift. In simple terms instead of using quotes use apostrophe.

    Hope this would solve your problem

    Cheers
    Vijay
  • garak
    garak Member Posts: 3,263
    if the fields start and end with a " (the sign over the 2) and not with two ' (and this ' 2x) use as FieldStartDelimiter & FieldEndDelimiter the ".
    How are the fields separated? With a ;? So the FieldSeparator is ;.

    The file 
    "BlaBlabla";"123,45";"454";"dfgr"

    The setting in the dataport:

    FieldSeparator -> ;
    FieldEndDelimiter -> "
    FieldStartDelimiter -> "
    FileFormat -> Variable

    But not: If the decimal is 12345,34 (the ,34 are the decimal places) and your system (local settings) use as separator for decimal values a dot (.) then the imported decimal is wrong. So you must convert it first.

    And why do you can't use a copy of the file and change this copy? Why is this not allowed?

    Regards
    Do you make it right, it works too!
  • emina
    emina Member Posts: 4
    Vijay,
    It doesn't really works with csv format. Because upon import, the dataport will interpret as separate fields before I can do anything.
    Eg.
    Field 1: "GAUGE;PRESS;0-60PSI;2-1/2"
    Field 2: " FACE;1/4"
    Field 3: " NPT"

    Garak,

    The file comes as it is. The description -> "GAUGE;PRESS;0-60PSI;2-1/2"" FACE;1/4"" NPT" is a field.
    I need to import the whole thing in 1 field.

    The FieldSeparator would be (,) & not (;).

    As for the decimal value, I have come out with a code to remove the comma.
    But the to import the description issue still remains.

    & reason why it is not allowed to do changes, o well, it's one of the customer requirement. :|
  • MBerger
    MBerger Member Posts: 413
    emina wrote:
    reason why it is not allowed to do changes, o well, it's one of the customer requirement. :|
    BUT...you could make a copy and change that. I guess the customer wants the original to stay intact.
  • vijayanderson
    vijayanderson Member Posts: 207
    I have done that earlier and the data has got imported.. try it out.. if doesnt probably i am wrong, if ported then ur problem is solved.

    Cheers
    Vijay
  • john.fo
    john.fo Member Posts: 16
    I tried to test this with a file importing to Table 9 (Countries/Regions) with only code and name. (Don't do this at home except in a testcompany - it makes a mess by creating some unwanted codes..) :)

    Testdata:
    "WW";"Countryname 1"
    "QQ";"Quote "" Name2"

    FieldStartDelimiter and FieldendDelimitor: <">
    FieldSeparator: ;
    and it created a mess, so it did not work with me either.

    It is possible to bypass this by manipulating the file runtime in the OnPreDataport trigger (at that time you know the CurrDataport.Filename) - create a copy of it and manipulate the original etc., but I believe NAV should be able to recognize the combination "; as end of field and treat the "" as text.
    But then again. Some of the fun of coding in C/SIDE is to overcome the limitations of it. :)

Categories