IMPORT Multiple CSV Files to SQLSERVER Table

Dear All,

I am importing all the files from a particular folder to a table on my database KB. It is working perfectly if i use it on the same system where the DB exists and not working from the network.

USE TESTDB

--Table Creation Starts here

Create table Account([ID] int IDENTITY PRIMARY KEY, Name Varchar(100),
AccountNo varchar(100), Balance money)

Create table logtable (id int identity(1,1),
Query varchar(1000),
Importeddate datetime default getdate())

--Table Creation ends here

--Stored Procedure Starts here

Create procedure usp_ImportMultipleFiles @.filepath varchar(500),
@.pattern varchar(100), @.TableName varchar(128)
as
set quoted_identifier off
declare @.query varchar(1000)
declare @.max1 int
declare @.count1 int
Declare @.filename varchar(100)
set @.count1 =0
create table #x (name varchar(200))
set @.query ='master.dbo.xp_cmdshell "dir '+@.filepath+@.pattern +' /b"'
insert #x exec (@.query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x
drop table #x
set @.max1 = (select max(ID) from #y)
--print @.max1
--print @.count1
While @.count1 <= @.max1
begin
set @.count1=@.count1+1
set @.filename = (select name from #y where [id] = @.count1)
set @.query ='BULK INSERT '+ @.Tablename + ' FROM "'+ @.Filepath+@.Filename+'"
WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n")'
--print @.query
exec (@.query)
insert into logtable (query) select @.query
end

drop table #y

--sp ends here

Exec usp_ImportMultipleFiles 'c:\myimport\', '*.csv', 'Account'

If i use the above Exec like

Exec usp_ImportMultipleFiles '\\kb-02\C$\MyImport\', '*.csv', 'Account'
I am getting the following error:

Could not bulk insert because file '\\kb-02\C$\MyImport\Access is denied.' could not be opened.
Operating system error code 5(Access is denied.).

C Drive and MyImport folder is shared on system kb-02

Would appreciate your valuable HELP.

thanking your valuable help in advance.
K006BMy guess would be that the NT Login being used by your SQL Server service doesn't have access to \\kb-02\c$ (which is a good thing). Try creating an explicit share and giving permission to the appropriate NT Login.

-PatP|||After SP3 the security context of the user executing XP_CMDSHELL is validated before it's executed in the context of SQL Server service account. Also, if the service is running under Local System, then NO NETWORK ACCESS IS ALLOWED, period. The service needs to run under a Domain User account, and the user that executes the XP_CMDSHELL needs to have sysadmin permission to successfully complete the operation. There is a way to avoid this by creating a scheduled task and then invoking it with sp_start_job. This also requires SQLAgent service to run under Domain Users account with WRITE privileges to the share, but does not require the invoking user to have anything special, - just EXECUTE permission to sp_start_job which is given to PUBLIC by default.

Import MS-Excel to SQL-Server

Greetings All,

I have a excel file which is originally a sqlserver table that was
exported as a excel file. I have added more data to this excel file
and now want to import it again to its original table,i.e, it will
overwrite current data in the table but with no change in the schema.
How should I handle the issue of PKs in the current table that will be
over-written. I know sqlserver dose not adjust PKs when data is
over-written, like my case.

MTIA,
Grawsha<grawsha2000@.yahoo.com> wrote in message
news:1108999543.155717.259460@.o13g2000cwo.googlegr oups.com...
> Greetings All,
> I have a excel file which is originally a sqlserver table that was
> exported as a excel file. I have added more data to this excel file
> and now want to import it again to its original table,i.e, it will
> overwrite current data in the table but with no change in the schema.
> How should I handle the issue of PKs in the current table that will be
> over-written. I know sqlserver dose not adjust PKs when data is
> over-written, like my case.
> MTIA,
> Grawsha

I'm not sure what you mean by "overwritten" - do you mean you want to delete
all rows from the table, then load the .xls? If so, just use DELETE or
TRUNCATE TABLE to empty the table before loading the file. If you mean
something else, then you should give some more details, preferably including
the CREATE TABLE statement for your table, so we can see what your key
actually is.

Simon|||Simon

Yes I want to delete the all rows from the table (employees). But the
problem is there are a PK (Person ID) that I use to relate it to a FK
in another table (sales) . I would assume the Enerprise Manager would
give an error if I try to delete the rows.

My question is, should I remove the relation first, delete the rows,
load the file and then create the relation again?

Grawsha|||If your target table is referenced by a foreign key from another table,
you'll get an error if you delete from it, unless you created the key
with ON DELETE CASCADE (assuming MSSQL 2000 - you didn't mention which
version you have), in which case the referencing rows will be deleted
also.

As you suggest, you can load the data by dropping the foreign key then
recreating it, but this will leave orphaned rows in the referencing
table, unless your .xls contains data for all the current rows in the
target table. It isn't clear from your description if your .xls
contains updated rows for existing PK values, or if it contains
entirely new PK values and rows, or both. You might want to consider
another approach, which is to create a staging table with the same
structure as your target table, load the .xls into it, then INSERT and
UPDATE the data in the target table - this may be closer to what you
really need.

/* Add new rows */
insert into dbo.Target (col1, col2, ...)
from dbo.Staging s
where not exists (select * from dbo.Target t
where s.PK = t.PK)

/* Update values for existing rows */
update dbo.Target
set col1 = s.col1, col2 = s.col2, ...
from dbo.Staging s
join dbo.Target t
on s.PK = t.PK

If this doesn't help, I suggest that you post CREATE TABLE statements
for your tables, INSERT for some sample data, and then a few rows of
data from your .xls, to show what your data looks like and what you
expect to happen - descriptions by themselves are usually unclear.

http://www.aspfaq.com/etiquette.asp?id=5006

Simon

Import MDF SQL 2000 file into SQL 2005 Express

Is it possible?
I only have the MDF from my SQL 2000 DB, and I need to import it to SQL
Server 2005...
Best Regards
Fabio CavassiniYou might try CREATE DATABASE...FOR ATTACH_REBUILD_LOG. This should work if
the database was properly detached from the SQL 2000 instance. See the SQL
2005 Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Fabio Cavassini" <cavassinif@.gmail.com> wrote in message
news:1138068510.765889.178750@.g44g2000cwa.googlegroups.com...
> Is it possible?
> I only have the MDF from my SQL 2000 DB, and I need to import it to SQL
> Server 2005...
> Best Regards
> Fabio Cavassini
>|||Thanks Dan
I tried to attach it with sp_attach_db and it works...it converts the
format to the new version.
After that I got the following error when I want to create a Diagram:
"Database diagram support objects cannot be installed because this
database does not have a valid owner. To continue, first use the Files
page of the Database Properties dialog box or the ALTER AUTHORIZATION
statement to set the database owner to a valid login, then add the
database diagram support objects."
that this code fix...
EXEC sp_dbcmptlevel 'yourDB', '90';
go
ALTER AUTHORIZATION ON DATABASE::yourDB TO "yourLogin"
go
use [yourDB]
go
EXECUTE AS USER = N'dbo' REVERT
go
Best Regards
Fabio Cavassini|||> I tried to attach it with sp_attach_db and it works...it converts the
> format to the new version.
Just like the SQL 2000 version, sp_attach_db is basically just a wrapper for
CREATE DATABASE...FOR ATTACH. I don't recommend it in SQL 2005 because it
will be discontinued in a future version so you might as well get used to it
(or use a GUI that does this for you). From the SQL Server 2005 Books
Online:
<Excerpt
href="ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/59bc993e-7913-4091
-89cb-d2871cffda95.htm">
Important:
This feature will be removed in a future version of Microsoft SQL Server.
Avoid using this feature in new development work, and plan to modify
applications that currently use this feature. We recommend that you use
CREATE DATABASE database_name FOR ATTACH instead. For more information, see
CREATE DATABASE (Transact-SQL).
</Excerpt>
Hope this helps.
Dan Guzman
SQL Server MVP
"Fabio Cavassini" <cavassinif@.gmail.com> wrote in message
news:1138070344.185793.284520@.f14g2000cwb.googlegroups.com...
> Thanks Dan
> I tried to attach it with sp_attach_db and it works...it converts the
> format to the new version.
> After that I got the following error when I want to create a Diagram:
> "Database diagram support objects cannot be installed because this
> database does not have a valid owner. To continue, first use the Files
> page of the Database Properties dialog box or the ALTER AUTHORIZATION
> statement to set the database owner to a valid login, then add the
> database diagram support objects."
> that this code fix...
> EXEC sp_dbcmptlevel 'yourDB', '90';
> go
> ALTER AUTHORIZATION ON DATABASE::yourDB TO "yourLogin"
> go
> use [yourDB]
> go
> EXECUTE AS USER = N'dbo' REVERT
> go
> Best Regards
> Fabio Cavassini
>|||Thanks for the info Dan, I'll consider it.
Best Regards
Fabio Cavassinisql

Import mdf file to server

Hi.
I have my *.mdf and *.ldf file and need to import it to my new sqlserver 2000.
Can anybody tell me how to do it
BEST REGARDS
BadleifEasy, you only need to attach the database. You can use the sp_attach_db system prodedure or go with the enterprise manager: right click on

your_server_name/databases

and the select "Attach Database"

and it's finished.

Import HTML and Tab delimited into SQL

Hi,
I need to import files from either HTML or Tab delimited format into SQL
Server. I imagine this would involve DTS and/or a DSN, but I'm not sure of
the details. I'm able to standardize the filename and directory using code,
so that's not a problem. Any ideas?
HTML? I don't think so. Tab delimited, you bet.
Yes, you would set up a DTS packages using the
Import / Export wizard.
If you want to script this and run it, check out this
VBScript code sample:
http://www.eggheadcafe.com/articles/20030923.asp
2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
http://www.mastervb.net
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:C603F79A-3654-4785-B144-84909C69473E@.microsoft.com...
> Hi,
> I need to import files from either HTML or Tab delimited format into SQL
> Server. I imagine this would involve DTS and/or a DSN, but I'm not sure of
> the details. I'm able to standardize the filename and directory using
> code,
> so that's not a problem. Any ideas?

Import DTS file to SQLServer 7.0

Hy,
Do you know how I could import a .dts file to SqlServer 7.0
Thanks a lot.
Best regards.
PierreIn EM, right click on Data Transformation Services and use the open package. i think it doesn't matter if you use 7.0 or 2000 (vice versa).|||Thank you! ;)sql

import data with different collation properties

Hi All,
I have to restore a database from a different collation name to my sql
server 2000 sp3.
This database has index, primary keys and so on.
After I restore the database if I try to do a relation I have an error.
Also if I try alter table with a new collation doesn't work because the
objects(index, ...)
How can I fix this? How can I import this database with my default
collation?
Tks
JohnnyYou can use COLLATE clause:
select 1
where
'Alejandro' collate SQL_Latin1_General_CP1_CS_AS = 'ALEJANDRO'
select 1
where
'Alejandro' collate SQL_Latin1_General_CP1_CI_AS = 'ALEJANDRO'
go
or you can dump the data into files, recreate your db with a new collation,
and load the data again (Uhhhh!!!).
AMB
"JFB" wrote:

> Hi All,
> I have to restore a database from a different collation name to my sql
> server 2000 sp3.
> This database has index, primary keys and so on.
> After I restore the database if I try to do a relation I have an error.
> Also if I try alter table with a new collation doesn't work because the
> objects(index, ...)
> How can I fix this? How can I import this database with my default
> collation?
> Tks
> Johnny
>
>|||Tks for you reply Alejandro,
uauuu.... It's not an easy way to change the db collation?
I tought this is an easy problem :)
JFB
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:1FF88CBD-9B30-45B1-A637-A1E7ED22D226@.microsoft.com...
> You can use COLLATE clause:
> select 1
> where
> 'Alejandro' collate SQL_Latin1_General_CP1_CS_AS = 'ALEJANDRO'
> select 1
> where
> 'Alejandro' collate SQL_Latin1_General_CP1_CI_AS = 'ALEJANDRO'
> go
> or you can dump the data into files, recreate your db with a new
> collation,
> and load the data again (Uhhhh!!!).
>
> AMB
>
> "JFB" wrote:
>

import data from excel to SQLserver

Hi all!

I need to import data from excel file to SQLserver. What is the best way to do this?
Please give as much explanations as possible (code example would be very-very helpful).

Any ideas are wellcome.
Thanks.The best way to do this is DTS = Data Transformation Service. Use the Wizards in SQL Server. It can't be more simple than that.

Greetings,

Mark|||Mark,

Thank you for your answer.
How it could be done if the server is on a remote computer? I use some hosting service - is it still possible to use DTS or something like this?

Sorry, if my problem is too simple for you. I am new to .NET, so everything is still difficult to me.

Thanks for help.|||Hi

Another alternative, is not the best one, very raw solution ...

write the script to connect to excel, read from excel and insert into sql server ...

import data from excel to SqlServer

Hello,

I want to import data from an excel sheet to SqlServer...
I use a linked server...
I execute the following code:

EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\MyExcel.xls',NULL,
'Excel 5.0'
GO

sp_addlinkedsrvlogin N'ExcelSource', false, sa, N'ADMIN', NULL
GO

SELECT * FROM ExcelSource...Sheet1$
GO

and I get the error:

Server: Msg 7314, Level 16, State 1, Line 2
OLE DB provider 'ExcelSource' does not contain table 'Sheet1$'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='ExcelSource', TableName='Sheet1$'].

