SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Import text files script		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts
Posted - 10/20/2003 :  11:40:58  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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 - 06/18/2004 :  03:41:14  Show Profile  Reply with Quote
Good Article.

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

thank You
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts
Posted - 06/18/2004 :  09:09:21  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
Use a mapped drive?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts
Posted - 06/18/2004 :  09:27:20  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

thaoton
Starting Member

1 Posts
Posted - 09/13/2006 :  17:27:15  Show Profile  Reply with Quote
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"
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts
Posted - 09/14/2006 :  05:03:11  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000