Importing csv file

IoannaK

Hi to all members of the forum. I usually only read your posts but now it's time for me to ask your help.It's my first attempt to run a dataport.
I would like to import in navision a csv file which was in xls form before with type text. I don't use request form. When I run the dataport I get an error message:
'The text 'almost all my fields in csv file' is too long. The text in code fields can have a maximum length of 20 characters'.
I would like to hear from you any advice.
Thank you in advance.

sgg

The Field length error could be due to the following:

1. The import file is NOT in .CSV format
2. There is a field with "," (Comma) as part of the values
3. The Dataport Field Separator is not the same as in the CSV file.

if you are the one that create dthe Dataport and u used default settings for the Feld and record separators, then ensure that you saved the import file in Excel as "Comma Delimited (.CSV)" file. You also ensure that the Excel File has no "," as part of the field values (Else you will have too change your Field separator to another character in the Navision Dataport).

kriki

It is better to use tabulation=TAB-separated files.
Dataport properties FieldStartDelimiter,FieldEndDelimiter=<None>
FieldSeparator=<TAB>.
I always use them since I got tired of the problems with CSV. With Tab-separated files I never have the problems of a ',' in a field.

Kowa

The Openoffice Calc allows you to use TAB instead of ; or , as the field separator when exporting data from the worksheet
http://www.openoffice.org/

IoannaK

Thanks a lot for your quick response!!
@sgg
I have saved the file in csv (MS-DOS) format because otherwise I import non readable characters. Even If I save the file in csv (Comma Delimited) I get the same error message with non readable characters. I am sure that I don't have any comma in my xls file. In the csv file I have all that strange characters.

@kriki
I used Tab as field separator and the other two with value <none> but without a result.

I try to run the dataport before saving it so as to test if everything is ok. Am I doing well? If I run the dataport before saving it I get this error message. If I save and then run I get a snap shot and nothing is imported.

The properties of the dataport are the follow:
ID:50002
Name:C
Caption:C
CaptionML:ENU=C
Import:<Yes>
FileName:C:\Documents and Settings\joank\My Documents\customer.csv
FileFormat:<Variable>
FieldStartDelimiter:<None>
FieldEndDelimiter:<None>
FieldSeparator:<TAB>
RecordSeparator:<<NewLine>>
DataItemSeparator:<<NewLine><NewLine>>
UseReqForm:No
ShowStatus:<Yes>
TransactionType:<UpdateNoLocks>
Permissions:<Undefined>

kriki

IoannaK wrote:

FileName:C:\Documents and Settings\joank\My Documents\customer.csv

Did you save this file as tabseparated?

It is also possible you swapped some fields in the dataport.
It is also possible there are some fields too large to fit into Navision-fields.
In this case you have to use textfields that are long enough as the datafields.
In the "OnBeforeImportRecord()"-trigger, you ALWAYS have to clear the fields used in the the datafields.
In the "OnAfterImportRecord()", you can write the imported data to your tables.

IoannaK

@kriki
Unfortunately when I save the file in txt format, I change the filename extension, I put field separator <TAB>, FieldStartDelimiter <None>, FieldEndDelimiter <None> and I run the dataport nothing happens ](*,)

I think that I am a little bit closer. The csv file contains strange characters inside. Probably and commas even if the xls hasn't.

kriki

The strange characters might be the tabs. If these strange characters only appear between fields, then they are, otherwise something is wrong here.
Can you post 2 or 3 lines (maybe you want to put fake data in those lines)? So we can check it out.

IoannaK

Thank you for your continuous support guys!

1)Once again the properties of the dataport are the follow:
ID:50001
Name:Customer
Caption:Customer
CaptionML:ENU=Customer
Import:<Yes>
FileName:C:\Documents and Settings\joank\My Documents\customer.txt
FileFormat:<Variable>
FieldStartDelimiter:<None>
FieldEndDelimiter:<None>
FieldSeparator:<TAB>
RecordSeparator:<<NewLine>>
DataItemSeparator:<<NewLine><NewLine>>
UseReqForm:<Yes>
ShowStatus:<Yes>
TransactionType:<UpdateNoLocks>
Permissions:<Undefined>

2)I have selected the follow fields :
Enabled SourceExpr StartPos Width
Yes "No." 1 20
Yes Name 22 30
Yes Address 53 30
Yes City 84 30
Yes "Phone No." 115 30
Yes "Gen. Bus. Posting Group" 146 10

3)I compile the dataport before running it.

4)The txt file contains one row ( I save the xls to csv MS-DOS and then the csv to txt)
00001,‰˜¨¨α,isiodou,thiva,2262022594,„‘—’„?ˆ‰Ž“

5)I use Navision 4.00 version on SQL Server.
And the message is the same:
'The text '00001,Καρρά,isiodou,thiva,2262022594,ΕΣΩΤΕΡΙΚΟΥ'
is too long. The text in code fields can have a maximum length of 20 characters'

Dataports may be too difficult for me to handle with, I quess.

bbrown

4)The txt file contains one row ( I save the xls to csv MS-DOS and then the csv to txt)
00001,‰˜¨¨α,isiodou,thiva,2262022594,„‘—’„?ˆ‰Ž“

Save the file as tab-deilimited (*.txt) directly from Excel.

kriki

bbrown wrote:

4)The txt file contains one row ( I save the xls to csv MS-DOS and then the csv to txt)
00001,‰˜¨¨α,isiodou,thiva,2262022594,„‘—’„?ˆ‰Ž“

Save the file as tab-deilimited (*.txt) directly from Excel.

Correct. Because your string is still comma-seperated.
To save it directly as tab-seperated in Excel : File->Save as-> Save as type-field:text(Tab Delimited)(*.txt). Then it should work.

kine

And one tip> Sometime the problems is " (doublequote) character in text. In this case excel write it as """" and the Navision parser has problems with that and take it as end of field...

kriki

kine wrote:

And one tip> Sometime the problems is " (doublequote) character in text. In this case excel write it as """" and the Navision parser has problems with that and take it as end of field...

And this is not a problem if you use FieldStartDelimiter:<None> and FieldEndDelimiter=<None>.
The only thing to do is to change "" into " in the text.

IoannaK

Thanks! I run to follow your tips.
I will let you know.

ehs

In Navision 2.0 entering <TAB> as the field delimiter can cause problems bacause the dataport handles this as an ASCII text.

Try to open Wordpad, press the TAB buttton, copy the result with ctrl-c and paste it to the field delimiter properties in the Navision 2.x dataport with ctrl-v.

You can also see the difference if you use the dataport to export data to a file.

IoannaK

The <TAB> solution is working but I import strange characters (the language is greek). English words are just perfect.

I am thinking to go abroad ](*,)

Savatage

If you search the forum for Import Characters

there are several posts about to import these strange characters

IoannaK

Thanx Savatage for your advice. I have searched only for dataports.

By the way I found I way that works fine for me. It is a little bit bizzare I think.

I save xls to csv and then csv to txt. I import with field separator ;
FieldStartDelimiter:<None> and FieldEndDelimiter=<None>

Probably there is a solution for everyone