• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

How to read from text file in SQL Server 2005

Dear Experts,

For some reason i have to read line by line from a text file and insert that text into a trigger at a specific time everyday. Is this is possible? then How? any special privileges are required for sql user? the file path and name is fixed....

Kind Regards,
0
jaguar4u
Asked:
jaguar4u
  • 10
  • 3
  • 3
3 Solutions
 
Dave BaldwinFixer of ProblemsCommented:
Specific privileges are always required, you need a username and password and permission to access that database.  Normally you will get that info from your database administrator.
0
 
jaguar4uAuthor Commented:
But how to read from a text file in SQL Server 2005?
I already have a username/password for database but do i need any store procedure to execute from msdb to process the text files?
0
 
Dave BaldwinFixer of ProblemsCommented:
Normally you have to write an external program to open the text file and send the lines to the database.  I don't believe it will do it by itself.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
jaguar4uAuthor Commented:
We can do it either by using OPENQUERY or SSIS package, but i don't have the syntax to do it using OPENROWSET....
0
 
Dave BaldwinFixer of ProblemsCommented:
You need to click on "Request Attention" above and get some other people and maybe some other zones for your question.  I haven't used either of those packages.
0
 
hdesoukyCommented:
Need more information to understand your problem
Are you asking to open the file to read content and insrt into database table or use the file content to create a trigger over a table?
0
 
jaguar4uAuthor Commented:
i need to open the file to read content and insrt into database table, the contents are plain text with fixed length.
0
 
hdesoukyCommented:
you can use table variables and openrowset to do this

Example code attached below

DECLARE @MyTable Table
(textLine nvarchar)

INSERT INTO @MyTable 
SELECT A.* FROM OPENROWSET(BULK 'C:\CKINFO.TXT',SINGLE_CLOB) as A


SELECT * FROM @MyTable

Open in new window

0
 
jaguar4uAuthor Commented:
Thank you for your kind response:

I used the following and it work like charm, only i have to update the SQL Server surface area configuration to allow openrowset...

Problem solved


CREATE TABLE #import (txtData nvarchar(50)) 

BULK INSERT #import
   FROM 'F:\XXXX.txt'
   WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n', FIRSTROW = 1)

SELECT * FROM #import
DROP TABLE #import

Open in new window

0
 
jaguar4uAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for jaguar4u's comment http:/Q_27410665.html#37013639

for the following reason:

Problem solved
0
 
jaguar4uAuthor Commented:
I have already found a solution and added to the question. Hence the problem is solved now so no need for question to be up.
I don't know what is the procedure of closing a question if found the answer...

Kind Regards,

Asim
0
 
hdesoukyCommented:
But note that your solution using templ tables and BULK INSERT which is already old style and temp tables causes troubles when something goes wrong before dropping the temp table

The table variable and openrowset is the optimum solution for your problem
0
 
jaguar4uAuthor Commented:
Thank you for further clarification, i will use table variables and openrowset.
0
 
jaguar4uAuthor Commented:
There are mutiple solutions to the questioin.
0
 
jaguar4uAuthor Commented:
Problem can be solved with multiple solutions
0
 
jaguar4uAuthor Commented:
I accept what ever is suggested by community
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 10
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now