When I execute the command:

select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\book1.xls',Sheet1$)

I get the same error...

Can anyone help me?

Thanks
KorinaTry using OPENQUERY.

SELECT * FROM OPENQUERY('ExcelSource','SELECT * FROM SHEET1$')|||I get the error:

Server: Msg 7403, Level 16, State 1, Line 2
Could not locate registry entry for OLE DB provider 'c:\book1.xls'.
OLE DB error trace [Non-interface error: Provider not registered.].

What I am doing wrong?|||Make the following changes.

sp_addlinkedserver 'ExcelSource6',
'Excel',
'Microsoft.Jet.OLEDB.4.0',
'c:\MyExcel.xls',
NULL,
'Excel 8.0'

SELECT * FROM OPENQUERY(ExcelSource6,'SELECT * FROM [Sheet1$]')|||and now I get the error:

Server: Msg 7399, Level 16, State 1, Line 8
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].|||Mine works. A couple of questions for you.

Do you or someone else or another program have the file open?

Do you realize the path you define (c:\MyExcel.xls) is relative to the server and not to your client machine? This the servers C:\ drive.|||The file is close and it is placed on the specified server drive...
Do you have any other idea?

I would be gratefull because I need it as soon as possible.

Thanks|||Take a look at this:

http://support.microsoft.com/default.aspx?scid=314530

Ahhh Google.... Who needs to know anything anymore? programming before high speed internet access was such a pain.

Import data from a web site

I'm trying to use 2005 Integration Services to import data from a web address into a SQLServer 2005 database.

The address I want to download data from is http://www.nymerc.com/futures/innf.txt

I'm not sure how I am supposed to access the data on the website. What kind of connection manager do I use? Flat File? HTTP? When I try to use a flat file connection manager, I set the connection string to 'http://www.nymerc.com/futures/innf.txt', but when I click OK, the connection string gets changed to 'c:\documents and settings\....\Temporary Internet Files\Content.IE5\V01H744)\innf.txt'

Is this expected?

What's the best practice for using a web page as a data source?

Hi ECDOK,

Can you get FTP acess to this site? That would be one way to gain access to it using SSIS.

The only other approach I can think of is to use a Script Task and get this data as an HttpStream.

Andy

import a backup to SqlServer 2005

Hi,
this might be a pretty ovious question but anyhow
I created a backup file of my Databases in SQLserver 2000
How can i load these into SqlSErver 2005 that I just installed
I always seem to get errors...
thxYes, it is possible to restore a SQL 2000 backup to SQL 2005. The database
will be upgraded to 2005 during the process. What errors are you getting?
Consider using the SQL Server 2005 Upgrade Adviser to analyze the
database(s) before the upgrade. SQL 2005 is a major release and some
applications may be affected by breaking changes. Be sure to peruse the
Upgrading to SQL Server 2005 topic in the Books Online
<ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/f7e79c63-875a-446c-9860
-439486928ba1.htm>.
Hope this helps.
Dan Guzman
SQL Server MVP
"benoit" <benoit@.discussions.microsoft.com> wrote in message
news:0E4CAC91-B775-4B1E-A256-B1652479CE5C@.microsoft.com...
> Hi,
> this might be a pretty ovious question but anyhow
> I created a backup file of my Databases in SQLserver 2000
> How can i load these into SqlSErver 2005 that I just installed
> I always seem to get errors...
> thx|||Thx
in meantime i got it working
I was getting the error that i was restoring the data from another database
and that it could not work
but by overwriting the destination DB it worked fine and all data is fully
operational
thanx anyway..
"Dan Guzman" wrote:

> Yes, it is possible to restore a SQL 2000 backup to SQL 2005. The databas
e
> will be upgraded to 2005 during the process. What errors are you getting?
> Consider using the SQL Server 2005 Upgrade Adviser to analyze the
> database(s) before the upgrade. SQL 2005 is a major release and some
> applications may be affected by breaking changes. Be sure to peruse the
> Upgrading to SQL Server 2005 topic in the Books Online
> <ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/f7e79c63-875a-446c-98
60-439486928ba1.htm>.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "benoit" <benoit@.discussions.microsoft.com> wrote in message
> news:0E4CAC91-B775-4B1E-A256-B1652479CE5C@.microsoft.com...
>
>