Excel to SQL

[Guest]

Hi all,
I took a look at the other thread with this subject, but didn't find the
info I'm looking for. Here's my question.

I want to query an CSV file (done) and insert it into a SQL table using C#.
Is it possible to do an Insert into sqlTable select * from the excel object.
If not what solution can you suggest me.

I know its not excel programing and more of C# question so if this is not
the appropriate forum. I'm sorry!

thank you!

 

[Tom Ogilvy]

I think you would want to open it as a ADO.Net recordset, so I don't see
much relation to the Excel Object unless you want to read it into Excel
first (which would probably be the long way around the block).

 

[Jamie Collins]

I think you would want to open it as a ADO.Net recordset using excel
object.

Tom, ADO.NET doesn't have a recordset object. Also, INSERT INTO..SELECT
does not return a rowset (it is a 'command' rather than a 'query') so
cannot be used to populate an ADO classic recordset anyhow.

Victor,
You *could*, using System.Data.OleDb, create an OleDbConnection to an
Excel workbook, create a related OleDbCommand and execute your INSERT
INTO..SELECT using odbc connection strings for both the source csv and
target database.

However, if by 'SQL table' you are referring to Microsoft SQL Server,
your connection could be to you server and the SQL could include SQL
Server's OPENROWSET to create an OLEDB connection to the csv file e.g.

using System.Data.OleDb;
...
OleDbConnection connection =
new OleDbConnection( "Provider=SQLOLEDB.1;Data
Source=MYSERVER;Initial Catalog=MYDATABASE;User Id=sa;password=;");
connection.Open();
OleDbCommand command1 = connection.CreateCommand();
command1.CommandText = "INSERT INTO MyTable (effective_date) SELECT
effective_date FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0','Text;HDR=YES;Database=C:\\Tempo\\','SELECT
MyDateTimeCol AS effective_date FROM [db.csv]');";
command1.ExecuteNonQuery();

Of course, for the above one would normally be using
System.Data.SqlClient with SqlConnection and SqlCommand objects; I
merely wanted to show you the OleDb equivalent.

Jamie.

--

 

[Tom Ogilvy]

http://tinyurl.com/3jy8u

might have something. Assumes you meant MS SQL Server

 

[shriop]

Use DTS, or buy a cheap CSV parsing component to pull the data into C#
for you.