export to excel

Horse06

I have a question on the export to excel. I created a dataport and export the customer's address and then import to excel. But today I found the data exported are mismatched when I imported it into Excel. I also tried to copy the data directly from the subform of Navision and pasted them to excel, it is the same thing, most to the data(address1, address2, state and etc) fill one column in Excel. Does anyone of you happen to know what caused this? Thanks a lot!!! Very appreciate it!

matttrax

Excel can be kind of annoying sometimes. One of my favorite programs, but not always the best.

If you have a csv file and you right click on it to open with Excel, it will not open properly. You have to run Excel, then go to File --> Open, find your csv file, and follow the dialogs that open.

Hope that helps. Good luck.

Horse06

Can the file be exported be saved as csv file?

Horse06

My question is why I copied the columns from Navision and pasted to Excel, all the data are mismatched and most of them are crowded to one column. Thanks!

Stardust77

You might have some special characters used in your addresses, or perhaps even your field delimiter, which creates the mismatch. Sometimes, when you copy something from Excel into Navision, there are some special characters that will appear in the field from Navison, creating afterward a confusion.

Navi_Learner

Thanks for your reply! I checked my data and find there is no special characters in some data that are crowded into one coloumn. here is one example:
AAA
A3D-511B
180 Concord Pike

There should be three columns in the above data, but they are displayed in one column after I export it from dataport or copy directly from subform. I also checked some other data that works and I cannot tell any difference between them. I also created a dataport for the sales order and it is the same thing. Some sales orders are well exported, while the others are not.
Please look at the following data that are exported:
"CO05","Dz
","5 Grand Road
","","Newburyport
","","01220
","NJ
",""
So, I have to manually adjust them into one line in txt file before I import them to Excel. If we have a lot of data like that, we have to spend a long time.
The correct one export should be:
"CO05", Dz","3100 urlineAvenue","","Chatsworth","","01311","NJ",""
If we have to pay attention to the special character every time, it looks like it is not friendly. There should be a way to solve it, is it right? Please help!

Stardust77

I did not solve this issue. I have asked all the users to avoid using special characters, but it seems they still like them so much. ](*,)

Try to use in your dataport separators less likely to be used by the users. Example: ~ ^. Most of the time it works.

Horse06

Thank you, Stardust77! I found there is no special character mentioned in these addresses. Is there any way to program in dataport? Sometimes, we have a large volumn of addressed needed to be exported and it is very urgent to us. Anybody has any good suggestions? Thanks!

Savatage

You can analyse each field and delete all special characters

MyTextField := DELCHR(MyTextField,'=','-_+=\|[]}{".,#$@%^&*+!~`:;/?><');

If you just want to export to excel you could write a report that does this right to excel no conversions needed.

There's a great example in the download section My Mark Brummel
http://www.mibuso.com/forum/viewtopic.php?t=12014

Savatage

Here's a little report that exports the Customer's info below right into excel

No Name Address Address 2 City State Zip Phone Fax Contact

You can use this as an example, add or remove fields that you need.
http://savatage99.googlepages.com/50095 ... oExcel.fob

the automation in use is Microsoft Excel 11.0 Object Library'.Worksheet you'll find it in the Globals. you can change it to whatever verson of excel you are using incase it doen't run for you.

Horse06

Thank you, Harry!!! It works.

Savatage

I would hope so O:)
This method can be applied to other reports too.

You pretty much copy the code from the OnPreDataItem, OnPostDataItem & EnterCell to your new report. The OnAfterGetRecord Trigger you put the fields you want to export. Change the Text Constants to say what you want.

Basically

Stardust77

Thank you Savatage! =D> Great idea.

I made so far a lot of dataports to export to Excel, but this method is far more convenient to use. I did not notice Mark Brummel's example, so I only checked it today.

I am going to see how is it working for more complicate reports, with GroupFooter and Footer sections.

Stardust77

I have tested this idea for a more complex report, as is the Inventory Availability, which should export the figures for the items without variants, along with the figures for the variants. It works fine, except it takes a huge amount of time to export the data and to create the Excel sheet.

I think that I am going to use this method whenever a dataport cannot be used for some reasons, but otherwise I will stick with the dataports.

Savatage

Stardust77 wrote:

I am going to see how is it working for more complicate reports, with GroupFooter and Footer sections.

What you do is put the code on the SECTION you want to export.
Here's an Example using the Customer Aging Report.
http://savatage99.googlepages.com/50085 ... oExcel.fob

In response to your post above - yes it's not a super fast procedure, but it is just 1 step. You give up speed for ease. 8)

Usually I set filters (ie By Salesperson) because the subsequent times you run the report the faster it gets.

Stardust77

Actually, I do not understand very well why the dataport is so much faster than this report. In fact, they practically have the same code inside, yet while the dataport is doing it's task in maximum 2-3 minutes, it takes about 20 minutes to the report to do the same.

Anyhow, it could be very useful, especially for some very specific exports, where you have several indented DataItems, you need to use GroupFooters, or you need an Options tab which is tricky to add in the dataport.

Thank you once again.