Import csv into NAV via dataport with data contain " , char

kiyac

I will making use of dataport to import csv format sales transaction into NAV as sales order. It seems that there is field shifting if data source file (csv) with data char like " or ,
(Note, customer only provide csv format to us)

CSV format
"14""LCD","18/F, Victor's Buliding,","23, Java Street,"

What should be the setting in dataport e,g, FieldStartDelimiter, FieldEndDelimiter, FieldSeparator,... in order to have following result after import.

Item description =14"LCD
customer address line 1 =18/F, Victor's Building,
customer address line 2 =23, Java Street,

Thanks for sharing.

garak

normally i would say StartEnddelimiter is " and Fieldseparator is ,.
But here in you scenario the Item Description contains also a " before the LCD (2x).
So, i'm sure NAV think it's a new field (i've not test it).
So, ask your customer if he can change the FieldStart/EndDelimiter for example to ° or ^ or | (use a sign that is not in the field as description)
or use as FieldSeparator a sign that is not in the Fielddescription. so you doesn't need the FieldStart/EndDelimiter and in NAV you set this in the property to None.

Regards

garak

if your customer use excel for creating the csv file he can't say which Fieldseperator is to use. Excel use ever comma or ; (; in german version).
If you import a csv file into excel, you have the option to "say" wich sign is the delimiter, but not on export (Save as) per default.

But with my little macro you can also define which Delimiter excel should use ;-)

Sub SaveCSV2()

Dim Area As Object, Line As Object, Cell As Object
Dim strTemp As String
Dim strFileName As String
Dim strDelimiter As String
Dim strPath As String

strPath = ActiveWorkbook.FullName
strPath = Replace(strPath, ".xls", ".csv")

strFileName = InputBox("Whats the name of the csv file (incl. path)?", "CSV-Export by garak, use it, its free", strPath)
If strFileName = "" Then Exit Sub

strDelimiter = InputBox("Which field delimiter?", "CSV-Export by garak, use it, its free", "|")
If strDelimiter = "" Then Exit Sub

Set Area = ActiveSheet.UsedRange

Open strFileName For Output As #1

For Each Line In Area.Rows
For Each Cell In Line.Cells
If InStr(1, Cell.Text, strDelimiter) > 0 Then
'Cells, that contains the delimiter we set before and after a "
strTemp = strTemp & """" & CStr(Cell.Text) & """" & strDelimiter
Else
strTemp = strTemp & CStr(Cell.Text) & strDelimiter
End If
Next
If Right(strTemp, 1) = strDelimiter Then strTemp = Left(strTemp, Len(strTemp) - 1)
Print #1, strTemp
strTemp = ""
Next

Close #1
Set Area = Nothing
MsgBox "File is saved" & vbCrLf & strFileName

End Sub

No guarantee that it works in your excel version.

Regards

ritesh.singh

Is it compulsory to use dataport? Try using Mapping for uploading data..

David_Singleton

My recommendation would be to find a program that converts CSV to XML, there are hundreds of the out there, most of them configurable and script-able, so the conversion would be automatic. Then use an XML port to import to Navision.

In all honestly, with the exception off one of "quick jobs" I would never use a DataPort.

idiot

If I find that the data contains comma or quotation marks I would change the delimiter to <TAB>.

lubost

Hi,

try open CSV file in Excel, change all occurences of " to ^ (this is only example), import records into NAV and then change all occurences of ^ back to ".

garak

.... or .... open the csv with excel, start the makro (see myecond post) and use a other field delimitier.

kiyac

Thanks for your valuable idea.

Requisite on the import :
1. Customer can only provide csv to me (as their souce is download from ebay)
2. I prefer not to open the csv file for further editing as it may contain many eurpoean language, if I open it, some european language become weird.

Savatage

If this ebay store would like to run smoothly for years to come, I would suggest fixing the descriptions.
Ebay provides plent of space for descriptions.
14" instead of 14inch IMHO was just laziness.
Our descriptions were just like that until I stated to the people who enter our descriptions that they should NOT use certain characters (quotes, commas and asterisks being some of them).
At the time we didn't do any importing or exporting but I knew 1 day it would bite us if we didn't keep our data clean.

Sounds like a lot of work but in the end I believe it would be worth the effort.

themave

Savatage wrote:

