Hi,
In my application, I need to copy data from an Excel file into a SQL
table. The article related to this can be found at
http://support.microsoft.com/defaul...Ben-us%3B306572
Using this,I am first extracting data from given excel file into a
temporary DataTable. After making some operations on that DataTable
(like splitting one column into two), I am saving the data into actual
table in SQL Server.
My doubt is that, in the above given link, there are few steps needed
to do on the excel file... eg.
--> Highlight the rows and columns where the data resides.
--> On the Insert menu, point to Name, and then click Define.
--> In the Names in workbook text box, type myRange1, and then click
OK.
I don't want my client to do thsese operations everytime he changes
contents in that excel file, as this excel file changes almost daily.
The extracting of data from excel file is done after clicking UPDATE
button on webpage. The person saving data into Excel file is differnet
from the one who updates it into SQL.
Is there any other way where we can directly copy data from excel file
into DataTable without doing the above three steps?
I am using Visual Studio.NET 2003,SQL Server 2000 and C# as developing
language for this web-based project.
Any advice would be greatly appreciated. Thanks in advance.
Regards,
RK."RK" <rakish123@.yahoo.com> wrote in message
news:22626a3.0404020930.7d9bdd7e@.posting.google.co m...
> Hi,
> In my application, I need to copy data from an Excel file into a SQL
> table. The article related to this can be found at
> http://support.microsoft.com/defaul...Ben-us%3B306572
> Using this,I am first extracting data from given excel file into a
> temporary DataTable. After making some operations on that DataTable
> (like splitting one column into two), I am saving the data into actual
> table in SQL Server.
> My doubt is that, in the above given link, there are few steps needed
> to do on the excel file... eg.
> --> Highlight the rows and columns where the data resides.
> --> On the Insert menu, point to Name, and then click Define.
> --> In the Names in workbook text box, type myRange1, and then click
> OK.
> I don't want my client to do thsese operations everytime he changes
> contents in that excel file, as this excel file changes almost daily.
> The extracting of data from excel file is done after clicking UPDATE
> button on webpage. The person saving data into Excel file is differnet
> from the one who updates it into SQL.
> Is there any other way where we can directly copy data from excel file
> into DataTable without doing the above three steps?
> I am using Visual Studio.NET 2003,SQL Server 2000 and C# as developing
> language for this web-based project.
> Any advice would be greatly appreciated. Thanks in advance.
> Regards,
> RK.
The article doesn't mention SQL Server anywhere, and it sounds like your
problem is how to automate something in Excel, so I guess you'll get a
better answer in an Excel or C# newsgroup.
Simon|||RK (rakish123@.yahoo.com) writes:
> Using this,I am first extracting data from given excel file into a
> temporary DataTable. After making some operations on that DataTable
> (like splitting one column into two), I am saving the data into actual
> table in SQL Server.
> My doubt is that, in the above given link, there are few steps needed
> to do on the excel file... eg.
> --> Highlight the rows and columns where the data resides.
> --> On the Insert menu, point to Name, and then click Define.
> --> In the Names in workbook text box, type myRange1, and then click
> OK.
> I don't want my client to do thsese operations everytime he changes
> contents in that excel file, as this excel file changes almost daily.
> The extracting of data from excel file is done after clicking UPDATE
> button on webpage. The person saving data into Excel file is differnet
> from the one who updates it into SQL.
> Is there any other way where we can directly copy data from excel file
> into DataTable without doing the above three steps?
Of course there is.
I have not tried to get data from Excel with SELECT statements, but
would be surprised if "SELECT * FROM Sheet1" would work. I've mainly
use the OLE interface to get data from Excel in Perl. While I understand
that you don't want users to have to highlight things, I fail to see
but that you need to have some pre-defined structure that the Excel
file must have, or else you will have no idea of what you are importing.
But while you said SQL Server twice in your posting (look again Simon! :-),
your question seems more to be related to Excel, so you should find a
group about Excel.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You might look at the capabilities of SQL Server DTS Packages. They can
read from Excel spreadsheets. However, your datasheet would have to be
massaged enough that the data was a fairly straightforward import.
I suppose if I were you, I'd use a combination of VBA code to hack up the
spreadsheet, save it to a convenient spreadsheet, then use SQL-DMO to
trigger a DTS package for the import.
Otherwise, you could just do a VBA module that would pass in the interesting
data directly over an ADO, OLEDB or ODBC connection. We've done something
like that, it wasn't terribly difficult.
"RK" <rakish123@.yahoo.com> wrote in message
news:22626a3.0404020930.7d9bdd7e@.posting.google.co m...
> Hi,
> In my application, I need to copy data from an Excel file into a SQL
> table. The article related to this can be found at
> http://support.microsoft.com/defaul...Ben-us%3B306572
> Using this,I am first extracting data from given excel file into a
> temporary DataTable. After making some operations on that DataTable
> (like splitting one column into two), I am saving the data into actual
> table in SQL Server.
> My doubt is that, in the above given link, there are few steps needed
> to do on the excel file... eg.
> --> Highlight the rows and columns where the data resides.
> --> On the Insert menu, point to Name, and then click Define.
> --> In the Names in workbook text box, type myRange1, and then click
> OK.
> I don't want my client to do thsese operations everytime he changes
> contents in that excel file, as this excel file changes almost daily.
> The extracting of data from excel file is done after clicking UPDATE
> button on webpage. The person saving data into Excel file is differnet
> from the one who updates it into SQL.
> Is there any other way where we can directly copy data from excel file
> into DataTable without doing the above three steps?
> I am using Visual Studio.NET 2003,SQL Server 2000 and C# as developing
> language for this web-based project.
> Any advice would be greatly appreciated. Thanks in advance.
> Regards,
> RK.|||Hi,
There are a few alternatives. I recommend you to use SQL*XL an addin I
wrote for Excel to do these things. There are some other alternatives
which I will mention.
1. SQL*XL - addin for Excel to access your databases.
SQL*XL is an addin for Excel that allows you to access your database
directly from Excel. You can query data into Excel or you can pump
data from Excel into the database. SQL*XL focusses on making it easy
for the end user but it can also be used to make scripts as it is
fully compatible with Excel macro recording and VBA.
Have a look at SQL*XL at: www.oraxcel.com
2. Define your Excel workbook as a data source in ODBC
If you do this you can issue select statements against it. You can use
it in an openquery statement in SQL server or even make it a linked
server I suppose. Named ranges will appear as tables. Reserve row 1
for the column headers.
Problems with this technique: it is readonly, you may have problems
with accessing the workbook (in use)
The nice thing is that you can use it directly in SQL or DTS
3. You can write a little macro in Excel that dumps the file out in a
nominated format (CSV e.g) and a nominated directory. You could have
DTS to pickup the file and import it in your database table.
Hope this helps,
Gerrit-Jan Linker
Linker IT Consulting Limited
www.oraxcel.com
Author of SQL*XL
