?
Solved

Read text file data to table (Table name provided dynamically)

Posted on 2010-01-12
12
Medium Priority
?
713 Views
Last Modified: 2012-05-08
Hi
I am using the following code to copy content of text file to a table and it is working fine in which the table name is static.
but i tried passing table name dynamically..it's not working.

I am posting the code with static table name which is working fine and one with dynamic table name in which table name is passed as a parameter but not working.





***Working Code with static table name***

ALTER PROCEDURE [dbo].[ReadFromTextFile]
@FileName VARCHAR (1024)

AS
DECLARE @OLEResult INT
DECLARE @FS INT
DECLARE @FileID INT
DECLARE @Message VARCHAR (8000)

-- Create an instance of the file system object
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0
BEGIN
PRINT 'Scripting.FileSystemObject'
PRINT 'Error code: ' + CONVERT (VARCHAR, @OLEResult)
END

-- Open the text file for reading
EXEC @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 1, 1
IF @OLEResult <> 0
BEGIN
PRINT 'OpenTextFile'
PRINT 'Error code: ' + CONVERT (VARCHAR, @OLEResult)
END

-- Read the first line into the @Message variable
EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT

-- Keep looping through until the @OLEResult variable is < 0; this indicates that the end of the file has been reached.
WHILE @OLEResult >= 0
BEGIN
PRINT @Message
INSERT INTO dbo.xxxx_yyyy (Output) Values (@Message)
EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT
END

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS



***Code Not working with dynamic table name:***

ALTER PROCEDURE [dbo].[Read_Pump_Stats]

@FileName VARCHAR (1024),
@TableName VARCHAR(100)

AS
DECLARE @OLEResult INT
DECLARE @FS INT
DECLARE @FileID INT
DECLARE @Message VARCHAR (8000)
DECLARE @SQL NVARCHAR(max)

 

-- Create an instance of the file system object
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0
BEGIN
PRINT 'Scripting.FileSystemObject'
PRINT 'Error code: ' + CONVERT (VARCHAR, @OLEResult)
END

-- Open the text file for reading
EXEC @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 1, 1
IF @OLEResult <> 0
BEGIN
PRINT 'OpenTextFile'
PRINT 'Error code: ' + CONVERT (VARCHAR, @OLEResult)
END

-- Read the first line into the @Message variable
EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT

-- Keep looping through until the @OLEResult variable is < 0; this indicates that the end of the file has been reached.
WHILE @OLEResult >= 0
BEGIN
PRINT @Message
SET @SQL=N'INSERT INTO '+@TableName+'(Data) VALUES ('+@Message+')'
EXEC sp_executesql @SQL
EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT
END

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

Open in new window

0
Comment
Question by:parpaa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26297437
it work fine for me ...
make sure that you pass the arguments in the proper order
0
 

Author Comment

by:parpaa
ID: 26298341
Following is the error message i am getting for each line...I didn't get why it is causing...
Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ')'.

GoldenGate Command Interpreter for ODBC

Msg 128, Level 15, State 1, Line 1

The name "GoldenGate" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Version 10.0.0.22 Build 001

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '10.0'.

Windows x64 (optimized), Microsoft SQL Server on May 8 2009 11:28:00

Msg 128, Level 15, State 1, Line 1

The name "Windows" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

 

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ')'.

Copyright GoldenGate Software, Inc. 1995-2009.

Msg 128, Level 15, State 1, Line 1

The name "Copyright" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

This software includes code written by third parties, including

Msg 128, Level 15, State 1, Line 1

The name "This" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Pthreads-win32(Copyright (C) 2001, 2006 Ross P. Johnson)

Msg 128, Level 15, State 1, Line 1

The name "Pthreads" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Blowfish encryption library (Copyright (C) 1997 by Paul Kocher)

Msg 128, Level 15, State 1, Line 1

The name "Blowfish" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

and other code as specified at http://support.goldengate.com. Additional

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'and'.

details regarding such third party code, including applicable copyright,

Msg 128, Level 15, State 1, Line 1

The name "details" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

legal and licensing notices, are available at the above referenced URL.

Msg 128, Level 15, State 1, Line 1

The name "legal" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

 

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ')'.

 

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ')'.

GGSCI (<instance name>) 1>

Msg 128, Level 15, State 1, Line 1

The name "<Instance Name>" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Sending GETTCPSTATS request to EXTRACT PUMP2 ...

Msg 128, Level 15, State 1, Line 1

The name "Sending" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

 

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ')'.

 

Msg 128, Level 15, State 1, Line 1

The name "RMTTRAIL" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

OK

Msg 128, Level 15, State 1, Line 1

The name "OK" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Session Index 0

Msg 1018, Level 15, State 1, Line 1

Incorrect syntax near 'Index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

Stats started 2009/11/02 16:58:59.196000 20:01:02.004000

Msg 128, Level 15, State 1, Line 1

The name "Stats" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

 

Msg 128, Level 15, State 1, Line 1

The name "Local" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Inbound Msgs 4542393 Bytes 61036633, 9 bytes/second

Msg 128, Level 15, State 1, Line 1

The name "Inbound" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Outbound Msgs 4542394 Bytes 30665380022, 5010 bytes/second

Msg 128, Level 15, State 1, Line 1

The name "Outbound" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Recvs 9084786

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '9084786'.

Sends 4542394

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '4542394'.

Avg bytes per recv 6, per msg 13

Msg 128, Level 15, State 1, Line 1

The name "Avg" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Avg bytes per send 6750, per msg 6750

Msg 128, Level 15, State 1, Line 1

The name "Avg" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Recv Wait Time 55230593000, per msg 12158, per recv 6079

Msg 128, Level 15, State 1, Line 1

The name "Recv" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Send Wait Time 44968000, per msg 9, per send 9

Msg 128, Level 15, State 1, Line 1

The name "Send" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

 

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ')'.

.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ')'.

 

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ')'.

 

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ')'.

GGSCI (<Instance name>) 2>

Msg 128, Level 15, State 1, Line 1

The name "Instance Name" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Open in new window

0
 
LVL 15

Expert Comment

by:MohammedU
ID: 26298500
I don't see any issue with the code...
But you can also use openrowset command...

declare @v varchar(500)
declare @s varchar(500)
declare @t varchar(500)
select @v ='select  * from '  + 'TestTextFileImport.txt'
select @v

select @s ='insert into '+@t+'
 select * from OpenRowset(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\;'', ''' +@v +' '')'

print @s
exec (@s)
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:parpaa
ID: 26305390
I got some thing to do with this line...
Could anyone let me know how to pass the @Message as string variable..
I think from the following statement it is taking @Message as scalar variable.
How can i format the following query to pass @Message with in quotes.

SET @SQL=N'INSERT INTO '+@TableName+' (Data) VALUES ('+@Message+')'

Thanks,
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26305648
Do you mean:
SET @SQL=N'INSERT INTO '+@TableName+' (Data) VALUES (' + REPLACE(@Message, '''', '''''''') + ')'
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26305673
Let's try that again:
SET @SQL=N'INSERT INTO '+@TableName+' (Data) VALUES (' + REPLACE(@Message, '''', '''''') + ')'
0
 

Accepted Solution

by:
parpaa earned 0 total points
ID: 26306393
I found solution for this...

SET @SQL=N'INSERT INTO '+@TableName+' (Data) VALUES ('+QuoteName(@Message,'''')+')'

Thanks,
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26306835
QUOTENAME() will add single quotes around the text, since you already have them it is not necessary to add them again.  See for yourself:

DECLARE @Message varchar(100)

SET @Message = 'asadsadsa''dadad'
PRINT @Message
PRINT REPLACE(@Message, '''', '''''')
PRINT QUOTENAME(@Message,'''')

OUTPUT:
asadsadsa'dadad
asadsadsa''dadad
'asadsadsa''dadad'
0
 

Author Comment

by:parpaa
ID: 26306924
It adds quotes so that the command is taking it as String ...
It is not inserting any quotes into the table to which i  am loding because it takes the @Message as '@Message' i.e, exactly the way we pass the string variable.

FYI i am getting the data what i am  looking for into the table.

Thanks acperkins.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26310569
>>It is not inserting any quotes into the table to which i  am loding<<
Sure it is.  Look at the example I posted.  If the text has a single quote it will double them.

But whatever works for you...
0
 

Author Comment

by:parpaa
ID: 26315815
It is becoz you had set the variable with in quotes during intialization and finally you are printing it using QOTENAME ...So it is keeping additional quotes...
In my case i am passing input from text file which doesn't have any kind of quotes.
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question