Hi,
I would like to know does anybody have the have the experience using excel format to import into navision using dataport without massaging the raw file in xls format?
Usually I would use csv or txt format however this customer insist on using excel format.
i have never done import from excel format before. I did try out jus now, but doesn’t seems to work.
Help. [:(]
Hi Lim,
You can’t import the xls-format through a dataport. (if we think of the “ordinary” way to use a dataport)
You don’t mention wich version you’re on, but if you’re on NAV 4.0 or newer, you should have the Excel-buffer table avaiable (ID = 370)
Of course you also have to have acces to it in the license.
You can take a look at Report 81 - Import Budget from Excel
This can give you an idea of how it can be done.
Basically you’ll need to use 2 functions in table 370
OpenBook: This function opens the Excel-workbook, and finds a specific sheet. The function takes 2 parameters: Filename and Sheetname
ReadSheet: This function reads the sheet defined in OpenBook cell by cell, and creates a record in table 370 for each cell it finds with values in it.
Now all you have to do is to loop through the records in Table 370, and do what ever you need with it in Navision.
DataPort wouldn’t read .xls (and .xlsm) files.
But why can’t you simply “save as CSV (MS-DOS)” your Excel file just before feeding it to DataPort? Its a common practice to prepare data in Excel - it is of course much easier as trying to key in csv format in Notepad - and then a simple “save as” does the job.
And, the MS-DOS part in save as dialog format selection IS important - there is an option without it, simply CSV, this will end up in Unicode and will generate garbage characters in NAV in languages other than English (languages using letters with diacritical marks)
Alexander: Thanks for the suggestion. I am using version 5 sp1. I am aware of method that you have suggested.
However, the dataport has alread been build but i build it to accept csv format. Suddenly the customer change their mind & they only will provide excel format & insisted on it.
I did try to persuade them excel format is not really possible but somehow, my customer got some information that can easily be done.
Since i not really experience enough, i really wonder whether it is even possible.
Excel format is fine, you simply use the Save As option in Excel, and save it as a CSV file. That is how most data gets into Nav, (almost) everyone still uses Excel to organise and cleanse their data.
Andrew
If you can’t use the approach that Modris Ivans suggested, i’m afraid that the only way is to “reinvent the wheel”.
It’s not that complicated to create an import from Excel, so that far they are right, but if you (and your customer) already have the same import-func based on csv-format, it will always be easier (and cheaper to your customer) to use the existing func.
I think you should confront your customer with the consequenses of their demands, and ask them again if they still insist.
Thanks all for your suggestions & solutions.
Conclusion is, it is impossible to use dataport to import excel format.
I guess I really should confront them again.
Big possibility that I would need to rebuild the whole thing using excel buffer instead of dataport. [:|]
Hi,
I have exported the data in excel format using dataport,
just at the time u r choosing the filename and location you have to specify the extension of the filename as “.xls”,
and the “fieldstartdelimiter” and “fieldenddelimiter” as “none” and the “fieldseperator” as “TAB”.
similarly u can also import the data from excel file using the same format.
sorry if my way of exporting is wrong.
Hi Lim,
You’re supposed to verify the reply which either gave you the solution or at least helped you so much that you found the solution. You had verified your own reply. I have removed this verification. Please verify the right reply.
Thanks! [:)]
Hello Alexander,
I’ve tryed your proposal in a sample report (I have no complete developer license).
The code is this:
Report - OnInitReport()
myXls.OpenBook(‘C:\myXls.xls’,‘Hoja1’);
myXls.ReadSheet;
And I only can get this error from the ReadSheet Function:
ReadSheet()
Window.OPEN(
Text007 +
‘@1@@@@@@@@@@@@@@@@@@@@@@@@’);
Window.UPDATE(1,0);
DELETEALL;
Saying this:
‘NO SE PUEDEN HACER CAMBIOS EN LA BASE DE DATOS HASTA INICIAR UNA TRANSACCION’
that traslated into English sounds like this: “NO CHANGES CAN BE DONE INTO THE DATABASE UNTIL A TRANSACTION HAS BEEN OPENED”
Someone could send any suggestion?
Thanks.