sql server stored procedure for reading file information

Question

1.00/5 (1 vote)

See more:

Hi all,

I have to write stored procedure which will insert file name and file path and type of file into table.Please help me.

Posted 24-Jun-13 3:07am

xyz from Bangalore

Updated 24-Jun-13 20:57pm

v2

Add a Solution

Comments

RedDk 24-Jun-13 13:35pm

Need some code, man.

xyz from Bangalore 25-Jun-13 0:04am

Sorry. I am not getting how to proceed this problem.Not getting how to read file so that it will store data into table.

RedDk 25-Jun-13 10:08am

Ok,
As I tried to make clear in the first section (vvexample(td).txt). That copy&paste bit is not formatted as (td) tab-delimited. In order to successfully paste it as useful input for the BULK INSERT, one must "add" {tab} to it; that is replace (literally) all {space} with {tab}.

Mind you, it's only data. Just easier to see the method when the outside-of-the-TSQL-box method with which I want to illustrate the point has 45 ones and zeroes randomly distributed ...

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sudhakar Shinde · Answer 1 · 2013-06-24T03:19:00

Solution 1

You can use BULK insert as mentioned below.

SQL

CREATE TABLE #FileData 
    LineNumber  int identity (1, 1),
    LineData  nvarchar(4000)
);

INSERT INTO #FileData
SELECT file.BulkColumn 
FROM OPENROWSET
    (
        bulk 'C:\yourpath\Temp\anyfile.txt',
        single_clob 
    ) file;

SELECT * FROM #FileData;

Note :- File name is hardcoded in above example which you need to change in your actual code.

Posted 24-Jun-13 3:19am

Sudhakar Shinde

Comments

xyz from Bangalore 25-Jun-13 0:29am

Hi,
I used bulk insert task but its giving error saying 'String or binary data would be truncated.
The statement has been terminated'.
I have text file which contains some information separated by ','.The stored procedure should read file and store that data into table along with file name and location.

Radhadeep12 · Answer 2 · 2013-06-25T00:07:00

May this helps

SQL

CREATE TABLE MainTable(Col1 INT, Col2 VARCHAR(30),Col3 VARCHAR(30),FName VARCHAR(30) NULL,FPath VARCHAR(20) NULL)
GO--Main table

CREATE PROCEDURE ReadFile
  @FileName VARCHAR(20),
  @FilePath VARCHAR(20)
AS
BEGIN
    --SET @FilePath = 'F:\data.txt'
    CREATE TABLE #temp(Col1 INT, Col2 VARCHAR(30),Col3 VARCHAR(30))--Data in text file

    DECLARE @String VARCHAR(MAX)
    SET @String = ''
    SET @String = 'BULK INSERT #temp FROM ''' + @FilePath + '''
    WITH
    (
       FIELDTERMINATOR = '','',
       ROWTERMINATOR = ''\n''
    )'
    EXEC (@String)

    INSERT MainTable(Col1,Col2,Col3) SELECT * FROM #temp

    UPDATE MainTable SET FName = @FileName
    UPDATE MainTable SET FPath = @FilePath

    DROP TABLE #temp
END
GO

EXEC ReadFile @FileName = 'Your File Name',@FilePath = 'File Path'

SELECT * FROM MainTable
GO