Re: VS2003 transfer data between two data sources

On Sun, 9 Apr 2006 14:34:01 -0700, AbeR <AbeR@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Hi Otis,

Actually this is a monthly event. It's really not that big, probably about
500 records, but I thought since I had a data set/ data table, theoretically
there might be a way to attach it to another data adapter. Oops on the
datasource it is an Excel file (old habits are tough to break ;) ). A DTS
package might be a good solution except that the column names the SQL Server
sees will be inconsistent, thanks. When I originally wrote this applet it was
in MS Access where it's really simple to link data. I want to write a simple
windows app in .Net so I can work with the roles I designed within SqlServer.

The problem with reading an excel table without a header row is that the
some of the field names that are arbitraly assigned are almost random (this
was an issue when I did an attach to the server or tried a dts package) Some
columns are coming up as F1, F2... others are picking up the content of the
cells in the first row as column names. At least with a data reader I can get
column values by ordinal.

Maybe I'm making too much out of this, but I thought there must be a more
elegant solution - especially if you think of potentially scallable problems.
Another not so elegant solution might be to get the UI to read the Excel
file, write an XML, have a predefined XSD with field names, attach the XLM
and do a set appent?

Thank you for the feedback.

- Abe

[snip]

These comments are opinions, so take them with a grain of salt if you wish.

I wouldn't do it with XML unless you are getting the data from outside your
organization. My experience is that XML is over-used. It's bloated and
unnecessary unless your moving data across the web. It seems to me that a fad is
in place where people are so enamored with it, they use it at every opportunity.

I wouldn't do it with a DataSet. Reading the Excel document is going to be time
consuming enough. Putting the data in a DataSet while reading it is just
another added step. If one does that, they have to now parse the DataSet a row
at a time.

I think opening a DataReader in the Excel document is the way to go. One read
per row, one insert per row.

I've been doing backend data manipulation for more than twenty years and have
seen a lot of changes in this biz for the better, but when applying those new
and better technologies, a good rule to follow is still, and will continue to
be; Keep it simple, you may have to maintain it some day.

Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com
.

Relevant Pages

  • Re: SV: Possible to build up an Excel document programmatically?
    ... for creation of a document using XML tags (the technique or format is called OOXML, ... Producing an Excel document programmatically isn't hard, because, as you've pointed out, it accepts XML. ... ControlAdapter and knowledge of OOXML, but if you're going this far you can probably directly output OOXML instead. ...
    (microsoft.public.dotnet.languages.csharp)
  • Export to XML
    ... I have an Excel document, and it isn't "too" complex, but it will not ... Description can have multiple of Titles 3-5 but only one of Title2. ... I want this to be converted into XML. ... The xml may seem slightly confusing. ...
    (microsoft.public.excel.misc)
  • XML Mapping
    ... I have an excel document that I would like to export to xml. ... been able to create a schema and map each column in excel to the ... is there a way to map individual cells? ...
    (microsoft.public.excel.misc)
  • How do I send an Excel attachment so that the recipient cannot al.
    ... I'm trying to send an Excel document as an attachment. ... but found that I could alter the document while reading it. ... Prev by Date: ...
    (microsoft.public.excel.misc)
  • Re: counting through the alphabet that is above 702..
    ... since the output of the program is an XML based excel document ... need to reconstruct it the way it was and we need to find a logic to ...
    (microsoft.public.excel.programming)
Loading