Article: Import text files script

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Import text files script

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-10-20 : 11:40:58

Nigel submitted "This procedure will import all text files that are placed in a directory into a table. The files are then moved to an archived table.

If the SP call is scheduled then it will import any files that arrive.
Used togther with the ftp procedure it can be used to import files from an ftp server.

The process imports into a global temp table then calls another SP to move the data to the destination table. The called SP is expected to be written for the file types to be imported.
The import SP deals with crlf delimitted files only but is easy to change for others.

For large amounts of data you may want to import directly to the destination table - this just means changing the bulk insert statement and removing the global temp table."

Article Link.

putsik
Starting Member

2 Posts

Posted - 2004-06-18 : 03:41:14

Good Article.

What if my text file is located on another computer, do you have any idea on how to do this?

thank You

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-18 : 09:09:21

Use a mapped drive?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-18 : 09:27:20

I would copy the files to a local drive first. It will make the import a lot faster and have less impact on the database. Not important for small files but can make a big difference for large ones.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

thaoton
Starting Member

1 Post

Posted - 2006-09-13 : 17:27:15

Any chance someone could help me modify Nigel's script to work with Tab-Quote delimited files? There's a header in every file.

Example Data:

"Name" "Value"
"Brian McMillen" "1722"
"Anthony Adams" "8532"
"Erik Jenner" "7209"
"Terrance Walker" "1873"
"Matthew Sullivan" "7337"

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-14 : 05:03:11

Depends how much data there is. This process won't be good for large amounts - maybe 1G would be ok depending on the machine.

Create the function f_GetEntryDelimiitted
http://www.nigelrivett.net/SQLTsql/f_GetEntryDelimiitted.html

Now the merge sp becomes
insert BCPData
(
fld1 ,
fld2 ,
fld3 ,
fld4
)
select
fld1 = dbo.f_GetEntryDelimiitted(s,1,',','Y') ,
fld2 = dbo.f_GetEntryDelimiitted(s,2,',','Y') ,
fld3 = dbo.f_GetEntryDelimiitted(s,3,',','Y') ,
fld4 = dbo.f_GetEntryDelimiitted(s,4,',','Y')
from ##Import

Note ##Import should be #Import.

There are more efficient ways to do this but this is very simple. I use it for small imports.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources