Format .xml text document in Excel

[Catsrules]

I have a program with a proprietary database I am trying to export out into another database. The Proprietary database does have an export feature that gives me raw .xml file however Excel is having really trouble trying to figure out the file.

 

The file if formated like this <variable><name>The name</name><datatype>String</datatype></variable><variable><name>The name2</name><datatype>String</datatype></variable>

 

The actual file has like 50 more fields in between <variable></variable> but for simplicity I have just shown name and datatype

 

I would like Excel or any Excel type program, LibraOffice Call to treat <variable></variable>  as one row with the <name>The name</name><datatype>String</datatype> etc.. as columns in that row.

 

Any ideas on a quick way of doing this?

 

Thanks.

[mariushm]

If you give me the file I can try to convert it into a CSV file or something you can open in excel

I think one of the tools I have can take such xml file and import the info in a database, if it does I can export the data in some other format for you.

// ps or at least send me the first 100 KB or so of the file to see if my tool can partially process it and we'll go from there.

[Catsrules]

5 hours ago, mariushm said:

If you give me the file I can try to convert it into a CSV file or something you can open in excel

I think one of the tools I have can take such xml file and import the info in a database, if it does I can export the data in some other format for you.

// ps or at least send me the first 100 KB or so of the file to see if my tool can partially process it and we'll go from there.

Sorry but I can't post the file online or share it,is against work policies and such.

[dgsddfgdfhgs]

5 hours ago, Catsrules said:

<variable><name>

a little tip for manual search & replace, a tab in notepad tranlates to next cell in excel

 

so open xml in notepad separate all >< with >'tab'< then copy everything to excel for further edit

tools like notepad++ is quite useful too

[LogicalDrm]

If you have Access, I think that can import XML files. I haven't used that feature before, so I don't know exactly how it works. And I don't have example file to test with. Anyway, once in Access, you can export tables as CSV which can be imported as is to any other software. Or if your next database supports Access files, you can import directly from Access.

 

If not, then I would recommend doing the above with Notepad++. Replace with tab or ";" or ",", depending on how numerical values are. You can also delete any repetitive things by replacing with nothing on Notepad++.

[Catsrules]

19 hours ago, dgsddfgdfhgs said:

a little tip for manual search & replace, a tab in notepad tranlates to next cell in excel

 

so open xml in notepad separate all >< with >'tab'< then copy everything to excel for further edit

tools like notepad++ is quite useful too

 

Ahh, I didn't know that tab would translate into next cell. Do you know if there is a way to translate enter/return?

 

18 hours ago, LogicalDrm said:

If you have Access, I think that can import XML files. I haven't used that feature before, so I don't know exactly how it works. And I don't have example file to test with. Anyway, once in Access, you can export tables as CSV which can be imported as is to any other software. Or if your next database supports Access files, you can import directly from Access.

 

If not, then I would recommend doing the above with Notepad++. Replace with tab or ";" or ",", depending on how numerical values are. You can also delete any repetitive things by replacing with nothing on Notepad++.

 

Oh I forgot about access, yeah I have it, I will definitely play around with it and see what it can do. Thanks.

[dgsddfgdfhgs]

13 minutes ago, Catsrules said:

Do you know if there is a way to translate enter/return?

may be the macro /recorder in notepad++ could do the trick, basically repeats you actions for set number of times you edited  i.e. key logger

 

eg.

start recording

search specific keywords > moving the arrows keys >press enter / return 

stop recording and run 

 

you could be very creative with these functions, and you may want to split you file into smaller files

[Catsrules]

23 minutes ago, dgsddfgdfhgs said:

may be the macro /recorder in notepad++ could do the trick, basically repeats you actions for set number of times you edited  i.e. key logger

 

eg.

start recording

search specific keywords > moving the arrows keys >press enter / return 

stop recording and run 

 

you could be very creative with these functions, and you may want to split you file into smaller files

oh that is a good idea as well.

I will look into that.