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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-06-24 : 01:23:51
|
Chris writes "Hi, I'm using SQL 2000 for tracking data in a mail house. I have been using DataJunction to import dbf files into a table that is updated daily to show what type of mail has gone out that day. Information such as letter-head type, postage, and the date it was sent is not in the content of the dbf file, so it must be drawn out from the file name. The dbf file names are date-stamped, and use other letters to designate information for the population of the table. For instance the file name 'SB1FC020619.dbf' tells me that it is a 'SB1' letter-head type, sent out via First Class postage 'FC', on June 19, 2002 '020619'. DataJunction can pull this information from the file name to create a table looking like this:ID Code Date Rate02022617086455808 FC-SB1 020325 0.1902022627160796769 FC-SB1 020321 0.1902040123129449395 FC-RT1 020425 0.26902022619112853418 FC-SB1 020313 0.1902022628165953403 FC-OG1 020429 0.340 My question is this: Can SQL be used to pull this information from a file name via a bulk insert query or DTS package (or some other method), so that I don't have to use DataJunction? Thanks in advance." |
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2002-06-24 : 10:44:46
|
DTS can do what you're looking for. You can use the Active Script Task in DTS to pull the information from each file and insert it into a table. You can even do it in a stored procedure if you want. Create a temporary table and then fill it with the file names like so.CREATE TABLE #filenameinfo (filename varchar(500))insert into #filenameinfo exec xp_cmdshell 'dir f:\maildir\ /b' Then just process each of the records.Jeff BanschbachConsultant, MCDBA |
 |
|
|
|
|
|
Importing data into SQL