Local TSV file into SQL table

Question

I have a tsv file on my local machine and need to load the values into a SQL table. Can I write a SQL script that does this? Or do I have to make an SSIS package or use a similar tool?

Answer 1

There are few solutions:

1. SQL Server Management Studio > select target database node > open contextual menu > Tasks > Import Data ...

2. SQL Server Integration Services > Flat File Source

3. T-SQL statement BULK INSERT with \t COLUMNTERMINATOR

4. T-SQL function OPENROWSET(BULK 'file path') with format file and \t terminator

5. bcp tool with -t parameter (default value \t)

6. TextFieldParser class (with Delimiters propery) and {SqlBulkCopy class or SqlCommand class}

7. Other solutions.

Note: For 3) and 4) source file have needs to be accessed by SQL Server instance (usually, this means that source file have to be on the same machine as SQL Server)

Answer 2

@Bogdan Sahlean gave a very exhaustive reply.

However we all know that importing whatever file in SQL Server is not easy and it always fails at the first try.

If you get exasperated like me while importing a .tsv that weights few gigabits like this one I have a fast solution for you.

In SSMS 2017 you can do: right click on the database > Tasks > Import Flat File... , and set nvarchar(MAX) for all Data Type and tick Allow Nulls for all rows:

This way you will import a data The Wrong Way but at least they are imported.

You can now work to clean them up.

EDIT: If you face a Exception of type 'System.OutOfMemoryException' was thrown. (mscorlib) you can create the table:

USE ip2location;  
GO  
DROP TABLE OpenStreetMap;  
CREATE TABLE OpenStreetMap   
(
name nvarchar(MAX) NULL,  
alternative_names nvarchar(MAX) NULL,  
osm_type nvarchar(MAX) NULL,  
osm_id nvarchar(MAX) NULL,  
class nvarchar(MAX) NULL,  
type nvarchar(MAX) NULL, 
lon nvarchar(MAX) NULL,  
lan nvarchar(MAX) NULL,  
place_rank nvarchar(MAX) NULL,  
importance nvarchar(MAX) NULL,  
street nvarchar(MAX) NULL, 
city nvarchar(MAX) NULL,  
county nvarchar(MAX) NULL,  
state nvarchar(MAX) NULL,  
country nvarchar(MAX) NULL,  
display_name nvarchar(MAX) NULL, 
west nvarchar(MAX) NULL,  
south nvarchar(MAX) NULL,
east nvarchar(MAX) NULL,  
north nvarchar(MAX) NULL,    
wikidata nvarchar(MAX) NULL,  
wikipedia nvarchar(MAX) NULL,  
housenumbers nvarchar(MAX) NULL, 
);  
GO 

And then import with BULK INSERT:

BULK INSERT OpenStreetMap
FROM 'C:\Users\franc\Desktop\planet-latest_geonames.tsv\planet-latest_geonames-sorted.tsv'
WITH (
  DATAFILETYPE = 'char',
  FIELDTERMINATOR = '\t',
  KEEPNULLS
);

EDIT,EDIT: if it's failing again because the table is too big you can:

1. Use the first query I pasted above to create the table
2. Run this on a CMD terminal:

bcp ip2location.dbo.OpenStreetMap in C:\Users\franc\Desktop\planet-latest_geonames.tsv\planet-latest_geonames-sorted.tsv -S localhost -U sqlninja -P sqlninja -c -r /r

EDIT,EDIT,EDIT:

Still failing? Let's try SQLCMD:

1. Use the query above to create the table
2. Save the BULK INSERT query above in a file called Target.sql or whatever and then:

sqlcmd -S localhost -d ip2location -U sqlninja -P sqlninja -i Target.sql -o Errors.txt