Import from excel

Business Central/NAV Developers Forum
1

Hello All,

Could any one give me easy code for importing from excel? I dont want to use dataport

2

Hey Imran,

You could check the standard code used in NAV in example G/L Budget Imports (and Exports).

3

I have seen that, but i want easier one, just pls give me small example of the import.

Thanks

4

Excel is a rather complex format (even the new XML based format of Excel 2007) and I can’t really recommend you any other easier way.

Unless you of cause do what most users do when they have an Excel file they just want to import a few times.They simply save the file in CSV (comma delimited format) and import the file using Dynamics NAV’s dataport. THAT’S EASY! [:P]

5

Also using the word “easier” might not be the best way to put it across.It makes you look like you want a quick fix.One way to become an expert is to try and crack the how to yourself…[:)]

6

Hey Imran,

Using the Excel buffer is actually very very easy.

At its most basic each record your place in the table is a cell in Excel with a row and column Id, plus the text that you want outputted.

So to use it all you need to do is repeat through a record set, For each new record you increase the RowID and reset the ColumnID.

For each field you want to export send out the RowID and ColumnID and that should be it.

You can create a Global of the Excel Buffer and make it temporary.

Once the XLBuffer table is populated you can use its inbuilt functions to create the Excel Book.

Don’t be afraid and give it a try, any problems let us know

/TH

7

Doh, just realised you said import! LOL.

Its just as easy. Use the Function in Excel Buffer Table to import the data in from the excel book.

XLBuffer.OpenBook(Filename,XLBuffer.SelectSheetsName(FileName));
XLBuffer.ReadSheet;

Then I tend to use a report based on Integer.

OnPreReport

Setrange(number,1,TempExcelBuffer,Count);

OnAftergetRecord

If number = 1 then
XLBuffer.Find(’-’)
else
XLBuffer.Next;

// 2 Ways to recognise new Row. Keep Track of Row No. here or use the fact that the record if back to the first Column

If OldRowNo <> XLBuffer.“Row No.” then begin
NewRow;
OldRowNo := XLBuffer.“Row No.”;
End;

Case XLBuffer.“Column No.” Of

1 : //Column A

2 : //Column B

3: //Column C

End;

Hope that makes sense let me know if not

/TH

8

Thank you so much for the help. Your code helped me alot and understand.

9

can you give me sample…

like how to import customer from excel

thanks

10

you want to import from excel? or import to excel?

11

import from excel

let say… to customer table

12

Hi Stan,

Why not use (or look at the code) in the data migration form.

13

Hi Dave,

i thought in CU Migration management using xml

ImportFromExcel(XLSDataFile : Text[1024])
IF ISCLEAR(XlApp) THEN
IF NOT CREATE(XlApp,TRUE,TRUE) THEN
ERROR(Text019);

XlApp.Workbooks._Open(XLSDataFile);
XlBook := XlApp.ActiveWorkbook;

TmpXmlFile.CREATETEMPFILE;
XMLDataFile := TmpXmlFile.NAME + ‘.xml’;
TmpXmlFile.CLOSE;
TmpXmlFile.CREATE(XMLDataFile);
TmpXmlFile.CLOSE;
XMLDataFileClient := “3TierMngt”.DownloadTempFile(XMLDataFile);
ERASE(XMLDataFile);

XmlMaps := XlBook.XmlMaps;
IF XmlMaps.Count <> 0 THEN BEGIN
XmlMap := XmlMaps.Item(1);
XmlMap.Export(XMLDataFileClient,TRUE);
IF ImportSetupDataXML(XMLDataFileClient,FALSE,’’) THEN
IF SuppressMessage <> TRUE THEN
MESSAGE(Text021);
END ELSE
ERROR(Text020);

XlBook.Close(FALSE);
CLEAR(XlBook);
XlApp.Quit();
CLEAR(XlApp);

14

i want to import to table 246 (Requisition Line)

i have 4 fields in excel,which are column A,column B,column C,column D

Worksheet Template Name → Constant
Journal Batch Name → Constant
Line No. → Running no (+10000)
Type → Constant (2 for Item)

No. → Column A
Shortcut Dimension 1 Code → Column B
Shortcut Dimension 2 Code → Column C

Quantity → Column D

what i’m confused is how to know column A,B,C,D should go to what fields in NAV

my Code

AnalyzeData()
IF ExcelBuf.FIND(’-’) THEN BEGIN
REPEAT
RecNo := RecNo + 1;
ExcelBuf.“Column No.”:=1;
ExcelBuf.“Row No.”:=RecNo;
ExcelBuf.“Cell Value as Text” := ‘TEST’;//DELCHR(FORMAT(ExcelBuf.xlColID + ExcelBuf.xlRowID)),’<’,’ ');
IF ExcelBuf.“Cell Value as Text” <> ‘’ THEN BEGIN
ExcelBuf.INSERT;
END;
UNTIL ExcelBuf.NEXT =0;
Window.CLOSE;
ExcelBuf.RESET;
END;

15

Hi Stan,

The columns A,B,C… are mapped to the corresponding number

A = 1, B = 2 …

So

ExcelBuf.“Column No.”:=2; is column B

16

can we import from line 2?

because line 1 from my excel is the header only

data starts from line 2

17

does any body have a help file or step by step to import from excel?

18

try this http://www.mibuso.com/dlinfo.asp?FileID=811

19

i’m using excel buffer

is excel file → '-04.00

but why in excel buffer become -4

how to make it → '-04.00

because it is a text , not number