2

I've got this tab-delimited flat file with fifty columns and two million rows. I am trying to import it into Sql Server 2017 Express. (I first tried working with it in Excel... hopeless.)

I see two import paths available: SqlServer Management Studio's Import Flat File command, and the Import and Export Data wizard. Both fail, but in different ways.

With SSMS, it seems to do a very good job of parsing the file and guessing the column types, but it bases the lengths on only the first few hundred rows. One column is long text, so I've tried setting it to nvarchar(4000) or nvarchar(max) or ntext. A few others I knew might be longer than the default guessed length of 50 so I bumped up the sizes. But when I run the import, it says that some column would be truncated or otherwise fail to convert, and it refuses to say which column is the one having trouble.

With the import wizard, on the other hand, it doesn't guess column types at all, and I have to manually set the ones I know. And thankfully, it has a setting that allows you to tell it per column whether to fail on conversion errors or ignore them. But it ignores this setting -- it always fails no matter how you set the tolerance, per column or globally.

The good part is that it does identify a column name where it failed, namely the long text column. But as best I have been able to measure it, there's nothing in it which should have failed. I don't think any value is over 4000 characters (the longest I've located is 2027) and I'm pretty sure the character set is pure ascii-7. In fact, it's been forced to all uppercase.

I also tried importing from the abortive Excel sheet I made. This was thwarted because only the 32 bit importer could see Excel, and only the 64 bit one could see my database instance.

How can I get one or the other of these to allow the import to complete?

2

I worked with tsv files before and had a similar issue. When you're importing a flat file, I would suggest you change the way SQL Server interprets the column https://user-images.githubusercontent.com/35179176/34650736-39eaaab0-f3c6-11e7-9706-377c5f0f1170.png ↑Change it to [DT_TEXT] (not unicode) or [DT_WSTR] (for unicode). To be able to change this, you have to import through "Import Data" and not the "Import Flat file" option.

0

Rather than using the import wizard, you could place your TSV file on the SQL Server and use OPENROWSET to read the file data directly using a T-SQL script. Using this method and SELECT ... INTO would allow you to create a table based off the actual data types in the file (largest column etc).

See this link for more info.

Basic syntax:

SELECT BulkColumn
FROM OPENROWSET (BULK 'c:\temp\mytxtfile.txt', SINGLE_CLOB) MyFile 

Check out the documentation for handling things like row terminators etc

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.