If this ebay store would like to run smoothly for years to come, I would suggest fixing the descriptions.
Ebay provides plent of space for descriptions.
14" instead of 14inch IMHO was just laziness.
Our descriptions were just like that until I stated to the people who enter our descriptions that they should NOT use certain characters (quotes, commas and asterisks being some of them).
At the time we didn't do any importing or exporting but I knew 1 day it would bite us if we didn't keep our data clean.

Sounds like a lot of work but in the end I believe it would be worth the effort.

Another vote for clean up the data. Say you get your dataport to work, by changing the character to something else. what happens when that character gets entered in an ebay description. If you don't clean up the data and set some rules for the future, you will always be programming a fix, it will never end.
just my opinion anyways

DenSter

There is no need to clean up data just because there are " and , characters in there. The limitation is entirely in the dataport object. Set it up properly and it will import the data just fine.

Open the CSV with Excel, and export the data to a flat text file, using the TAB character as the field separator, and no field start and end delimiters. Set your field separator as <TAB> and field start and end delomiters to <None>, and you're all set to import the flat text. That is the only reliable way that I have been able to get dataports to work properly.

For some reason dataports simply do not work with " as field start and end, and commas as field separator. When a dataport comes in contact with a " it thinks it is the end of the field, even when there is no , to separate the fields. This has never worked properly.

DenSter

kiyac wrote:

Requisite on the import :
1. Customer can only provide csv to me (as their souce is download from ebay)
2. I prefer not to open the csv file for further editing as it may contain many eurpoean language, if I open it, some european language become weird.

Like I said open the CVS in Excel, and export the data to text file with Tab as field separator, no field start and end delimitation. If you are going to do work for a European customer, then you will need to install the proper language on your computer.

kiyac

Hi DenSter,

May I know what language pack I need to install in order to display common european lanugage properly in NAV e.g french, Italian, German, Spainish,.....

Locale : English (United Kingdom)

Data :spain España
After import via dataport : spain Espa±a (become weird char)
If I use cut and paste : spain España (normal)

Why???

dspasov

Hello,
If you receive the CSV file directly from the customer(Not creating it from Excel) than there is one very simple solution:
In Dataport-Properties change the values in FieldStartDelimiter and FieldEndDelimiter wich are <"> with <None>.

That`s all you need!

Savatage

dspasov wrote:

Hello,
If you receive the CSV file directly from the customer(Not creating it from Excel) than there is one very simple solution:
In Dataport-Properties change the values in FieldStartDelimiter and FieldEndDelimiter wich are <"> with <None>.

That`s all you need!

How will it know where the start & end of a field is?

the dataport will only rely now on the Field Seperator of comma (in this case)
his example of "23, Java Street," will cause a problem.

With his data containing Commas & quotes a distinctive Start & End Delimiter would be solve the problem.
But as Denster said you could open it in excel & resave it forcing tab seperators.
As long as they do not mind adding another step

garak

did no one test the excel macro? Thats all what he need. Open the File in excel, run the macro import it with dataport in NAV ....
where is there the problem?

DenSter

You don't need a macro to export data from excel with user defined delimiters, that capability comes with Excel.

garak

yes, but only TAB (text), Space (formated text) or Semikolon or Comma (CSV). Or is there per default a other way?

DenSter

Years ago I spent two days writing a macro just like yours, because we wanted to be "flexible" and be able to select any character. We then figured out at some point that using no field start and end delimiters, and a TAB character to separate the fields always works. There's no need for the macro, because standard Excel will give you enough flexibility.

john.fo

I have solved this problem by changing the file runtime in the OnPreDataPortTrigger.
Then you will not have to leave NAV for manipulating the file in Excel. Example where I had to remove binary zeroes from a .csv file (I made this from memory so syntax errors could occur):

ch - char
i - integer

[b]OnPreDataPort[/b]
OriginalFile.TEXTMODE(FALSE);
OriginalFile.OPEN(CurrDataPort.FILENAME);
WorkFile.TEXTMODE(FALSE);
WorkFile.WRITEMODE(TRUE);
WorkFile.CREATE(CurrDataPort.FILENAME+'1');

FOR i := 1 TO OriginalFile.LEN DO BEGIN
  OriginalFile.READ(ch);
  if ch <> 0 then
    WorkFile.WRITE(ch)     
END;

OriginalFile.CLOSE;
WorkFile.CLOSE
ERASE(CurrDataPort.FILENAME);
RENAME(CurrDataPort.FILENAME+'1', CurrDataPort.FILENAME);

You could of course also save the original file, but I have not done that in this example