Export data

[Former Member]

Hi Support,

Now I have an excel file which contains 110 thousands records with ID field and the ID are miscellaneous. There is another field called Description in the main table of the respository. Can MDM export an excel file containing both the ID field and the matching Description field.

Thanks & Regards,

Song

[Former Member · Accepted solution]

Hello Song,

Yes, it is possible, the most straight forward way is to export the needed data to excel file from MDM Data Manager.

From MDM Data Manager select File-> Export to->Excel.

In the dialog select the "fields to export", in your example it should be the ID and Description fields.

You may export the entire main table records and later cross between the 110K records that you currently have and their respective descirptions to generate the correlation between IDs and descirptions.

Regards,

Hedda Cohen

[Former Member · Accepted solution]

Hi Hedda,

Thanks for the helping.

My repository contains 200,000,000 records and I only want to export 11,000 records of them according to the ID in the excel file. Can you help me find a solution?

Regards,

Song

[Former Member · Accepted solution]

Hi,

here is exactly what you need to do.

1. Create a txt file with your IDs, first line in the file should tell exactly the column name of the field in MDM repository on which you will be searching. i.e. If you will be searching on the MDM ID field, then txt file should look like this:

MDM ID

101

102

...

2. Create a new mask.

3. Open main table and select: Records -> Modify Mask -> Select Mask and Add it, then in the Operaion select 'Replace with' and in the Source choose 'Records from file', select your file.

Now you have all records from your file in the mask, open mask and export what you need.

Simple isn't it

[Former Member · Accepted solution]

Hi Edas,

It is exact what I want. I tried it and it works well in my test system.

There is one issue that the feild name in our production environment is difined as Chinese and when I selected the .txt file in the dialog it will warning me the field could't find in the table. It is because the Chinese field name are changed to messy code. Then I converted the .txt to UTF-8 and Unicode and it doesn't work either.

Regards,

Song

[Former Member · Accepted solution]

Hi Songxu,

It is showing you grey because MDM Syndicator accept merge file as only Tab Delimited Format. You can check the format of your file is that accurately tab delimited format or not using MDM Import manager. Select this file Type as Delimited and give there for this as \t. if you are able to open it please check this format in source preview. if this is fine then as said in above post try using mdm syndictor. it will take this file.

Note: Make sure this file should be in tab delimted format only. It does not take any other format and will show you always grey if your file is not tab delimited format.

Revert with result and let me know if you still face any issue.

Regards,

Mandeep Saini

[Former Member · Accepted solution]

Hi Mandeep,

I tested the file with import manager and I could open it successfully. But the Matching Fields and Source Fields to Marge are also grey. Is there any other reasons contributing to this issue?

Regards,

Song

[Former Member · Accepted solution]

HI songxu,

I think you are missing something, Tab delimited text format file should work.

Please find the procedure on page 10 to 13 of 20 (10-13/20) of this article

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/20dbbca2-fde5-2a10-c68a-95745c1fc...

Revert with result...

Regards,

Mandeep Saini

[Former Member · Accepted solution]

Hi Mandeep,

I did it step by step and it didn't work. I find they are white(editable) when a new Merge Item is built at the bottom of page 10. But in page 130 of my document MDM 5.5 SP06 - Syndicator Reference Guide.pdf, the new Merge Item are grey as me. Is it possible that we should do some setting firstly like Map Properties or Merge Item Properties?

Regards,

Song

[Former Member · Accepted solution]

Hi Songxu,

If your Add merge item is not saved properly, it will not allow you add source file as it is non editable.

After Add merge item under merge tab, it should be saved properly else it would not reflect editable. I would suggest like as in given link, page10-13 of 20 give Add merge Item name as Mege Item1 as shown in that above PDf, now just click on any other tab like Item mapping tab and again come back to Merge tab. If this merge Item1 is saved properly, it will editable and you need to first select your text delimited file and once you would able to see your source file link there then only both Matching fields and Source fields to Merge become editable.

Note: I dont feel that there is any setting you need to do it.

Revert with result

Regards,

Mandeep Saini

[Former Member · Accepted solution]

