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 from postgres

I'm trying to work with a web designer to get data copied from their system to ours (SQL Server 2000). She's giving me a file in what she called postgres format, which I've never worked with. I can get the CREATE TABLE statements to work with some tweaking, but I can't get the actual data. She's giving me a text file that looks like:

COPY manager (id, name, username, "password", access_level, affiliate) FROM stdin;
1 SunMie sunmie pw 5
2 Manager manager pw 5
\.

which I can't get Query Analyzer to get happy with. Am I missing something, or does she need to provide me with the data in a different format?if you have all the tables created but no data in them, you might be able to use bcp.exe to get the data in. bcp takes delimited files as input and will bulk insert to the table of your choice. as I recall, you can tell it what line to start on, and what line to end on.

look up "bcp utility" in bol for more info.

one catch may be that you are using a space to delimit the field values. it might make more sense to use some other char, like tab or "|". basically you need to choose a delimiter that won't appear in any of your text fields.

EDIT: i hope those aren't real passwords you posted... you might want to edit them if they are!|||The actual text file appears to be tab delimited. I think this forum condensed it when I posted. I will look at bcp, though my problem will be that the text file she sent contains this type of thing for multiple tables (actually it starts with create table statements). I guess in postgres you could just execute the text, and it would create/populate each table in turn. Given that the start and end point for each table would change, I'm not sure bcp will work without my manipulating the data in some way, which I was hoping to avoid.

Thanks for the help!

import data from MySQL to SQL Server

Hi All
I'm trying to import data from mySQL database to SQL server 2005. I installed mySQL odbc driver (3.51) and configured system DSN. However when I start SQL server import/export wizard mysql is not an option in data source dropdown.
What do I miss?
Thanks,
Vlad.Hi,
I never worked with MySQL, but there is a general approach. If you were able to create DSN, try to create a DTS package on SQL Server. I do not use SQL Server2005, but 2000. There is Other Connections icon under Connections in DTS designer. Configure this connection for MySql. The rest is yours - just configure the destination for your MySQL data.
Regards|||Hi,
I agree with dbmjs that creating a DTS package will probably be an easy way of transfering the data. Just a couple of quick notes:
1) DTS in sql server 2000 works great for smaller < 1mil records. Anything bigger than that it seems to run into some issues. (You can always make a couple of smaller packets and import them)
2) The DTS equivelant in SQL Server 2005 is named SQL Server Integration Services (SSIS), and works great no matter the size or amount of records.

Hope this helps
Good luck,
Reghardt

import data from excel data sheets

Hello

A problem with my replication system as occured.

I have a working SQL server that can do replication trough internet, everyting works

The problem is when I try to import large amount of data (10000 rows) to my database on the SQL server

the subscriber on my client don't get the rows. That will say imported data is not being replicated.

only rows that i have manually inserted will be replicated.

I used the import wizard that came with SQL server.

Is there a solution to this problem?

tap, tap is anybody here

some kind of reply would be nice.

|||are you using merge or tran replication? If merge replication, you might have to import into a staging table and then bcp/bulk insert into your replicated table with the proper settings. i.e. bcp.exe/bulk insert has a "fire triggers" switch/setting that needs to be set so that the merge triggers will fire, and then yo have to run some other proc. you can search BOL for "merge replication considerations" to get more details.|||

Thank for reply

I am using merge replication

Is there any tutorials how to use bcp/staging?

By the way what is bcp?

|||

Did you search for "merge replication considerations" in books online? You should have come up with a topic similar to this, which I know applies to SQL 20005. http://msdn2.microsoft.com/en-us/library/ms151206.aspx

If you're using SQL 2000, I'm pretty sure the same thing applies.

You can read about bcp/bulk insert in Books Online as well, just search for it.

|||

Ok

Thanks

|||

Hi

Thanks for a VERY god response

I imported data to a yable and ran sp_addtabletocontents and Yeeeaaaa everyting is working.

My import is a one time import before deploying my application so I am happy with this.

Best regards KK

Import Data

Hi everybody,
I want to import data from different server.

According to my system. when we sell anything to our customer we upload all the information to webserver from our local server. This job we are doing it manually right now. but i want to upload the information automatically. when my agents sell something and they submit as sell i want to send a copy of information to my webserver automatically. Is there any trigger which can insert data into different server or schedule which can import data from different server. Please help meread BOL about trigger,if u want immediate update to ur webserver.
Or u can write procedures and schedule it,check BOL schedule job.|||I try to steer clear of triggers which operate outside the scope of the database. Consider using the trigger to populate a staging table of changes, and then running a scheduled job to load those changes to your destination database. This setup is more tolerant of system disruptions.

Import ASCII Data

I have data that comes from a legacy system. I can obtain the data in an
ASCII format. Currently I have created scripts in ACCESS to import the data
into tables.

What I would like to do is create an automated import function in SQL.

I am new to SQL, can anyone point me in the direction I should look to find
out how I could perform this task?

Using SQL 2005.

Thanks
Matt

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via http://www.sqlmonster.commattc66 via SQLMonster.com (u16013@.uwe) writes:

Quote:

Originally Posted by

I have data that comes from a legacy system. I can obtain the data in an
ASCII format. Currently I have created scripts in ACCESS to import the
data into tables.
>
What I would like to do is create an automated import function in SQL.
>
I am new to SQL, can anyone point me in the direction I should look to
find out how I could perform this task?
>
Using SQL 2005.

There are a couple of alternatives. There is BCP (command-line tool)
and BULK INSERT (T-SQL statement) which work very similarily. Their
good as long as the files have one entry for each file in each record,
and there are no headers.

You can use the Import Wizard in SQL Server Management Studio. As with
all graphical tools, it's good for a one-off, but it's really a good
place if you need to do this on a regular basis. The Import Wizard requires
that SSIS (see below) is installed.

And then there is SQL Server Integration Service (SSIS), the member of the
SQL Server family that is all about importing and exporting data and
transforming it on the way. I have not used SSIS or its predecessor
myself, so I don't really know what it's so fantastic. (Being an old-
timer, I get by very well with BCP and BULK INSERT.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||To add to Erland's response, you can use a SQL Agent job to schedule the
import process. In the case of a package created with SSMS or the BI dev
studio, there is a specialized SQL Server Integration Services step type
that allows you to specify the desired package and run time settings via a
GUI.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"mattc66 via SQLMonster.com" <u16013@.uwewrote in message
news:65826d5d02090@.uwe...

Quote:

Originally Posted by

>I have data that comes from a legacy system. I can obtain the data in an
ASCII format. Currently I have created scripts in ACCESS to import the
data
into tables.
>
What I would like to do is create an automated import function in SQL.
>
I am new to SQL, can anyone point me in the direction I should look to
find
out how I could perform this task?
>
Using SQL 2005.
>
Thanks
Matt
>
--
Matt Campbell
mattc (at) saunatec [dot] com
>
Message posted via http://www.sqlmonster.com
>
>