Excel to XML and CSV ( Using Excel 2002) 1

[vbakan]

Hi All,

Could anyone have an idea how I can convert a Generic Converter from Excel to .XML and .csv and wise versa ? Any code sample or advise would be a grate help

I know in Excel 2003 have got the XML option but not in Excel 2002, But I need to work on Excel 2002 unfortunately.

The Transformation is from

.csv to .xls from .csv

But what I thought is it is better to use XML to work with

so the transformation is

.csv to .xml to .xls from .xml to .csv

Any idea about my thinking would be much appriciated.

 

[Chance1234]

Why the need for XML ?

CSV will open straight up in Excel and a XLS can be saved as a .csv

Chance,

F, G + _HH

 

[vbakan]

Thanks for your reply. The reason for using XML is to manupulate the .csv file data and update certen part of the .csv file

If you think any better way of doing it please let me know.

.csv files are the input files and produce another output file as .csv

 

[Chance1234]

How does this sound ?

via code,
open up the CSV file
Load data into an array
Carry out manipulation
Save back down as CSV

alternativly
via code,
open up the CSV file into excel
Use range/cell to manipulate the data on the excel spreadsheet
Save back down as a csv




Chance,

F, G + _HH

 

[vbakan]

Thanks again, But the problem is the .csv file data is unorderd therefore it need to be formated for the user to enter some additional values and then validate the data types. I though if I have it in xml then I can do all that. Also this file can be used as input for another system which accept .xml only

 

[Chance1234]

there is no reason why you can't do the formating sorting in the code or by manipulating the sheet.

however, would not XSLT be better at converting over the CSV file ?

Chance,

F, G + _HH

 

[vbakan]

That is what I am thinking to do , that is why I tend to use XML. Can I use XSLT with excel or .csv ?

 

[Chance1234]

you can use XLST to convert a csv file to
xml and vice versa.

Then you could change your workflow

CSV
XLST - to carry out your formating / sorting
Load modified CSV into excel allow user to make changes
Then in code you could convert the CSV via XLST to your final XML file nad have your csv file



Chance,

F, G + _HH

 

[vbakan]

Chance,
This is exactly I want to do, but I don't have much idea, Any sample code help would be grately appriciated.

 

[Chance1234]

in regards to csv and XSLT have a look at the following

http://www.scit.wlv.ac.uk/~jphb/xml/xmlcsv.html

Also there is a XML forum on Tek Tips which will be a better place to post that question

In regards from going back from excel to csv and XML I refer you to the faq i pointed you to in your other post, but also as well;

how much do you know about dom objects and parsers ?




Chance,

F, G + _HH

 

[Chance1234]

another interesting link here

http://www.codeproject.com/vbscript/xml2csv.asp

Chance,

F, G + _HH

 

[vbakan]

Thanks for your replys,I would say I know about 30 % of DOM, but I sould be able to work on it if I have the clear direction