Hi Mandeep,

It didn't work either. It is really weird.

Do you have any other ideas? If not, let's leave it over. Thanks for your patience. I did learn a lot from this issue and be more familiar with MDM.

Regards,

Song

[Former Member · Accepted solution]

Hi Songxu,

I tested the solution given by Edas, it is really good and most appropriate among all the solutions.

As you said, There is one issue that the feild name in our production environment is difined as Chinese and when I selected the .txt file in the dialog it will warning me the field could't find in the table.

Say your source file looks like as given below:

Field ID:

11001645242

20000701912

But problem is your Field ID in MDM production environment is written as 外地ID. So all you need to change your source field ID as 外地ID. I mean your source file should look like this as given below then your problem will be solved.

外地ID

11001645242

20000701912

Since your repository is loaded and in production environment you need field name as 外地ID not Field ID.

As suggested above how to export records using mdm data manager, select only one record which has value 外地ID in MDM. Now Go to File->Export to ->say text, select fields to export as 外地ID from Available fields.

Also mark check box as tick For Selected Records Only under Export Options box. A file will export with this single(only one) field name and value, so in this way you will get your Chinese field name as 外地ID and then put it into your source file. Now as suggested procedure by Edas go for it it will result in mask with all of your 11000 records. Now as said in above post to export data using mdm data manager go for it. This will resolve your problem.

Revert with Result.

Regards,

Mandeep Saini

[Former Member · Accepted solution]

Hi Mandeep,

Appreciate for your solution. It solved the issue successfully.

You can speak Chinese?[外地] ^_

Thanks again!

Song

[Former Member · Accepted solution]

Hi Songxu,

Its great to know that finally your issue get solved :). I would say please award points accordingly to everyone reply which helped you in solving this issue so to keep motivating SDN contributors.

Regards,

Mandeep Saini

[Former Member · Accepted solution]

Hi Mandeep,

Oh, I misunderstood the regulation and I guess the three level points could only assign to three people. Thanks for the reminding. I have awarded the points accordingly.

Regards,

Song

[Former Member]

Hi Songxu,

Using Syndicator ,You can' t export into Excel format, i mean XML or Text only. But you can convert it into XML/Text to Excel file using PI. One intersting thing, even if you export these two fields in Syndicator text file format, then after exporting this file on desktop, try open this file by right click open with --> Microsoft File Excel format, this file open in Excel format and then save as give new name, you can get excel file format easily.

Other Alternative, you can easily do it by making use of MDM Data Manager.

Open MDM data Manager, Go to File>Export>Excel, A pop up window will come under Fields tab, Select ID field and Description in Fields to Export: Column from Available Fields: Column. and then Press Ok.

It will export both of these fields in Excel format.

You can also select other fields if required in Fields to Export: Column from Available Fields: Column.

Regards,

Mandeep Saini

[Former Member]

Hi Mandeep,

Thanks for the quick repay.

You might misunderstand my requirement. There are more than 200,000,000 records in my repository, but I only want to export 11,000 records of them according to the ID in the excel file. Because these ID numbers are miscellaneous and I can't search them out in Data Manager. Does MDM have another way to export them.

Regards,

Song

[Former Member]

Hi Songxu,

As per my understanding, you need to filter these records and put under mask, say e.g your 11000 records has different search criteria, filter some records say up-to 1000 as per some search criteria and put in mask, then do another search criteria as you said it is miscellaneous and need to put in mask gain and again till all your search of 11000 records not come into this MASK. Then click on this mask created you will find 11000 of 200,000,000 records found and export as explained using MDM Data Manager.

Create a Mask after selecting Mask tables as current table in MDM Data Manager not main-table. Give some name of MASK say 11000. Now select Main table as Current table in MDM Data Manager, suppose you find some recods upto 10 then select all these 10 records using keyboard option ( CTR +A), right click Add to Mask->11000 , then again find remaining records 10990 and put it in mask inthe same manner i eman select record->right click> Add to Mask>11000 till it completes 11000. I understand that this will take lot of time to search and put in mask but as such i think there is no other way to do it. Once your Mask has all these 11000 records. Go to Drill down search-->Masks->click on 11000 and you will find 11000 of total(XXX) records found. Now export using MDM data Manager as explained in above post.

