Problem with .csv file in XMLport

poppins
poppins Member Posts: 647
edited 2013-07-29 in NAV Three Tier
Hi everyone,

I am trying to import some data from a .csv file into a table I created using an XMLport.
I setup the XMLport as following: 
Format : Variable Text
FieldDelimiter : <">
FieldSeparator : <,>
RecordSeparator : <<NewLine>>

When running it, I get the result in the following image:

What shall I do?
csv.jpg 62.6K

Answers

  • Kishorm
    Kishorm Member Posts: 921
    Check the import file, a missing double quote at the start of the line would probably cause the issue you are getting.
  • poppins
    poppins Member Posts: 647
    Kishorm wrote:
    Check the import file, a missing double quote at the start of the line would probably cause the issue you are getting.
    I checked the file, indeed there was no double quote for the first field, I added it but when running I get the following error: 
    You cannot enter 'Bicyclette' in Decimal.
  • Kishorm
    Kishorm Member Posts: 921
    You'll need to show how the xmlport is setup (fields being imported) and what is in the data file.
  • poppins
    poppins Member Posts: 647
    Kishorm wrote:
    You'll need to show how the xmlport is setup (fields being imported) and what is in the data file.
    Here are the .csv file and the XMLport structure:

  • Kishorm
    Kishorm Member Posts: 921
    I would expect an error in the unit price field as it contains a strange/invalid character in it, but I wouldn't expect he error message you are getting. Try correcting the data for the unit price and see if it works.
  • Kishorm
    Kishorm Member Posts: 921
    Actually, I've just realised that the data you have shown is in Excel. Try opening the file in notepad.exe - you'll see that it is different with extra double quotes around each field and also at the start and end of each line.

    Best thing is to create a new data file in notepad - do this by highlighting and the copying (ctrl+c) the data in Excel and then pasting into notepad. Also, make sure the unit price does not have the strange character in it - it should work then.
  • poppins
    poppins Member Posts: 647
    Kishorm wrote:
    I would expect an error in the unit price field as it contains a strange/invalid character in it, but I wouldn't expect he error message you are getting. Try correcting the data for the unit price and see if it works.
    I deleted the invalid character and run the XMLport again,
    now I am getting the following error: 
    The length of the string is 44, but it must be less than or equal to 20 characters. Value: """1000"",""Bicyclette"",""F4"",""4000,00"""
  • Kishorm
    Kishorm Member Posts: 921
    Please refer to previous post
  • poppins
    poppins Member Posts: 647
    Kishorm wrote:
    Please refer to previous post
    I created a text file with the two following lines: 
    "1000","Bicyclette","F4","4000,00"
"1001","Bicyclette de randonnee","F5","4000,00"
    I tried to run the XMLport and got the following error: 
    The length of the string is 34, but it must be less than or equal to 20 characters. Value: "1000","Bicyclette","F4","4000,00"
  • poppins
    poppins Member Posts: 647
    I figured it out :)
    Infact, I replaced the settings of the XMLport as following: 
    Format : Variable Text
FieldDelimiter : "
FieldSeparator : ,
RecordSeparator : <NewLine>
    and it did worked...
    I've tried that before but it didn't work since I was using an Excel file instead of a Text file...
    Thank you for pointing that out to me :mrgreen:

Categories