Import partial Sql table from Excel spreadsheet

I have this situation that I need to read a spreadsheet with user names into a sql table where user name is just one of the columns. I tried using oledb connection to read the spreadsheet and sqlbulkcopy to import into sql table. There was no error, but the data wasn't imported into sql.

Does anyone have any suggestion what I did wrong or what is the right way of doing this?

Thanks a lot.

Mia

Try the thread below for all you need, post again if you still have question. Hope this helps.

http://forums.asp.net/thread/1442470.aspx

|||Thank you. I will take a look at the posts/articles and let you know.

Import Logins from text or spreadsheet

Is there a way to create logins from a text file or
spreadsheet? I have a long list of users. They will be
Sql Server Authenicated. I need to
1. add login
2. set psw to generic value
3. set default database
4. add them to user defined role
Thanks,
Brian
> 1. add login
> 2. set psw to generic value
> 3. set default database
4. add user to database
5. add them to user defined role
The required script template:
EXEC sp_addlogin 'SomeLogin', 'SomePassword', 'SomeDatabase'
USE MyDatabase
EXEC sp_adduser 'SomeLogin'
EXEC sp_addrolemember 'SomeRole', 'SomeLogin'
Let's assume your Excel spreadsheet has 3 columns: Login, DefaultDatabase
and Role. One method is to generate the needed script using SQL and
OPENROWSET. The query below will generate a script for each row in the
spreadsheet. You can then copy/paste the results into a Query Analyzer
window and execute. The spreadsheet file needs be accessible by the SQL
Server service.
SELECT
'EXEC sp_addlogin ''' +
Login +
''', ''SomePassword'', ''' +
DefaultDatabase + '''
USE ' + DefaultDatabase + '
EXEC sp_adduser ''' +
Login + '''
EXEC sp_addrolemember ''' +
Role +
''', ''' +
Login +
''''
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\temp\Logins.xls',
'Select * from [Sheet1$]')
Hope this helps.
Dan Guzman
SQL Server MVP
"Brian" <bgroves@.medibase.com> wrote in message
news:2db5001c46b6b$7f8d35d0$a601280a@.phx.gbl...
> Is there a way to create logins from a text file or
> spreadsheet? I have a long list of users. They will be
> Sql Server Authenicated. I need to
> 1. add login
> 2. set psw to generic value
> 3. set default database
> 4. add them to user defined role
> Thanks,
> Brian
|||Possibly less prone to error is:
CREATE PROCEDURE Process (
@.SomeLogin sysname,
@.SomePassword nvarchar(200) collate Latin1_General_CS_AS,
@.SomeDatabase sysname
) as
DECLARE @.SQL nvarchar(4000)
set @.SQL = '
EXEC sp_addlogin $L$, $P$, $D$
USE MyDatabase
EXEC sp_adduser $L$
EXEC sp_addrolemember $R$, $L$
'
select
replace(replace(replace(replace(@.sql,
'$L$',quotename(Login,'''')),
'$P$',quotename(Password,'''')),
'$D$',quotename(Database)),
'$R$',role)
from openrowset ...
It could go wrong if any of the Excel fields contains one of
the $x$ codes replaced later.
Creating a 4th column in Excel is also a solution:
="EXEC sp_addlogin '"& A1 & "', ... and so on
Steve Kass
Drew University
Dan Guzman wrote:

>4. add user to database
>5. add them to user defined role
>The required script template:
>EXEC sp_addlogin 'SomeLogin', 'SomePassword', 'SomeDatabase'
>USE MyDatabase
>EXEC sp_adduser 'SomeLogin'
>EXEC sp_addrolemember 'SomeRole', 'SomeLogin'
>Let's assume your Excel spreadsheet has 3 columns: Login, DefaultDatabase
>and Role. One method is to generate the needed script using SQL and
>OPENROWSET. The query below will generate a script for each row in the
>spreadsheet. You can then copy/paste the results into a Query Analyzer
>window and execute. The spreadsheet file needs be accessible by the SQL
>Server service.
>SELECT
>'EXEC sp_addlogin ''' +
> Login +
> ''', ''SomePassword'', ''' +
> DefaultDatabase + '''
>USE ' + DefaultDatabase + '
>EXEC sp_adduser ''' +
> Login + '''
>EXEC sp_addrolemember ''' +
> Role +
> ''', ''' +
> Login +
> ''''
>FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;DATABASE=c:\temp\Logins.xls',
> 'Select * from [Sheet1$]')
>
>
|||Good suggestion, Steve. FWIW, I usually the token names like the following
when using this technique but that's just a personal preference.
set @.SQL = '
EXEC sp_addlogin $(Login), $(Password), $(Database)
USE $(Database)
EXEC sp_adduser $(Login)
EXEC sp_addrolemember $(Role), $(Login)
'
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23okGIu7aEHA.3892@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Possibly less prone to error is:
> CREATE PROCEDURE Process (
> @.SomeLogin sysname,
> @.SomePassword nvarchar(200) collate Latin1_General_CS_AS,
> @.SomeDatabase sysname
> ) as
> DECLARE @.SQL nvarchar(4000)
> set @.SQL = '
> EXEC sp_addlogin $L$, $P$, $D$
> USE MyDatabase
> EXEC sp_adduser $L$
> EXEC sp_addrolemember $R$, $L$
> '
> select
> replace(replace(replace(replace(@.sql,
> '$L$',quotename(Login,'''')),
> '$P$',quotename(Password,'''')),
> '$D$',quotename(Database)),
> '$R$',role)
> from openrowset ...
> It could go wrong if any of the Excel fields contains one of
> the $x$ codes replaced later.
> Creating a 4th column in Excel is also a solution:
> ="EXEC sp_addlogin '"& A1 & "', ... and so on
> Steve Kass
> Drew University
>
> Dan Guzman wrote:

Import Logins from text or spreadsheet

Is there a way to create logins from a text file or
spreadsheet? I have a long list of users. They will be
Sql Server Authenicated. I need to
1. add login
2. set psw to generic value
3. set default database
4. add them to user defined role
Thanks,
Brian> 1. add login
> 2. set psw to generic value
> 3. set default database
4. add user to database
5. add them to user defined role
The required script template:
EXEC sp_addlogin 'SomeLogin', 'SomePassword', 'SomeDatabase'
USE MyDatabase
EXEC sp_adduser 'SomeLogin'
EXEC sp_addrolemember 'SomeRole', 'SomeLogin'
Let's assume your Excel spreadsheet has 3 columns: Login, DefaultDatabase
and Role. One method is to generate the needed script using SQL and
OPENROWSET. The query below will generate a script for each row in the
spreadsheet. You can then copy/paste the results into a Query Analyzer
window and execute. The spreadsheet file needs be accessible by the SQL
Server service.
SELECT
'EXEC sp_addlogin ''' +
Login +
''', ''SomePassword'', ''' +
DefaultDatabase + '''
USE ' + DefaultDatabase + '
EXEC sp_adduser ''' +
Login + '''
EXEC sp_addrolemember ''' +
Role +
''', ''' +
Login +
''''
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\temp\Logins.xls',
'Select * from [Sheet1$]')
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Brian" <bgroves@.medibase.com> wrote in message
news:2db5001c46b6b$7f8d35d0$a601280a@.phx.gbl...
> Is there a way to create logins from a text file or
> spreadsheet? I have a long list of users. They will be
> Sql Server Authenicated. I need to
> 1. add login
> 2. set psw to generic value
> 3. set default database
> 4. add them to user defined role
> Thanks,
> Brian|||Possibly less prone to error is:
CREATE PROCEDURE Process (
@.SomeLogin sysname,
@.SomePassword nvarchar(200) collate Latin1_General_CS_AS,
@.SomeDatabase sysname
) as
DECLARE @.SQL nvarchar(4000)
set @.SQL = '
EXEC sp_addlogin $L$, $P$, $D$
USE MyDatabase
EXEC sp_adduser $L$
EXEC sp_addrolemember $R$, $L$
'
select
replace(replace(replace(replace(@.sql,
'$L$',quotename(Login,'''')),
'$P$',quotename(Password,'''')),
'$D$',quotename(Database)),
'$R$',role)
from openrowset ...
It could go wrong if any of the Excel fields contains one of
the $x$ codes replaced later.
Creating a 4th column in Excel is also a solution:
="EXEC sp_addlogin '"& A1 & "', ... and so on
Steve Kass
Drew University
Dan Guzman wrote:
>>1. add login
>>2. set psw to generic value
>>3. set default database
>>
>4. add user to database
>5. add them to user defined role
>The required script template:
>EXEC sp_addlogin 'SomeLogin', 'SomePassword', 'SomeDatabase'
>USE MyDatabase
>EXEC sp_adduser 'SomeLogin'
>EXEC sp_addrolemember 'SomeRole', 'SomeLogin'
>Let's assume your Excel spreadsheet has 3 columns: Login, DefaultDatabase
>and Role. One method is to generate the needed script using SQL and
>OPENROWSET. The query below will generate a script for each row in the
>spreadsheet. You can then copy/paste the results into a Query Analyzer
>window and execute. The spreadsheet file needs be accessible by the SQL
>Server service.
>SELECT
>'EXEC sp_addlogin ''' +
> Login +
> ''', ''SomePassword'', ''' +
> DefaultDatabase + '''
>USE ' + DefaultDatabase + '
>EXEC sp_adduser ''' +
> Login + '''
>EXEC sp_addrolemember ''' +
> Role +
> ''', ''' +
> Login +
> ''''
>FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;DATABASE=c:\temp\Logins.xls',
> 'Select * from [Sheet1$]')
>
>|||Good suggestion, Steve. FWIW, I usually the token names like the following
when using this technique but that's just a personal preference.
set @.SQL = '
EXEC sp_addlogin $(Login), $(Password), $(Database)
USE $(Database)
EXEC sp_adduser $(Login)
EXEC sp_addrolemember $(Role), $(Login)
'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23okGIu7aEHA.3892@.TK2MSFTNGP10.phx.gbl...
> Possibly less prone to error is:
> CREATE PROCEDURE Process (
> @.SomeLogin sysname,
> @.SomePassword nvarchar(200) collate Latin1_General_CS_AS,
> @.SomeDatabase sysname
> ) as
> DECLARE @.SQL nvarchar(4000)
> set @.SQL = '
> EXEC sp_addlogin $L$, $P$, $D$
> USE MyDatabase
> EXEC sp_adduser $L$
> EXEC sp_addrolemember $R$, $L$
> '
> select
> replace(replace(replace(replace(@.sql,
> '$L$',quotename(Login,'''')),
> '$P$',quotename(Password,'''')),
> '$D$',quotename(Database)),
> '$R$',role)
> from openrowset ...
> It could go wrong if any of the Excel fields contains one of
> the $x$ codes replaced later.
> Creating a 4th column in Excel is also a solution:
> ="EXEC sp_addlogin '"& A1 & "', ... and so on
> Steve Kass
> Drew University
>
> Dan Guzman wrote:
> >>1. add login
> >>2. set psw to generic value
> >>3. set default database
> >>
> >>
> >
> >4. add user to database
> >5. add them to user defined role
> >
> >The required script template:
> >
> >EXEC sp_addlogin 'SomeLogin', 'SomePassword', 'SomeDatabase'
> >USE MyDatabase
> >EXEC sp_adduser 'SomeLogin'
> >EXEC sp_addrolemember 'SomeRole', 'SomeLogin'
> >
> >Let's assume your Excel spreadsheet has 3 columns: Login, DefaultDatabase
> >and Role. One method is to generate the needed script using SQL and
> >OPENROWSET. The query below will generate a script for each row in the
> >spreadsheet. You can then copy/paste the results into a Query Analyzer
> >window and execute. The spreadsheet file needs be accessible by the SQL
> >Server service.
> >
> >SELECT
> >'EXEC sp_addlogin ''' +
> > Login +
> > ''', ''SomePassword'', ''' +
> > DefaultDatabase + '''
> >USE ' + DefaultDatabase + '
> >EXEC sp_adduser ''' +
> > Login + '''
> >EXEC sp_addrolemember ''' +
> > Role +
> > ''', ''' +
> > Login +
> > ''''
> >FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> > 'Excel 8.0;DATABASE=c:\temp\Logins.xls',
> > 'Select * from [Sheet1$]')
> >
> >
> >

Import Logins from text or spreadsheet

Is there a way to create logins from a text file or
spreadsheet? I have a long list of users. They will be
Sql Server Authenicated. I need to
1. add login
2. set psw to generic value
3. set default database
4. add them to user defined role
Thanks,
Brian> 1. add login
> 2. set psw to generic value
> 3. set default database
4. add user to database
5. add them to user defined role
The required script template:
EXEC sp_addlogin 'SomeLogin', 'SomePassword', 'SomeDatabase'
USE MyDatabase
EXEC sp_adduser 'SomeLogin'
EXEC sp_addrolemember 'SomeRole', 'SomeLogin'
Let's assume your Excel spreadsheet has 3 columns: Login, DefaultDatabase
and Role. One method is to generate the needed script using SQL and
OPENROWSET. The query below will generate a script for each row in the
spreadsheet. You can then copy/paste the results into a Query Analyzer
window and execute. The spreadsheet file needs be accessible by the SQL
Server service.
SELECT
'EXEC sp_addlogin ''' +
Login +
''', ''SomePassword'', ''' +
DefaultDatabase + '''
USE ' + DefaultDatabase + '
EXEC sp_adduser ''' +
Login + '''
EXEC sp_addrolemember ''' +
Role +
''', ''' +
Login +
''''
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\temp\Logins.xls',
'Select * from [Sheet1$]')
Hope this helps.
Dan Guzman
SQL Server MVP
"Brian" <bgroves@.medibase.com> wrote in message
news:2db5001c46b6b$7f8d35d0$a601280a@.phx
.gbl...
> Is there a way to create logins from a text file or
> spreadsheet? I have a long list of users. They will be
> Sql Server Authenicated. I need to
> 1. add login
> 2. set psw to generic value
> 3. set default database
> 4. add them to user defined role
> Thanks,
> Brian|||Possibly less prone to error is:
CREATE PROCEDURE Process (
@.SomeLogin sysname,
@.SomePassword nvarchar(200) collate Latin1_General_CS_AS,
@.SomeDatabase sysname
) as
DECLARE @.SQL nvarchar(4000)
set @.SQL = '
EXEC sp_addlogin $L$, $P$, $D$
USE MyDatabase
EXEC sp_adduser $L$
EXEC sp_addrolemember $R$, $L$
'
select
replace(replace(replace(replace(@.sql,
'$L$',quotename(Login,'''')),
'$P$',quotename(Password,'''')),
'$D$',quotename(Database)),
'$R$',role)
from openrowset ...
It could go wrong if any of the Excel fields contains one of
the $x$ codes replaced later.
Creating a 4th column in Excel is also a solution:
="EXEC sp_addlogin '"& A1 & "', ... and so on
Steve Kass
Drew University
Dan Guzman wrote:

>4. add user to database
>5. add them to user defined role
>The required script template:
>EXEC sp_addlogin 'SomeLogin', 'SomePassword', 'SomeDatabase'
>USE MyDatabase
>EXEC sp_adduser 'SomeLogin'
>EXEC sp_addrolemember 'SomeRole', 'SomeLogin'
>Let's assume your Excel spreadsheet has 3 columns: Login, DefaultDatabase
>and Role. One method is to generate the needed script using SQL and
>OPENROWSET. The query below will generate a script for each row in the
>spreadsheet. You can then copy/paste the results into a Query Analyzer
>window and execute. The spreadsheet file needs be accessible by the SQL
>Server service.
>SELECT
>'EXEC sp_addlogin ''' +
> Login +
> ''', ''SomePassword'', ''' +
> DefaultDatabase + '''
>USE ' + DefaultDatabase + '
>EXEC sp_adduser ''' +
> Login + '''
>EXEC sp_addrolemember ''' +
> Role +
> ''', ''' +
> Login +
> ''''
>FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;DATABASE=c:\temp\Logins.xls',
> 'Select * from [Sheet1$]')
>
>|||Good suggestion, Steve. FWIW, I usually the token names like the following
when using this technique but that's just a personal preference.
set @.SQL = '
EXEC sp_addlogin $(Login), $(Password), $(Database)
USE $(Database)
EXEC sp_adduser $(Login)
EXEC sp_addrolemember $(Role), $(Login)
'
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23okGIu7aEHA.3892@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Possibly less prone to error is:
> CREATE PROCEDURE Process (
> @.SomeLogin sysname,
> @.SomePassword nvarchar(200) collate Latin1_General_CS_AS,
> @.SomeDatabase sysname
> ) as
> DECLARE @.SQL nvarchar(4000)
> set @.SQL = '
> EXEC sp_addlogin $L$, $P$, $D$
> USE MyDatabase
> EXEC sp_adduser $L$
> EXEC sp_addrolemember $R$, $L$
> '
> select
> replace(replace(replace(replace(@.sql,
> '$L$',quotename(Login,'''')),
> '$P$',quotename(Password,'''')),
> '$D$',quotename(Database)),
> '$R$',role)
> from openrowset ...
> It could go wrong if any of the Excel fields contains one of
> the $x$ codes replaced later.
> Creating a 4th column in Excel is also a solution:
> ="EXEC sp_addlogin '"& A1 & "', ... and so on
> Steve Kass
> Drew University
>
> Dan Guzman wrote:
>

import from excel to SQL Server - can it be more efficient ?

Hello All,

Iam new to the world of ASP .Net. Right now iam building an application that will IMPORT about 5,000 records from an Excel spreadsheet to a table in MS SQL Server. Right now the code works correctly, but i feel it is not efficient and takes a little bit of more time in doing the import. Could you guys throw some light on how i can make the code run more faster ? Someone suggested me that i can use DataAdapter and update the table in the database thru an update method available with it. I dont know how to do it? Could anyone share with me a snippet of code that does this ?

Here is my code:

Private Sub ProcessRecords()
Dim ds2 As New DataSet
' readExcelSheet is a user-defined function that reads a spreadsheet and returns a DataSet object
ds2 = readExcelSheet("C:\Inetpub\wwwroot\Project1\Book2.xls", "SELECT * FROM [Sheet1$]")
Dim myConnection As SqlConnection = Connection() ' user-defined function that returns a SQLConnection object
myConnection.Open()
Dim strSQL As String = "insert_member" ' stored procedure that inserts records
Dim myCommand As New SqlCommand(strSQL, myConnection)
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.Add("@.salutation", SqlDbType.NVarChar)
myCommand.Parameters.Add("@.firstname", SqlDbType.NVarChar)
myCommand.Parameters.Add("@.lastname", SqlDbType.NVarChar)
myCommand.Parameters.Add("@.company", SqlDbType.NVarChar)

Dim i, j As Integer
Response.Write(Date.Now() & "<br>")
For i = 0 To ds2.Tables("Members").Rows.Count() - 1
myCommand.Parameters("@.salutation").Value = ds2.Tables("Members").Rows(i).Item("sal")
myCommand.Parameters("@.firstname").Value = ds2.Tables("Members").Rows(i).Item("firstname")
myCommand.Parameters("@.lastname").Value = ds2.Tables("Members").Rows(i).Item("lastname")
myCommand.Parameters("@.company").Value = ds2.Tables("Members").Rows(i).Item("company")
j = myCommand.ExecuteNonQuery()
If (j > 0) Then
Response.Write("Record Inserted - " & i + 1 & "<br>")
End If
Next
Response.Write(Date.Now() & "<br>")
myConnection.Close()
End Sub

Please reply soon.
Thank You.can you show us the readExcelObject class?

i got this unspecified exception when trying to connect to an excel file:

System.Data.OleDb.OleDbException: Unspecified error at System.Data.OleDb.OleDbConnectionInternal.InitializeProvider() at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnection conn, OleDbConnectionString constr) at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object providerInfo, DbConnectionBase owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnectionBase owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnectionBase owningConnection) at System.Data.ProviderBase.DbConnectionClosed.Open(DbConnectionBase outerConnection) at System.Data.ProviderBase.DbConnectionBase.Open() at ASP.excel1_aspx.LoadExcelIntoDS() in c:\excel_dev\excel1.aspx:line 25

<%@. Page language="c#" %>
<%@. Import Namespace="System.Runtime"%>
<%@. Import Namespace="System"%>
<%@. Import Namespace="System.Data"%>
<%@. Import Namespace="System.Data.OleDb"%
<script runat=server>
void Page_Load(Object src, EventArgs e)
{
LoadExcelIntoDS();
}
void LoadExcelIntoDS()
{
OleDbConnection MyConnection = new OleDbConnection();
try
{
String conn = "Provider=Microsoft.Jet.OLEDB.4.0;";
conn += @."Data Source=C:\excel_dev\demo_banc.xls;";
conn += "Extended Properties=Excel 8.0;";
MyConnection = new OleDbConnection(conn);
MyConnection.Open();
}
catch(ArgumentException ae)
{
Response.Write(ae.ToString() + "<BR>");
}
catch(OleDbException e)
{
String err_msg = "";
for (int i = 0; i < e.Errors.Count; i++)
err_msg += "------<br>Index #" + i + "<br>Message: " + e.Errors[i].Message + "<br>NativeError: " + e.Errors[i].NativeError + "<br>Source: " + e.Errors[i].Source + "<br>SQLState: " + e.Errors[i].SQLState + "<br>";
Response.Write(err_msg + "<br>");
Response.Write(e.ToString() + "<BR>");
}
catch(Exception e)
{
Response.Write(e.ToString() + "<br>");
}
finally
{
MyConnection.Close();
}
}|||solved.

pls refer to:

http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=314899

Import from excel

I am trying to import data from an excel spreadsheet. The file comes from a client and sometimes it may not have any data, that is, it will be blank. It seems that the Excel Source in SSIS does not like empty files, is there any way around this? I suppose I can check to see if it is empty and abort the import process but I am not sure how to do this. Any other ideas on how to get around this?

mthierauf wrote:

Any other ideas on how to get around this?

you could have the package fail if the excel file is empty. in other words, you would first check the contents of the excel file, then determine how the package should proceed based whether the contents is empty or not. you could use the script task to check the contents of the excel file.|||

Thanks Duane... I was trying to do that but wasn't sure how to check for an empty excel file. I assume you would do this in a script but I am not sure what function does this. I may not even need to do this now anyway, our client is going to put a header row in the file... but I would still appreciate the code to check for an empty excel file.

|||

mthierauf wrote:

Thanks Duane... I was trying to do that but wasn't sure how to check for an empty excel file. I assume you would do this in a script but I am not sure what function does this. I may not even need to do this now anyway, our client is going to put a header row in the file... but I would still appreciate the code to check for an empty excel file.

did you ask this question on the visual studio tools for office forum?

Import Excel Spreadsheet to SQL 7

Running SQL Server 7 and Excel 2002 on an Win XP Pro machine.
I created a database with two tables, ran a program that added some data to
both tables, then used the Data Transformation Services Export Wizard to
export the tables to an Excel workbook containing each table as a separate
spreadsheet. So far, so good.
I manually made a number of changes to the spreadsheets, altering some of
the values that had been exported, adding some more rows to one of the
spreadsheets and sorting the rows. Now when I go back to SQL Server and use
the Wizard to import the data back into the tables, not all of the data
comes back in. Of about 250+ rows in one of the tables, less than 200 of
them get imported. I don't see any errors when running the wizard. I did a
TRUNCATE TABLE in query analyzer before doing the import to clear all the
rows in each table.
Is there some kind of marker to put in the file that tells the import wizard
where to stop? For example, when printing a spreadsheet, you "set" the
print area. Is there a corresponding function to set the "import" area?
Thanks
John Schneider
When you export to Excel from SQL Server a named range is created on the
Excel worksheet. In Excel select Insert on the menu and then Name. You
should see a name in the dialog box. Click on it and update the range and
then the full data set should get imported to SQL server.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Bingo! Thanks for the reply. I NEVER would have found that setting.
John
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:cP9tbKX0EHA.764@.cpmsftngxa10.phx.gbl...
> When you export to Excel from SQL Server a named range is created on the
> Excel worksheet. In Excel select Insert on the menu and then Name. You
> should see a name in the dialog box. Click on it and update the range and
> then the full data set should get imported to SQL server.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>

Import Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Hi, I'm a Student, and since a few months ago I'm learning JAVA. I'm creating an application to call and compare times. For this I create in Excel a time table which is quite big and it would be a lot of typing work to input one by one the data in each cell in SQL Server, considering that I have to create 8 more tables. I was able to retreive the data from excel usin the JXL API of JAVA but it doesn't give all the funtions to perform math operations as JDBC. That's why I need to move the tables from Excel to SQL.

I found this sitehttp://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx which gives a code to do so, but I guess that some heathers are missing or maybe I don't know which compiler to use to run that code, I would like you help to identify which compiler use to run that code or if there is some vital piece of code missing.

// Connection String to Excel Workbookstring excelConnectionString=@."Provider=Microsoft
 .Jet.OLEDB.4.0;Data Source=Book1.xls;Extended
 Properties=""Excel 8.0;HDR=YES;""";// Create Connection to Excel Workbookusing (OleDbConnection connection=
new OleDbConnection(excelConnectionString)){ OleDbCommand command=new OleDbCommand
 ("Select ID,Data FROM [Data$]", connection); connection.Open();// Create DbDataReader to Data Worksheetusing (DbDataReader dr= command.ExecuteReader()) {// SQL Server Connection Stringstring sqlConnectionString="Data Source=.;
 Initial Catalog=Test;Integrated Security=True";// Bulk Copy to SQL Serverusing (SqlBulkCopy bulkCopy=
new SqlBulkCopy(sqlConnectionString)) { bulkCopy.DestinationTableName="ExcelData"; bulkCopy.WriteToServer(dr); } }}

On the other hand in this forum I that someelse use that link but implements a totally different code which I'm not able to compile alsohttp://forums.asp.net/p/1110412/2057095.aspx#2057095. It seems this code works as I was able to read, but I do not know which language is used.

Dim excelConnectionStringAsString ="Provider=Microsoft .Jet.OLEDB.4.0;Data Source=Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;"""

' Using

Dim connectionAs OleDbConnection =New OleDbConnection(excelConnectionString)

Try

Dim commandAs OleDbCommand =New OleDbCommand("Select ID,Data FROM [Data$]", connection)

connection.Open()

' Using

Dim drAs DbDataReader = command.ExecuteReader

Try

Dim sqlConnectionStringAsString = WebConfigurationManager.ConnectionStrings("CampaignEnterpriseConnectionString").ConnectionString

' Using

Dim bulkCopyAs SqlBulkCopy =New SqlBulkCopy(sqlConnectionString)

Try

bulkCopy.DestinationTableName =

"ExcelData"

bulkCopy.WriteToServer(dr)

Finally

CType(bulkCopy, IDisposable).Dispose()

EndTry

Finally

CType(dr, IDisposable).Dispose()

EndTry

Finally

CType(connection, IDisposable).Dispose()

EndTry

Catch exAs Exception

EndTry

The Compilers I have are: Eclipse, Netbeans, MS Visual C++ Express Edition and MS Visual C# Express Edition. In MS Visual C++

Thanks for your help.

Regads,

Robert.

Hi,

From your description, it seems that you want to connect to your EXCEL table by OLDEB from your application, right?

If so, you can build your connection string in the following way, and create the oledbcommand object to execute your query statement.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ExcelFilePath;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
string SQLQuery = "SELECT * FROM [sheet1$]".
// excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.

For details, see:

http://support.microsoft.com/kb/326548 (Excel Part)

Besides, If your application runs in 64-bit mode, all of the components it uses must also be 64-bit. There is no 64-bit Jet OLE DB Provider, so you get the message described. You would receive a similar error when trying to connect to a database using OLE DB or ODBC if there is no 64-bit version of the specified OLE DB provider or ODBC driver.

http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=123311

Thanks.

|||

if you really would like be sure that your import is successfully, do not use OLE DB or any other tolls to export your data from excel. Just use excel to export data to any common format like DBase, ACCESS, flat file and next import it to place you want. It will save you a lot of problems because the best tool to read Excel data is Excel itself and do not use other tools to do it. do not use SQL server SSIS also, it use OLE DB and does not work correctly with some excel shits.

Import Excel Spreadsheet

I am using TransferSpreadsheet from an ADP file to import an Excel
spreadsheet into an MSDE database. On one computer, when I run the code to
import the spreadsheet, it prompts for an SQLServer login. The same user ca
n
try it on a different computer and it will work fine. Data in the tables of
the database can be viewed and edited on the bad computer. But the
spreadsheet cannot be imported. I even tried the Get External Data->Import
menu option and it prompts for SQLServer login also.
Any ideas on what to look at on this computer to determine the problem?
Or any suggestions on a better way to import a spreadsheet into an SQLServer
database.?
Thanks!
JerryJerry..
you have to tell us what is the error that you get when you import.
What I usually do (if its only a few thousand rows to import), then I go
forr opendatasource(ODS). Check out in BOL for ODS and they give an example
of select from excel. U can then insert into the table in SQL Server.
But to investigate on why this error is occuring, you need to give us more
info on why its not getting imported.
--
"JerryWendell" wrote:

> I am using TransferSpreadsheet from an ADP file to import an Excel
> spreadsheet into an MSDE database. On one computer, when I run the code t
o
> import the spreadsheet, it prompts for an SQLServer login. The same user
can
> try it on a different computer and it will work fine. Data in the tables
of
> the database can be viewed and edited on the bad computer. But the
> spreadsheet cannot be imported. I even tried the Get External Data->Impor
t
> menu option and it prompts for SQLServer login also.
> Any ideas on what to look at on this computer to determine the problem?
> Or any suggestions on a better way to import a spreadsheet into an SQLServ
er
> database.?
> Thanks!
> Jerry
>|||Thanks for the responds.
This only happens on one particular computer. It works fine on the 8 other
computers that access the database. They are all running Access 2003 and
Windows XP.
When I try to import the spreadsheet, I am prompted to enter an SQL Server
Login. When I enter a valid login (I know it is a valid login because it
works from the other computers) after about a minute I get the following
error messages:
Connection Failed
SQLState: '01S00'
SQLServerError: 0
[Microsoft][ODBC SQLServerDriver][dbnmpntw]Invalid Connection String Attribute
SQLState: '01000'
SQLServerError: 2
[Microsoft][ODBC SQLServerDriver][dbnmpntw]ConnectionOpen
(CreateFile())
SQLState: '08001'
SQLServerError: 6
[Microsoft][ODBC SQLServerDriver][dbnmpntw]Specified SQL Server not found
Thanks for your help.
Jerry
"Omnibuzz" wrote:
> Jerry..
> you have to tell us what is the error that you get when you import.
> What I usually do (if its only a few thousand rows to import), then I go
> forr opendatasource(ODS). Check out in BOL for ODS and they give an exampl
e
> of select from excel. U can then insert into the table in SQL Server.
> But to investigate on why this error is occuring, you need to give us more
> info on why its not getting imported.
> --
>
>
> "JerryWendell" wrote:
>sql

import excel

need some help with getting this stored procedure working.
I'm trying to import excel spreadsheet into an existing table
getting error message of syntax near "*"
===========================================
CREATE PROCEDURE [dbo].importExcel
AS
--Create linked server
EXEC sp_addlinkedserver 'ExcelSource', 'Jet
4.0','Microsoft.Jet.OLEDB.4.0','C:\temp\tables.xls ', NULL,'Excel 5.0'
GO
--login to linked server
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL
GO
-- import spreadsheet data into database table...
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
DECLARE @.SQLString1 VARCHAR(100)
SET @.SQLString1 = 'SELECT * FROM ExcelSource...[table$]'
DECLARE @.SQLString2 VARCHAR(100)
SET @.SQLString2 = 'INSERT INTO table ' + @.SQLString1
-- convert variable from VARCHAR to NVARCHAR
DECLARE @.S2 NVARCHAR(1000)
SET @.S2 = CAST(@.SQLString2 as NVarchar(1000))
EXECUTE sp_executesql @.S2
GO
EXEC sp_dropserver 'ExcelSource', 'droplogins'
GO
hi,
"TJS" <nospam@.here.com> ha scritto nel messaggio
news:10qlms38norp6e3@.corp.supernews.com
> need some help with getting this stored procedure working.
> I'm trying to import excel spreadsheet into an existing table
> getting error message of syntax near "*"
> SET @.SQLString1 = 'SELECT * FROM ExcelSource...[table$]'
what is the name of the Excel sheet?
usually it defaults to Sheet1$, so the correct syntax is
SET @.SQLString1 = 'SELECT * FROM ExcelSource...[Sheet1$]'
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||the name is correct , I am only testing at this time.
I did remove the extra GO statements and that seems to have resolved the
error messages.
I now have to find an ASP file to dynamically generate the sql statements
for each spreadsheet table and pass them into the stored procedure because
the live database tables have primary keys. That unfortunately requires
using column lists...
I can't believe somebody hasn't already done all this ?
sql

Import Data to SQL server from Excel spreadsheet

Hi all,

Firstly, i'm new to integration services and have only done a little with DTS jobs.

I'm trying to create an integration services project which will import data from an two worksheets in an Excel spreadsheet to two different tables in a database. I'm looking at only one table at present to make things a little more understandable.

One stipulation i have is that i need to be able to specify a variable value and insert that as an additional column in the database. I have and Excel source and a SQL destination both of which have been set up with there specific connection managers. I also have a variable which i add in using the derived column task.

When i try to debug this i am getting a few problems. I think these may be to do with the fact that although the worksheet in Excel has 20 rows (1st column shows these numbers) i only want those rows with data in them. If i preview the excel table it shows all the rows including those with null columns. Is there some sort of way that i can only get the rows that have data in the columns after the row number. I.e. can i select rows that do not have a second column value = to NULL.

I hope this makes sense and that someone can help me out with this problem.

All help is greatly appreciated.

Cheers,

Grant

P.S.

Apologies. I have this resolved now. I didn't see the option to use a SQL command as apposed to a table or view when setting up the Excel source.

I am still however getting the following errors which i'd appreciate some help on:

Error: 0xC0202009 at Data Flow Task, Excel Source [1]: An OLE DB error has occurred. Error code: 0x80040E21.
Error: 0xC0208265 at Data Flow Task, Excel Source [1]: Failed to retrieve long data for column "Rework Entry Information (BE SPECIFIC)".
Error: 0xC020901C at Data Flow Task, Excel Source [1]: There was an error with output column "Rework Entry Information" (170) on output "Excel Source Output" (9). The column status returned was: "DBSTATUS_UNAVAILABLE".
Error: 0xC0209029 at Data Flow Task, Excel Source [1]: The "output column "Rework Entry Information" (170)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "Rework Entry Information" (170)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "Excel Source" (1) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

Any help on this would be greatly appreciated.

GrantI'd also like to know how to go about specifying a variable as the datasource for my Excel connection. This is so that at runtime i can specify a number of different files to process.

Thank you,

Grant|||

You can use a ForEach loop container in your control flow to go through all files in a specific file system folder; then inside of that ForEach container add a dataflow task that does what you want. You may need to use an expression to change the connection string of your Excel Connection manager for every iteration (using the variable that has the collection value). I have never tried that before; this is just an idea.

good luck!

Rafael Salas

|||I had a similar problem importing into a SQL Server 2000 database with SQL Managment Studio. If you're using SQL 2000, try using the appropriate version of Enterprise Manager.|||I had a similar problem importing into a SQL Server 2000 database with SQL Managment Studio for SQL Server 2005. If you're using SQL Server 2000, try using the appropriate version of Enterprise Manager.

Import Data in SQL Server 2005?

I'm using SQL Server 2005 Management Studio Express, and trying to import data from an Excel spreadsheet into a new table. I'm used to doing this through Enterprise Manager in SQL Server 2000, but I'm not sure how to do it in the new interface. Is there a way to do a simple data import without also installing Business Intelligence Development Studio?

hi,

nope..

all this "stuff" has now be included in SSIS (integration service), not available in SSMSE as SQLExpress does not include the required runtime at all..

but you can perhaps interact with "linked servers" scenario and perform INSERT ... SELECT ...

something like http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=670748&SiteID=1

regards

|||Thank you!

Import data from Excel?

Hello,

I have an Excel spreadsheet that I am loading data from and I want to prevent SSIS from making assumptions about the data contained within the spreadsheed and to just treat every column as a string (i.e. Unicode string [DT_WSTR]). How is this done?

I know I could do a conversion once the data is loaded, but I am wondering if there is a way to specify this in the Excel Source settings without having to add a Data Conversion task to the Data Flow.

TIA...

As far as I know, this cannot be done. You are allowed to set data types in Flat File Sources and delimit them by a comma, though.

Maybe you could export the excel file to comma delimited and then import as a Flat File.

Just a suggestion,

Mark

https://spaces.msn.com/mgarnerbi

|||

You could experiment with the following settings:

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes