Microsoft Dynamics NAV (Archived)

CSV creation from Excel Automation

(0) Share

Report

Posted on by subrata.bauri

1,144

Dear all,

How to create a csv file using the Excel Automation in Nav 2009 R2 Database ?

I have tried my best but still unable to achieve the same.

My generated csv files looks fine but when I open with notepad mode it seems wrong.

Suppose there is a value/string "Dynamics" and I want it should be remain same in the both view whether it from excel view or notepad view, but in my case the string "Dynamics" looks ""Dynamics"" in notepad view that extra " is creating the problem.

How to solve this ?

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Suggested answer

Suresh Kulla 50,243 Super User 2025 Season 2 on at

Like (0)

Report

Try to check this codeunit from this blog

www.archerpoint.com/.../beloved-report

Was this reply helpful? Yes No
Suggested answer

4BzSoftware 6,073 on at

Like (0)

Report

Hi Subrata,

Why do you use Excel Automation to create a csv file?

You can use dataport or write code to export data to csv text file, it is simpler and faster.

Was this reply helpful? Yes No
subrata.bauri 1,144 on at

Like (0)

Report

Actually I want to run the code from NAS that's why I'm going to use dataport object.

Was this reply helpful? Yes No
subrata.bauri 1,144 on at

Like (0)

Report

Previously a custom dataport was used to export data into excel file (CSV format).

The dataport had the following properties

FieldStartDelimiter =<"> and

FieldSeparator =|

Was this reply helpful? Yes No
subrata.bauri 1,144 on at

Like (0)

Report

i just want to avoid extra double quote when saving excel file as csv.

Was this reply helpful? Yes No
Suggested answer

Suresh Kulla 50,243 Super User 2025 Season 2 on at

Like (0)

Report

Just FieldStartDelimiter and FieldEndDelimiter to <None> as show in the below figure to avoid doubles quotes.

Was this reply helpful? Yes No
subrata.bauri 1,144 on at

Like (0)

Report

I'm not using any dataport object.

I'm looking for the same result from my custom made codeunit which I want to fire through NAS.

My code is given below:

// Start Code

StrWhere:='=';

StrWhich:=',"';

FilePath :='D:\';

Location.RESET;

IF Location.FIND('-') THEN BEGIN

IF NOT CREATE(XlApp,TRUE,FALSE) THEN

ERROR('Excel Not Found.');

I :=1;

xlBook := XlApp.Workbooks.Add(-4167);

xlSheet:= XlApp.ActiveSheet;

xlSheet.Range('A'+FORMAT(I)).Value :=

'Factory_Code|"Factory_Name"|"Factory_Address"|'+

'"Factory_Address2"|"Factory_City_Name"|"Factory_State_Code"|'+

'"Factory_Is_Active"|"Owner_Name"|"TIN_No"|"EMail"|"Factory_Master_Code"';

REPEAT

Found1:=TRUE;

I :=I+1;

xlSheet.Range('A'+FORMAT(I)).Value :=

FORMAT(DELCHR(Location.Name,StrWhere,StrWhich))+

'|'+FORMAT(DELCHR(Location.Address,StrWhere,StrWhich))+'"'+

'|'+FORMAT(DELCHR(Location."Address 2",StrWhere,StrWhich))+'"'+

'|'+FORMAT(DELCHR(Location.City,StrWhere,StrWhich))+'"'+

'|'+FORMAT(DELCHR(Location."State Code",StrWhere,StrWhich))+'"'+

'|'+FORMAT(TRUE)+'"'+

'|'+FORMAT(DELCHR(Location.Contact,StrWhere,StrWhich))+'"'+

'|'+FORMAT(DELCHR(Location."T.I.N. No.",StrWhere,StrWhich))+'"'+

'|'+FORMAT(DELCHR(Location."E-Mail",StrWhere,StrWhich))+'"'+

'|'+FORMAT(Location.Code)+'"';

UNTIL Location.NEXT =0;

END;

IF Found1 THEN BEGIN

XlApp.Visible := FALSE;

XlApp.DisplayAlerts := FALSE;

xlBook.Activate;

xlBook.SaveAs(FilePath+FORMAT(TODAY,0,5)+'_'+FORMAT(TIME,0,2)+'.csv',6);

xlBook.Close; //To Close the Excel

CLEAR(XlApp);

CLEAR(xlBook);

END;

// End Code

The generated csv files looks(excel view) exactly which I want to create but when I open it in notepad mode a extra double quotes are found.

I don't want this extra double quote.

Was this reply helpful? Yes No