Re: VS2003 transfer data between two data sources

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

"Otis Mukinfus" wrote:

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

I am loading a DataSet from an access file (exported from another app that a
vendor provided) and want to appendthe data into an SQL Table that has the
same structure.

I can easily append rows to the SQL table, but that can't be a best
practice! Does anyone have any guidance as to taking the filled dataset from
excel and then doing an append to the SQL table without having to step
through all the rows (obviously the two have the same structure)

Currently:

private void TransferData()

string _eConnectionString;
// Connection string for the Excel file selected through UI into TextBox
fileName
_eConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
this.fileName.Text + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [CIPEXPORT$]", oConn);

DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = cmd;

da.Fill(ds,"testData");
DataTable dt = new DataTable();

// Open up the SQL Server Connection
SqlConnectionConnection sConn = new SqlConnection(ConnectionString);
SqlCommand sCmd = new SqlCommand("_StoredProcedure", sConn);
SCmd.CommandType = CommandType.StoredProcedure;

sCmd.Parameters.Add("@Field1", SqlDbType.NvarChar, 10);
// list of fields by variable

// At this point what's the best/fastest method to transfer the dataset to
the Sql Server table? I could export to XML. There must be an easy method,
but I'm not fluent enough in ADO.NET yet.

Thanks!


Is this a one time exercise, or do you need to do it frequently? If you need to
do it frequently and have access to DTS on the MS SQL Server database, then
create a DTS package to do the transfer of data. Even if it's a one-timer you
can do it very easily with DTS.

If it must be done in an application, then I believe the best way is a row at a
time (but not with a DataSet), even though it may seem tedious, you might be
surprised how fast it is.

Question: In your question you say the file is an "access file", but in your
code you seem to be loading an Excel file. Just curious, not throwing rocks.


Good luck with your project,

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

.

Relevant Pages

  • Re: SSIS and unicode data types
    ... my varchar columns are at most 50 characters. ... destination column in the SQL database destination is only varcharor ... How do I get an Excel column of datatype ... It left SQL Server as a VARCHAR and went into Excel, ...
    (microsoft.public.sqlserver.dts)
  • Use Single Sign on to connect to sql server
    ... connection in an excel file that connects using an odc file connecting using ... The logon account information that you configure here must match the logon ... Name: SQL Server ... If I'm connecting using a sql username, do i need to use the domain piece ...
    (microsoft.public.sharepoint.portalserver.development)
  • Re: Excel 2007 connection
    ... The Feature pack also contains the SQL Server Client ... You should install it. ... so I installed them both as I do for Excel 2003. ...
    (microsoft.public.sqlserver.olap)
  • Re: Trasfer data to SQL Server
    ... Do you know how you would execute the DTS package from excel? ... Currently Using SQL Server 2000, ... This will place a SQL Server icon onto the DTS ...
    (microsoft.public.excel.programming)
  • Re: Newbie: Update From Excel
    ... Add an Excel Connection. ... Point it to your SQL Server ... Now create a transform data task coming from the Excel Spreadsheet to the ...
    (microsoft.public.sqlserver.dts)