inserting into or reading from text files

[sagn]

How can I read data from a textfile
and put it into a table using QA?
Of course the reverse would be nice also....

I hate the DTS interface and would much prefer
to use direct SQL to do this...



thanks

diane

 

[bcouse]

Your pretty much stuck with DTS or BCP (Bulk Copy Program) for loading data. If your data file format is consistent, BCP is quick and easy to use, although you don't have the capabilities that DTS has (e.g. constructs to clean or change data during the load process).

From QA, you can always save your results to a text file or even a delimited file like a csv.

 

[bperry]

I've never done this myself. There's probably a few different ways...one of them is by using the openrowset function see BOL).

I saw this example once on the net and saved it.

Select * from openrowset('DTSFlatFile'
, 'Data Source=\\myserver\bosac7\t2.txt
;Mode=Read;Row Delimiter={LF};File Format=0;Column Delimiter=|
;File Type=1;Skip Rows=0;First Row Column Name=False
;Number of Column=0;Max characters per delimited column=255'
,'SELECT * ')

Perhaps it can be easily adapted to waht you are doing.

probably someone else with more experience here will respond with something more helpful.

brian perry

 

[carrr]

Assuming you've got a table built to receive the text data, you can drop it in from QA like this:

BULK INSERT 'destinationtablename' FROM 'location of text file'
WITH (datafiletype = 'char',
fieldterminator = ',',
rowterminator = '\n')

You may have to tweak this a bit, depending on the datatype, terminators, etc.
Use the Books Online section covering Bulk Insert for specs.

 

[sagn]

Hmm

I suppose I will have to use the bcp...

however... do I have to actually
be on the server whiere the DB is located to
get the data from the table ?

I have looked at the syntax which is somehting like

bcp DBname.owner.tablename

there is no mention of the server...

 

[Guest_imported]

You use bcp from the command line of your machine. If you embed it in dynamic sql then the database server will need to know where to find the file you're trying to import.

Part of the command will be -U"Username" -P"Password" -S"Server"

You will need to have the BCP utility installed on your machine. You can just install the sql client tools to get this if you do not wish to install all of sql server.

I prefer bulk insert though. It's not as fast as BCP but it is easier to script in a pinch.

Use DTS for designing reusable packages. This is really an interface built for managing common import tasks and not so much for the adhoc importing of data (unless you use the wizard which is rather simple)