Import Part of Text File 2

[sabloomer]

Here is my problem. I have a HUGE text file that holds 6 months worth of line level invoice data. I need to pull recent records out of this file, based on a record type and date, and them combine that it with tables that contain customer and item data, and then append them into my master invoice table.

In Access I would create a link to the text file and then write the delete and append statements to use just the records I needed. I do not think that the Linked Server option in SQL Server 7.0 will let me link to a flat file. The help files I read kept talking about "OLE DB Provider", and it didn't seem a flat file was one. If it can I link to a flat file I would love to learn how.

My next thought was I had to import the flat file into SQL Server. I used the wizard to create the DTS package to import the flat file. I changed the delete statement to truncate table, in an attempt to speed up the process. This process takes to long currently. I read about Bulk Import, but I am confused on how to use it and if it will speed up this process. Is there a way to import records from a flat file based on a certain criteria? It would be really wow-e-wiz-bang to take the data from the flat file, combine it with the customer and the item data, and append it into my master invoice in one step, like I currently do in Access.

As you can tell I am learning how to use SQL Sever and I would be thankful for any help you can provide.

sabloomer

 

[mrdenny]

Within SQL the easiest way to do it will be to load the entire text file into a temp table, then delete the un-needed data. There are ways to link to a text file, and do the deletes that way, but getting that working correctly will take longer than just deleting the un-needed data.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[rasETL]

I agree with mrdenny

Import the entire file into a temp table. Then create a DTS SQL Task that has the SQL Delete code.

How many records are in the file? Does the file reside in a folder on the Server's Hard Drive, or is there a network issue? My experience has shown it takes twice as long to import a file if it resides on a LAN folder.

Hope that helps.

 

[sabloomer]

Thank you for the feedback. To answer some of your questions, the file can get as big as 1.5 million records. The flat file is located on a network drive and can grow to almost 500 MB. Maybe I can try to copy the file local. Right now I am messing around with using an Access database as the data source for the import. Link the text file into Access and when I use the DTS Wizard I select the "Use Query" option and insert a query that links to the additional tables I need. So far so good.

When I import the data into a temp table should I be using the "Bulk Import" option or let the DTS wizard build it for me? Either way I want to truncate the temp before I load the data into it. Would it be better to drop the table and rebuild it? Should I index the temp table to speed up the transfer between temp file and the final table?

Thanks again,
sabloomer

 

[mrdenny]

You can build the dts package either way that you are more comfortable. You will want to truncate the table before you use it each time. It's more effecent to truncate the table then to drop it each time.

Creating an index on the table might speed things up a little. Best way to find out, is to run it, then put an index on it, and run it again, and check out the differences.

Denny

--Anything is possible. All it takes is a little research. (Me)