Note : while exporting records in excel file if you get any error then go for:

To configure Microsoft Excel 2002 to permit MDM export to Excel:

1. Open Microsoft Excel 2002.

2. From the Excel Main menu, select Tools > Macro > Security.

3. In the Security dialog, click the Trusted Sources tab.

4. Click to enable the Trust Access to Visual Basic Project check box.

5. Close the dialog.

Regards,

Mandeep Saini

[Former Member]

Hi Mandeep,

I got your meaning and thanks for the exhaustive explanation.

I need't to do another search criteria when searching the required data. I only need search the ID field. but this field's data are irrelevant each other. Here is a little part of the data.

ID:

11001645242

20000701912

20000000563

20001790450

20000720478

20000702294

20000817227

20001761333

20001831220

20002082086

20002145064

11000466811

So it is really a tough job to add them to the mask one by one because I even can't search 10 records per time as you wished.

Anyway, thanks all the time.

Regards,

Song

[Former Member]

Hi Songxu,

Do you have this excel file already with all the records containing ID i mean 11000 records IDs?

and you want to export from MDM with MDM ID and Description using this source file 11000 records ID?

If yes then you can export these records with complete data without mask which is quite a time consuming process.

All you need to do firstly convert this already excel file containing ID into text delimited format. i mean open this file save as Give File Name: ID.txt and Save as Type: Text(Tab Delimited). if you able to get this already file into text tab delimited format then job is very easy.

Open MDM Syndicator, Create two Fields which you want to export from MDM say ID and Descirption under Destination Items Tab. Then map this source field Description with this Destination Items Description field.

Now, come to Merge Tabs, Select Source file as your ID.txt file, Matching Fields: Source Fields: ID with Matching MDM Fields: MDM ID, Source Fields to Merge: ID

Map this ID when navigate to Merge under Item Mapping with target MDM ID. Now export.

I know you will get all the records but for all of your 11000 records, you will get MDM ID and Description both as you map Merge source ID as MDM ID and for rest of records you will get Description only, delete all these records which don't have MDM ID. So finally you will get all 11000 records with ID and Description. Now you have this file as Text Tab delimited. As explained above now try open this file by right click open with --> Microsoft File Excel format, this file open in Excel format and then save as giving new name, you can get excel file format easily.

Regards,

Mandeep Saini

[Former Member]

Hi,

If you need to export only some of records, there should be common value in those records.So if there is any thing common value

in those records,put search based on that.you will get required records which you want and syndicate them.

If you do not have any common value,create dummy field in your repository (For example: Boolean field with True and False values).Make the boolean field as "True" for records which you want to export by using import manager.

For this, you need 2 fields like ID and boolean field with True value as souce file to import using import manager.After importing

you can see records which you want contains True value for boolean field.Then you can perform search like where boolean field

is equal to True.Finally you will get the required records to syndicate.

Thanks,

Narendra

[Former Member]

Hi Song

A simpler way.

1. Assuming that you have a ready file with all different IDs. Create one more column in excel file with label as processed and fill 01 for all rows.

2. Create a dummy field in repository- Indicator for Import as text. Else select some existing field which is not used and has blnak values for all records.

3. Import the excel file with mapping the ID with the material numbers/IDs in MDM and Processed with Indicator for Import

4. open data manager, do a free form search for the field Indicator for Import with value as 01.

5. Export them all in excel.

Best regards

Ravi

[Former Member]

Hi Mandeep,

Yes, you understood my requirement correctly.

I tested what you said, but the value lines of Matching Fields and Source Fields to Marge were gray(unable) after I created a Merge Items and selected the Source File under the Merge Items Tab.

Could you tell me why?

Thanks & Regards,

Song

[Former Member]

Hi Narendra and Ravi,

The solution you recommended me was pretty good, but the data was in the production environment. So I can't stop the server to restructured the table.

Thanks & Regards,

Song

[Former Member]

Please find the relay above.