Import text file

[SteveMac32]

I am looking for a way that in a stored procedure I can read/import a text file into a table, delimiting by a ‘, ‘.

I have read the archives on this matter but they only tell me about DTS (I am fairly new to SQL and not fully clued up on DTS) I would rather write some code it into a procedure to accomplish this. Any pointers or advice would be appreciated.

thanks

Steve Mac

 

[mfairchild]

Try this:

Create Procedure pTempProc @FilePath varchar(255)
AS

Declare @SQL nvarchar(1000)

Create Table #TempFile
(Field1 int Null,
Filed2 varchar(50) Null)

SET @SQL = "
Bulk Insert #TempFile FROM '" + @FilePath + "'
With (FirstRow = 1,
FieldTerminator = ',')"
EXEC(@SQL)

You may have to alter the FirstRow value depending on whether or not you have column headings in the file.

Matt

 

[arrowhouse]

You can use bcp or BULK INSERT (if your version of SQL has it) . See books on line, both can have the row & field delimiters specified.

BCP is run from the command line so you can call it in your SQL vi xp_cmdshell, BULK INSERT can just be written as T-SQL statement.

Good luck.

 

[SteveMac32]

Now I understand, thanks a lot your help is much appreciated.

Steve Mac
X-)