need advise how to import text file into sql server
-
need advise how to import a DATA FROM text file evry 1 second
to sql server
or import the text file on line TO SQL SERVER
the problem is thet we have an old computer that generat A DATA INTO text file on line
i wont to show the data on line (FROM THE TEXT FILE) BUT FROM THE SQL SERVER on line
so the user can Refresh the data evry 1 second
(see evry cange in the text file)
thnks ilan
-
I would use BCP, it is the fastest way to read in a text file. Then i would recommend getting better equipment to be able to do what you need it to do.
Dealing with text files are not that difficult, but they are extremely slow. You will need to inport the text file only once. And then you can hit it all you want from your front-end app.
Andrew
-
thnks for the advise !
i need good examples + samples
because i need to do it evry 1 second an read from 4 text file !!!
and i not experienced with BCP and how to use bcp !!!
ilan
-
Ilan,
Open "Books on Line" (comes free with SQL Server) and look up the BCP Utility. It has examples...
It may help you to know that BCP.EXE runs from the "DOS" or "COMMAND PROMPT" environment and can be setup in a batch file which, in turn, can be scheduled through the Windows Task Scheduler. It will also help you to know that you will need to create a "BCP FORMAT FILE".
I am curious... why is it necessary to import once per second? What is it that you are trying to do?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
it i a power station
and i wont to show the value ON LINE from the web
and in power station evry second is important
(i need live examples to make batch file)
thnks ilan
-
I guess "Books on Line" is out of the question for you...
If you want a "live" example, perhaps you'd be kind enough to provide some live data and a description as to the layout of the data?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
ok
for start
how to make the BCP batch file thet copy the data from text file
to table in sql server
on line
Or
evry 1 second
-------
thnks ilan
-
You have to declare your text file for accessing directly from SQL Server like this example above :
EXEC sp_addlinkedserver
txtsrv,
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\temp',
NULL,
'Text'
GO
--Set up login mappings
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, sa, NULL
GO
select * into txtsrv...[HowBigAmI#txt] from sysservers
SELECT * FROM txtsrv...[ft#txt]
insert into txtsrv...[ft#txt] values ('test')
exec sp_droplinkedsrvlogin txtsrv, sa
exec sp_dropserver txtsrv
-
This is a very interesting question. In order to access a text file directly from SQL Server, you first need to create a schema.ini file that describes the data in the text file. See item #'s 4 and following below. Then you'll need to link to the text file. See item #'s 1-3 below.
Also, to avoid conflict between your reading of the file and the other app updating it, you probably want to make a snapshot copy of the file and link to the snapshot copy from SQL Server. See item # 3B. Below.
Finally, to actually run it every second, perhaps you can use a timed stored procedure, like this:
-- Assume you have a table: get_text_control_table with one field: keep_running
-- Also assume that you have a stored procedure: get_text_data
-- that actually does the work to get the text data into the database
CREATE PROCEDURE get_text_data_every_second
AS
WHILE (SELECT keep_running FROM get_text_control_table) = TRUE
BEGIN
WAITFOR DELAY '000:00:01'
EXEC get_text_data
END
Alternatively, you can run from an outside process such as your web server.
Good luck with it!
The the Microsoft documentation for linking to a text file is at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp. This is an annotated version.
1. This example creates a linked server for directly accessing text files, without linking the files as tables in an Access .mdb file. The provider is Microsoft.Jet.OLEDB.4.0 and the provider string is 'Text'.
2. The data source is the full pathname of the directory that contains the text files.
2A. A schema.ini file, which describes the structure of the text files, must exist in the same directory as the text files. For more information about creating a schema.ini file, refer to Jet Database Engine documentation. [SEE ITEM # 4 BELOW.]
3. Annotated example code:
--Create a linked server named: txtsrv
-- It can access all the files in directory: 'c:\data\distqry'
-- You only have to run this once,
-- unless as shown in TUR's example, you add and drop every time!
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\data\distqry',
NULL,
'Text'
GO
--Set up login mappings
-- It's unclear if you have to run this every time or not.
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL
GO
--List the tables in the linked server
-- You would need this only to verify that the file(s) are there
-- and that you can indeed access them
EXEC sp_tables_ex txtsrv
GO
--Query one of the tables: file1#txt
-- using a 4-part name: server...[filename#extension]
-- In your case, you might
SELECT *
FROM txtsrv...[file1#txt]
3B. Important: The link may not support multiuser access to text files. When you open a text file using the sp_addlinkXXX commands, you may have exclusive access to the file. If this is the situation, then you have to link and unlink every time you access the files. In fact, you may have to issue a DOS copy of the file so that you're working with a snapshot, in case the other app wants to update the file while you're peeking at it. The sequence would be, as shown in TUR's example:
xp_cmdshell {'COPY c:\data\livedata.txt c:\datacopy\snapshotdata.txt'}
EXEC sp_addlinkedserver txtsrv, [other options to access snapshotdata.txt]
EXEC sp_addlinkedsrvlogin txtsrv, [other options]
SELECT * FROM txtsrv [and/or other SQL statements]
EXEC sp_droplinkedsrvlogin txtsrv, sa
EXEC sp_dropserver txtsrv
4. To use the sp_addlinkXXX procedures, you must create a SCHEMA.INI file in the same directory as the text files. Here's an annotated excerpt from the Microsoft documentation at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/html/workingwithtextfiles.asp
4A. Example of a Schema Information File
The following example of a Schema.ini file specifies the format for the fixed-width file "Filename.txt" and the delimited file "Delimit.txt", and a pure text file "yourfile.txt".
[yourfile.txt]
ColNameHeader=False
Format=FixedLength
MaxScanRows=25
CharacterSet=OEM
Col1=all_text LongChar Width 500
[Filename.txt]
ColNameHeader=False
Format=FixedLength
MaxScanRows=25
CharacterSet=OEM
Col1=columnname Char Width 24
Col2=columnname2 Date Width 9
Col3=columnname7 Float Width 10
Col4=columnname8 Integer Width 10
Col5=columnname9 LongChar Width 10
[Delimit.txt]
ColNameHeader=True
Format=Delimited(!)
MaxScanRows=0
CharacterSet=OEM
Col1=username Text
Col2=dateofbirth DateTim
4B. Sample file contents:
yourfile.txt:
---------------------------------------
This is line 1 of the sample file
Row 2 abracadabra abcdefghijklmnopqrstuvwxyz etcetera etcetera
---------------------------------------
Filename.txt:
....+....1....+....2....+....3....+....4....+....5....+....6....+
---------------------------------------
Apple Orchards Inc. 10/15/04 12345678.9 10Apple Anne
Peach Brands 12/31/99 0000678.9 101Joe Pitts
---------------------------------------
Delimit.txt
---------------------------------------
username!dateofbirth
Anne Smith!12/15/1960
John Bush!1/12/1982
---------------------------------------
4C. Valid file formats are:
Format value File format
------------- -----------------------------------------------
TabDelimited Fields in the text file are delimited by tabs.
CSVDelimited Fields in the text file are delimited by commas.
Delimited(*) Fields in the text file are delimited by asterisks.
You can substitute any character for the asterisk
except the double (") quotation mark.
FixedLength Fields in the text file are of a fixed width.
4D. Valid data types are:
Microsoft Jet SQL data types:
Byte
Long
Currency
Single
Double
DateTime
Text
Memo
OR ODBC data types:
Char (same as Text)
Float (same as Double)
Integer (same as Short)
LongChar (same as Memo)
Date ( you must or may have to specify a date format )
Regards,Bob Monahon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply