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 Excel with varying worksheet name using DTS

Hi Everyone,

I'm trying to create a DTS package that will let me import an Excel file. The user will be able to name the file the same name every time. But can the DTS package read a different worksheet name each time? Right now, if I use the Excel connection object in DTS designer, it wants to hard code the worksheet name.

Thanks,

Eric

You might want to post in the DTS forum:

http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.dts

import excel file into dropdownlist then export to sql server 2005

i am handling a project where user can choose the excel file and the field in the excel file to export into sql server 2005. which mean there will be dropdownlist where the user can choose the field and so on. anyone know how to do this?I'm a bit uncertain how this question is Asp.Net related. Please clarify how it is.|||which mean i will need to import the excel file into a dataset and then bind the dataset to the drop down list and at last import it to the sql server 2005. it can be in vb.net or asp.net

Import Design Question

I have at least three tables into which the user can import data.
The three tables are 1) Portfolio 2) Account 3) Transactions. The user
will provide us three files - one for each entity (in the file) related
by a userdefined NUMBER field.
These three tables are linked by foreign keys but the key columns in
the parent table are IDENTITY columns (PORTFOLIOID, ACCOUNTID and
TRANSACTIONID).
The files can be really big so the import has to be quick. Right now I
first insert the rows into PORTFOLIO, get the generated IDs , loop
through the ACCOUNT records and set the PORTFOLIO IDs. Then insert
ACCOUNTs , get the IDs, loop through TRANSACTIONS and set the ACCOUNT
ID and then import the TRANSACTIONS. This process of retreiving and
setting the IDs is consuming a long time and making the import very
slow. I am wondering if using IDENTITY is the right design for the
above mentioned tables. Is it better to generate the key values myself
using a SEED table?
Is there a more elegant solution to the problem?
Thanks.Looping? In SQL?
Don't you have any natural keys in the source files? If there is a natural
key (or at least one or more candidate columns) use that to achieve a
set-based solution.
Whether you use identity or any other kind of key generation is IMHO
irrelevant, since there really should be a more natural way to uniquely
identify each row of data (i.e. a natural relationship between the sets).
ML
http://milambda.blogspot.com/|||S Chapman wrote:
> I have at least three tables into which the user can import data.
> The three tables are 1) Portfolio 2) Account 3) Transactions. The user
> will provide us three files - one for each entity (in the file) related
> by a userdefined NUMBER field.
> These three tables are linked by foreign keys but the key columns in
> the parent table are IDENTITY columns (PORTFOLIOID, ACCOUNTID and
> TRANSACTIONID).
> The files can be really big so the import has to be quick. Right now I
> first insert the rows into PORTFOLIO, get the generated IDs , loop
> through the ACCOUNT records and set the PORTFOLIO IDs. Then insert
> ACCOUNTs , get the IDs, loop through TRANSACTIONS and set the ACCOUNT
> ID and then import the TRANSACTIONS. This process of retreiving and
> setting the IDs is consuming a long time and making the import very
> slow. I am wondering if using IDENTITY is the right design for the
> above mentioned tables. Is it better to generate the key values myself
> using a SEED table?
> Is there a more elegant solution to the problem?
> Thanks.
>
Sounds like your relationships are like this:
Portfolio -> AccountID -> TransactionID
meaning a single portfolio links to multiple Account ID's, a single
account links to multiple Transaction ID's.
You might consider changing this. Look for natural keys to link on
instead of manufacturing one (using an ID value). For instance, a
transaction should link to an account via ACCOUNT NUMBER. A portfolio
should contain multiple ACCOUNT NUMBERS, not account ID's.
What this will allow you to do is to bulk import all three tables
without having to mess with finding ID's, updating ID's, etc. The data
is already naturally linked together.|||Unfortunately there are no natural keys. The Portfolio Number and
Account Number can be gauranteed to be unique only within a batch.
Also, I am NOT looping through in Sql, looping through rows in the
dataset inside the program.
Tracy McKibben wrote:
> S Chapman wrote:
> Sounds like your relationships are like this:
> Portfolio -> AccountID -> TransactionID
> meaning a single portfolio links to multiple Account ID's, a single
> account links to multiple Transaction ID's.
> You might consider changing this. Look for natural keys to link on
> instead of manufacturing one (using an ID value). For instance, a
> transaction should link to an account via ACCOUNT NUMBER. A portfolio
> should contain multiple ACCOUNT NUMBERS, not account ID's.
> What this will allow you to do is to bulk import all three tables
> without having to mess with finding ID's, updating ID's, etc. The data
> is already naturally linked together.

import data properly from csv file.

I need to extract data from a csv file, validate it, and populate other
tables with that data for a multi user web application.
I am importing a csv file via linked servers as follows:
EXEC('SELECT * into ##temptbl FROM '+@.linked_server + '...['+@.file + '#' +
@.extension + ']')
Once data gets into ##temptbl then I do proper validation and populate other
tables.
This will not work if there are other users importing the file as well
because of global temp table ##temptbl.
Do I create a separate physical table to populate and delete based on
certain criteria for that user?
I tried using table variable inside the dynamic sql but did not work. So my
best bet for now is
to have a physical table, populate it for certain criteria, do validation,
and populate other permanent tables. After
successful population I would go ahead and delete rows this temporary
staging for certain criteria.
Does this make sense or this approach stinks?
TIA...I would really appreciate if any guru/expert could address this.
TIA...
"sqlster" wrote:

> I need to extract data from a csv file, validate it, and populate other
> tables with that data for a multi user web application.
> I am importing a csv file via linked servers as follows:
> EXEC('SELECT * into ##temptbl FROM '+@.linked_server + '...['+@.file + '#' +
> @.extension + ']')
> Once data gets into ##temptbl then I do proper validation and populate oth
er
> tables.
> This will not work if there are other users importing the file as well
> because of global temp table ##temptbl.
> Do I create a separate physical table to populate and delete based on
> certain criteria for that user?
> I tried using table variable inside the dynamic sql but did not work. So m
y
> best bet for now is
> to have a physical table, populate it for certain criteria, do validation,
> and populate other permanent tables. After
> successful population I would go ahead and delete rows this temporary
> staging for certain criteria.
> Does this make sense or this approach stinks?
> TIA...

Import Data Problem

Hi all...
I am trying to copy all tables, views, and stored procedures (except for only
one table the user does not want copied) from a database on a remote server to
another database on a local server. Every time I try to run this copy procedure,
it runs for a while and the throws the following error:
ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
'Foreign_Key_Name'. The conflict occurred in database 'LocalDB', table
'Local_Table_Name'.
The source tables have multiple relationships defined to multiple fields in each
table, and I think this is where it is failing.
What can I do to work around this to get the object copied?
Thanks!
Do you get this error when you try to create the script or when you run the
script?
If you get it when you run the script you have a dependency problem. You
will have to investigate when the problem occurs and order these elements
of your script accordingly.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:%235fjmbdFFHA.3616@.TK2MSFTNGP10.phx.gbl...
> Hi all...
> I am trying to copy all tables, views, and stored procedures (except for
only
> one table the user does not want copied) from a database on a remote
server to
> another database on a local server. Every time I try to run this copy
procedure,
> it runs for a while and the throws the following error:
> ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
> 'Foreign_Key_Name'. The conflict occurred in database 'LocalDB', table
> 'Local_Table_Name'.
> The source tables have multiple relationships defined to multiple fields
in each
> table, and I think this is where it is failing.
> What can I do to work around this to get the object copied?
> Thanks!
|||I just modify and click through the Import Data Wizard, then click the button to
make it go, and sit back and wait. It shows progress, then after a loooong while
(because this is a large database), the error I described pops up. I also have a
DTS package built that I can alter and run. It only has the one task "Copy SQL
Server Objects". So, I am not quite sure what you mean by "order these elements."
Hilary Cotter wrote:
> Do you get this error when you try to create the script or when you run the
> script?
> If you get it when you run the script you have a dependency problem. You
> will have to investigate when the problem occurs and order these elements
> of your script accordingly.
>
|||take the problem table out and try to DTS it again, unfortunately this will
mean you are starting from scratch again. So you might want to find out what
tables have been successfully exported and then only DTS the tables which
are missing.
You might want also want to look at backing up the database and restoring it
on the destination server.
The problem is SQL Server uses a table called sysdepends to track
dependencies. This table can get out of sync by various database
operations - like alter proc statements.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:exNXBHfFFHA.4072@.TK2MSFTNGP10.phx.gbl...
> I just modify and click through the Import Data Wizard, then click the
button to
> make it go, and sit back and wait. It shows progress, then after a loooong
while
> (because this is a large database), the error I described pops up. I also
have a
> DTS package built that I can alter and run. It only has the one task "Copy
SQL
> Server Objects". So, I am not quite sure what you mean by "order these
elements."[vbcol=seagreen]
> Hilary Cotter wrote:
the[vbcol=seagreen]
elements[vbcol=seagreen]

Import Data from SPSS (Statistical Package for the Social Sciences

hi to everyone. I'm developing a web application using C#.NET and MS SQL
Server 2000 database backend. But the user wants to transfer a huge data
from his SPSS application because it will take large amount of time to input
each of the item. My question is: Is there a way to import data from SPSS
application to the database using MS SQL Server 2000.
Specifications:
Import Data From a data file (SPSS Standard Version 11.0.0)
Hope you'll reply as soon as possible. You idea will greatly help me.
Thank you and God Bless.Hi
If this information is in a reasonably formatted file you can use BULK
INSERT command, DTS or the BCP utility to import the information. This can
be scheduled as a job and you can archive the file once loaded.
There is plenty of information on BULK INSERT, DTS and BCP in Books Online,
also check out http://www.sqldts.com/ for more DTS articles such as
http://www.sqldts.com/default.aspx?231
and http://www.sqldts.com/default.aspx?246
You will need some method of loading the data file onto your server or
somewhere accessable from the server. It may be necessary to hold the data
in a staging table(s) if it requires additional work before loading into
your destination table(s).
John
"rolly-hubport" <rollyhubport@.discussions.microsoft.com> wrote in message
news:231A4F51-B185-44D8-AE52-730275EC3C21@.microsoft.com...
> hi to everyone. I'm developing a web application using C#.NET and MS SQL
> Server 2000 database backend. But the user wants to transfer a huge data
> from his SPSS application because it will take large amount of time to
> input
> each of the item. My question is: Is there a way to import data from SPSS
> application to the database using MS SQL Server 2000.
> Specifications:
> Import Data From a data file (SPSS Standard Version 11.0.0)
>
> Hope you'll reply as soon as possible. You idea will greatly help me.
> Thank you and God Bless.
>
>|||Hi,
If u will use BCP or Bulk insert u have to manually define the table
name.
use dts package tranfer data from one server to another and u have
option to select the tables.
hope this helps u
from
doller