Goodbye CSV and Text Files – Hello XML

I’ve built more than a few Excel apps that used a CSV file or Text Flat File as their data input. There are multiple reasons for doing so, here’s my top 3:

1. I wasn’t allowed access to the database, however an export was permitted (uptight DB admins).

2. The driver that was needed to connect to the database would probably not be installed on every computer the spreadsheet would be used on.

3. I wouldn’t have a connection to the database every time I needed it (out of company network etc).

In each case I would ask something like “well then, can I get a flat file of the data, say, every Monday morning”, and the response was usually “Text or CSV?”… I think from now on I’ll say XML.

Why? XMl is easier to use and offers more flexibility than a CSV file.

There aren’t any amazing differences, however here are a few obvious ones off the top of my head:

• With XML I can import only the columns of data I want in a spreadsheet. With CSV I need to import the entire document.

• With XML I can import different parts of the Data to wherever I like on the spreadsheet. With CSV I’m restricted to importing the data as it looks in the file.

• With XML I can right click and hit “refresh” when the data is new. With CSV I need to walk through import process again. (takes a few minutes with fixed width imports and a large number of columns)

Of course I can get the same functionality from a CSV import with some coding, however there’s no need to if I start with XML.

This is possibly a no-brainer for some, however it’s a step in the “welcome to XML, welcome to 2005” direction for myself. Maybe it’s a reason you may want to explore the XML functionality in Excel also.