Dataport issue ?

gemini_shooter
gemini_shooter Member Posts: 149
edited 2010-05-21 in NAV/Navision Classic Client
Hello,

I have a dataport issue. I have designed a dataport to export the following tables to the CSV excel format.

--Item
--Item Cross Reference
--Default Dimension (For item)
--Item UOM
--Item Vendor

The idea is to export the record from one database and import into another. The dataport works if I export and import into the same database and company.

1. It does not work when I export from one database and import into another database, even though the schema is the same on both databases,
2. But when I export the file from one database and copy it to a new csv file and then import it into the new database it works ???????

Does NAV write a reference to an export files when it exports in some unknown system tables ..... has anyone had this issue before ?

Comments

  • matttrax
    matttrax Member Posts: 2,309
    I've never heard of such a thing. Are you getting an error message? Or does the data just look like it is importing?
    Matt Traxinger - ArcherPoint
  • gemini_shooter
    gemini_shooter Member Posts: 149
    No error message ... it skips lines ????
  • gemini_shooter
    gemini_shooter Member Posts: 149
    here is the issue...

    file exported to CSV format, --> imported into a different database, dataport stops at a blank line

    file exported to CSV format --> imported into the same database, dataport works, does not break at blank line

    file exported to CSV format --> copied to a new CSV file --> imported into a new database, dataport works, does not break at the blank line

    So I am not sure why it works with the same CSV format in one database and not in another, even though both of them are the same
  • Alex_Chow
    Alex_Chow Member Posts: 5,063
    Have you checked the data being exported? My guess is that there are invalid ascii characters on the export file.
    Confessions of a Dynamics NAV Consultant = my blog
    AP Commerce, Inc. = where I work

    Getting Started with Dynamics NAV 2013 Application Development = my book
    Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
  • gemini_shooter
    gemini_shooter Member Posts: 149
    That is what I found ... but now the question is how to I stop that ?????

    Is there a way to make sure these invalid characters don't export or a safer way to export or change the format to txt ????
  • gemini_shooter
    gemini_shooter Member Posts: 149
    This is scary we have lot of data moving to-from different databases, even though the schema's are the same I have to do a manual copy paste into new CSV to avoid CSV ASCII issues ?

    -- Is it better if we changed to TXT or when exporting to CSV is better to use start and end
    delimiters as Blank (<None>) and the field separator as TAB

    -- Do start positions and widths on the data fields make a difference with this issue or I can leave them as 0.
  • rdebath
    rdebath Member Posts: 383
    The problem is usually caused by users doing Copy and paste from excel, this puts newlines and occasionally tabs into the database fields.

    This will replace or remove those unacceptable characters. 
    // Put this in the OnInitDataport
BadCnP[1] := 9;
BadCnP[2] := 10;
BadCnP[3] := 13;
BadCnP[4] := 32;
NewCnP := PADSTR('', STRLEN(BadCnP), ' ');

// Put this in the OnAfterFormatField of each field.
Text := CONVERTSTR(DELCHR(Text, '>', BadCnP), BadCnP, NewCnP);
    If you're using Nav CSV files they also can't cope with double quotes; you'll have to remove or replace them too. Tab separated, what you call a txt file, can cope with quotes.

    Note: The output file uses the OEM character set not the Ansi character set.
    Robert de Bath
    TVision Technology Ltd
  • gemini_shooter
    gemini_shooter Member Posts: 149
    Thank you all ... I got this covered.

    It seems that when you export the file from a dataport, the windows dialiog box does not give u a .CSV option so I was doing "<Filename>.csv"

    NAV does not save it as a true CSV file and adds extra ASCII characters and you can open the file again and may be resize something and close and it will prompt to save again and then windows saves it a true CSV and it gets rid of the extra ASCII characters ???

    If you open the same file just after the export from NAV and after saving it again using windows CSV ... using notepad you can see the difference.

Categories