2

I have a .txt file which is 6.00 GB. It is a tab-delimited file so when I try to load it into SQL Server, the column delimiter is tab.

I need to load that .txt file into the database, but I don't need all the rows from the 6.00 Gb file. I need to be able to use a condition like 

select * 
into <my table> 
where column5 in ('ab, 'cd')

but this is a text file and am not able to load it into db with that condition.

Can anyone help me with this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1960217
  • 131
  • 2
  • 3
  • 15
  • 1
    No, you cannot do this natively in SQL Server – SQLMason Jul 13 '16 at 19:19
  • Will you be importing *most* of the rows from the flat file or just a very few? Is the condition you're searching for something that you could do very easily, like with [`grep`](http://www.gnu.org/software/grep/manual/grep.html)? If so, you can filter your data before importing it. Otherwise, you're probably going to have to import the entire file, then run a `DELETE FROM`... `WHERE` statement. – Bob Kaufman Jul 13 '16 at 19:20
  • Thank you BOB, I will be importing around 20% of the file, the thing is when I do a preview on the flat file the default column name (column 11) is the one that I need to put condition on. that colmn has around 65 distinct names and I need to filter by 2 names. Can I try using visual studio or any other tool.? – user1960217 Jul 13 '16 at 19:27
  • I've never used this particular tool, but a quick Google search on `windows tool text search` yielded [WinGREP](http://www.wingrep.com/) which may be a quick solution for you. – Bob Kaufman Jul 13 '16 at 19:32
  • hey I think i found a way, there is a similar command like grep in windows called findstr, when i gave that command for the txt file it shows all the rows which i need for that name, all i need is to store the output in a separate text file. any ideas on it.? – user1960217 Jul 13 '16 at 19:39
  • Thank you so much bob, for giving me the idea about grep, it worked i ran that command and stored it in a separate txt file and I am able to import that into my db. – user1960217 Jul 13 '16 at 19:50

2 Answers2

3

Have you tried with BULK INSERT command? Take a look at this solution:

--Create temporary table
CREATE TABLE #BulkTemporary
(
  Id int,
  Value varchar(10)
)

--BULK INSERT has no WHERE clause
BULK INSERT #BulkTemporary FROM 'D:\Temp\File.txt'
WITH (FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n')

--Filter results
SELECT * INTO MyTable FROM #BulkTemporary WHERE Value IN ('Row2', 'Row3')

--Drop temporary table
DROP TABLE #BulkTemporary

Hope this helps.

Paweł Dyl
  • 8,888
  • 1
  • 11
  • 27
1

Just do a Bulk Insert into a staging table and form there move the data you actually want into a production table. The Where Clause is for doing something based on a specific condition inside SQL Server, not for loading data into SQL Server.

ASH
  • 20,759
  • 19
  • 87
  • 200