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 excel into SQL 2005 working in development but not in production

I have a simple code that uses a the file upload control to read an excel sheet and upload the data into a SQL 2005db.

I'm using Visual Web Developer and Sql's express edition to test it. It works fine when I test. However, when I push it up to the production server and try it via the any other pc it does not. The page loads fine. However, when it starts to upload it errors out.

Any reason why? I've never seen this happen.

Here's the code. Thanks in advance.

Protected Sub BtnUpload2_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles BtnUpload2.Click UploadTextDocument()End Sub Private Sub UploadTextDocument()Dim locationAs String = FileUpload1.PostedFile.FileName.ToString' Connection String to Excel WorkbookTry Dim excelConnectionStringAs String =String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", location)' Create Connection to Excel Workbook Using connectionAs Data.OleDb.OleDbConnection =New Data.OleDb.OleDbConnection(excelConnectionString)Dim commandAs Data.OleDb.OleDbCommand =New Data.OleDb.OleDbCommand("Select BuilderID,SeriesID,OptionLevel,CommunityID,PhaseID,PlanID,ElevationID,OptionID,CurrentSalesPrice,LocalComments,Active,DateAdded,DateAvailable,DateInactive,SalesPriceEffective,SalesPriceExpires,PreviousSalesPrice,CutOffNotBefore,CutOffNotAfter FROM [Data$]", connection) connection.Open()' Create DbDataReader to Data Worksheet Using drAs Data.Common.DbDataReader = command.ExecuteReader()' SQL Server Connection StringDim connectionStringAs String = ConfigurationManager.ConnectionStrings("HbAdminMaintenance").ConnectionString' Bulk Copy to SQL Server Using bulkCopyAs SqlBulkCopy =New SqlBulkCopy(connectionString) bulkCopy.DestinationTableName ="ExcelData" bulkCopy.WriteToServer(dr)End UsingEnd Using connection.Close()End Using LBError.Text ="The spreadsheet was successfully uploaded."Catch LBError.Text ="There was an error. Check the spreadsheet for correct format."End Try End Sub

whats the exact error message ur getting?

|||

Hey Karen,

I don't get one. Debugging is off on the live application. I just get the error message at "catch".

I have a prospective client looking at it as we speak and don't want to make any changes to the web.config while they are viewing it...

|||

to catch error

use

try catch.. with and throw ex,.

one good possible reason is excel work sheet contains datatype that are not a part of datatypes of ur columns in tables. ..

there would not be any error, but the writetoserver would simply exit.. that's it..so use the old sheets u used in development server and try again.

Try printing., sheetnames and fieldcount.. in reader..

|||

Actually, I'm using the exact same sheet, same data, everything. I debugged it and still can't find any errors.

It only works from developement... I haven't tried it in a reader.

|||

How bout u try saving that file first and then transferring its contents to the database?

Regards

Karen

|||

Possible problems:

You don't have the correct version of Jet installed on the production server.

The process doesn't have read permissions at the location that the file is being written to.

try turning on remote errors in the web.config file so you can see exactly what the error is.

|||

Please provide the code topcopy the data/rows in the Excel work sheet to the sql server table.

Thanks and Regards

Ravi Shanker Maduri

Hyderabad

|||

Okay. I captured the error message. When I select the excel sheet from the folder on my desk top this message appears. The path is correct, as I said, it works fine when I do it from developent...

'C:\Documents and Settings\rednelo\Desktop\IPAddress_Mapping\test.xls'. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

|||

The problem is this line here:

Dim locationAs String = FileUpload1.PostedFile.FileName.ToString

You are using the filename that was on the client. You need the filename of the temporary file that the server created.

|||

Hi

I want to Import The Excel Data to the sql server using T-Sql Query ,i will run somany PL-sql statements for each project i want to automate it , i want to insert the default data to a table using excel sheet to the sql server table.

Please if any one know the solution for moving the excel data to the sql server using T-Sql Statement only.

Thanks and regards

Ravishanker Maduri

|||

Motley:

You need the filename of the temporary file that the server created.

All that makes sense. How would I get to the name of the temporary file?

|||

strINVSRC = Path.GetFileName(FlInvSrc.PostedFile.FileName)

|||

Hey Karen,

I tried what you have and I'm getting this error when I put in on the server. Did you run into this?

The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.
However, the same code in development gives me this:

The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.

So, its not finding the file. Let me recap...

Dim locationAsString

'this works in development as I said before. So I know that was finding it at least on my local machine.

location = FileUpload1.PostedFile.FileName.ToString

What you had does not appear to find the file.

location = Path.GetFileName(FileUpload1.PostedFile.FileName)

Its somewhere between the two...

|||

Take a look at this link some one gave it to me when i had the same error message

http://forums.asp.net/t/1034337.aspx

I have done it this way..

First i am getting the name of the file...

Dim strFilePath,location as string

StrFilePath = Path.GetFileName(FileUpload1.PostedFile.FileName)

and then

location = Server.Mappath("The directory where u wanna store the file" & StrFilePath.

hope this helps..

Regards

Karen

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 database from access

Hi
I am develop a project with .net in windows application.my source of the
database set in access I want to working with WMSDE and I need to import the
database under the access to database in WMSDE first Is it passible?
If yes, how can I do this.
Thanks for who refer to my question
doesn't access have an upsizing wizard in it ?
"amos hchmon" <amoshchmon@.discussions.microsoft.com> wrote in message
news:91853F12-5194-4D31-891F-0C5EAFC16E94@.microsoft.com...
> Hi
> I am develop a project with .net in windows application.my source of the
> database set in access I want to working with WMSDE and I need to import
> the
> database under the access to database in WMSDE first Is it passible?
> If yes, how can I do this.
> Thanks for who refer to my question

Import database from access

Hi
I am develop a project with .net in windows application.my source of the
database set in access I want to working with WMSDE and I need to import the
database under the access to database in WMSDE first Is it passible?
If yes, how can I do this.
Thanks for who refer to my questiondoesn't access have an upsizing wizard in it ?
"amos hchmon" <amoshchmon@.discussions.microsoft.com> wrote in message
news:91853F12-5194-4D31-891F-0C5EAFC16E94@.microsoft.com...
> Hi
> I am develop a project with .net in windows application.my source of the
> database set in access I want to working with WMSDE and I need to import
> the
> database under the access to database in WMSDE first Is it passible?
> If yes, how can I do this.
> Thanks for who refer to my question|||you can import the access via SQL Servers import function in the EMC.

Import database from access

Hi
I am develop a project with .net in windows application.my source of the
database set in access I want to working with WMSDE and I need to import th
e
database under the access to database in WMSDE first Is it passible?
If yes, how can I do this.
Thanks for who refer to my questiondoesn't access have an upsizing wizard in it ?
"amos hchmon" <amoshchmon@.discussions.microsoft.com> wrote in message
news:91853F12-5194-4D31-891F-0C5EAFC16E94@.microsoft.com...
> Hi
> I am develop a project with .net in windows application.my source of the
> database set in access I want to working with WMSDE and I need to import
> the
> database under the access to database in WMSDE first Is it passible?
> If yes, how can I do this.
> Thanks for who refer to my question

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