Read from an existing text file(.txt) in sql server 2005

Question

My gps device writes its data to a text file in my server. Now i want sql server to read it and store values in a table. How to get started with this kind of a scenario? Any suggestion.

EDIT: Consider it is an existing file with 10 rows and i have already imported it. Now the file gets updated with new 10 rows. How to import the new rows to sql server?

Answer 1

SqlBulkCopy, or TSQL BULK INSERT, or perhaps if no data transformations are required, the SSIS Bulk Insert Task.

See:

• About Bulk Import and Bulk Export Operations

• SSIS: Using Bulk Insert Task with csv files

• Tutorial: Creating a Simple ETL Package

Answer 2

Make sure your file path is correct. It can be relative if it lives on the same machine as sql server but if not you need an absolute path. Also, sql server would need permissions to that file location. Maybe not the case here, but that's ok.

Answer 3

You can use BULK INSERT for example. Save the count of rows that you have inserted and set the parameter FIRSTROW of the BULK INSERT respectivelly next time so that you will start on the new rows.

Similary you can use bcp utility and set the -f parameter.

Answer 4

Here is a sample solution:

/** Table to hold GPS data **/

CREATE TABLE Pings (
  RowID     INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  PingDate  DATETIME,
  Lat       FLOAT,
  Long      FLOAT
) 

/** View for bulk insert **/

CREATE VIEW V_Pings AS 
SELECT 
  PingDate,
  Lat,
  Long
FROM Pings

The GPS data comes from a pipe-delimited file > C:\GPS\Pings

2010/01/01 00:00:00|30.1|50.1
2010/01/01 00:00:01|30.1|50.2
2010/01/01 00:00:02|30.1|50.3
2010/01/01 00:00:03|30.1|50.4
2010/01/01 00:00:04|30.1|50.5

You have a stored procedure which is being called intermittently via SQL Agent:

CREATE PROCEDURE usp_LoadPings AS 

DECLARE 
  @firstRow INT,
  @sql NVARCHAR(1000)

SELECT TOP 1 @firstRow = RowID + 1
FROM Pings ORDER BY RowID DESC

SET @sql = N'
BULK INSERT V_Pings
FROM ''C:\GPS\Pings.txt''
WITH (
  FIELDTERMINATOR =''|'',
  ROWTERMINATOR =''\n'',
  FIRSTROW = ' + CAST(@firstRow AS NVARCHAR(50)) + '
)'

EXEC(@sql)

The stored procedure will not load data unless there is a new set of rows starting after the last row loaded in the table.

I realize this simply looks like an implementation of devmake's answer but I actually created it separately. That said, I upvoted his answer since he posted his first.