Dataport issue ?

gemini_shooter

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 ?

matttrax

I've never heard of such a thing. Are you getting an error message? Or does the data just look like it is importing?

gemini_shooter

No error message ... it skips lines ????

gemini_shooter

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

Have you checked the data being exported? My guess is that there are invalid ascii characters on the export file.

gemini_shooter

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

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

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.

gemini_shooter

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.