I am trying to import a large file into SQL server that is | delimited.

I know basically nothing about the data, I just want to get it imported.

When I go to Database->tasks->import I use the advanced option to suggest types, and provide padding.

The problem is that that routine does not go through the whole file, even when I specify an absurly large number of rows (1000000000), and so I am constantly getting tructation->error, change the types, restart the import etc errors.

Is there a better way to do this?

Note: The file is not on the same machine as SQL Server

  • Please check the datatype in your table. Is it exactly matching with your source filed? Commented Sep 4, 2019 at 6:33

1 Answer 1


I would use Bulk insert. If you have a delimited file, it should be pretty easy.


A sample looks like this

   FROM 'c:\test\test.txt'
         FIELDTERMINATOR ='|',
         ROWTERMINATOR =' \n'


Based on the given requirements (the file cannot be copied, and a share cannot be used), My recommendation would be to develop your own SSIS package. In the package you will be able to choose your source and play with the data types. The suggest types seems to be a constant miss when I have used it, so I wouldn't rely on that. I would look at a row of the data in Excel and determine, what data type and how large you should make it. Sorry there is no silver bullet here.

Note: You can go through the import wizard and save the package, if you want to start from that point, instead of starting from scratch.

  • This does not work as the file is not on the same machine as the server (see note)
    – soandos
    Commented Aug 2, 2013 at 18:54
  • Can you copy the file to the database server, or put the file on a share that the database server can see? Commented Aug 2, 2013 at 18:58
  • No, it is not allowed
    – soandos
    Commented Aug 2, 2013 at 19:02
  • 1
    The silver bullet is going to be the BULK INSERT command, but in order for this to work the SQL Server Service account has to be able to see the file (if you are using SQL Authentication) or you will have to perms the file (Windows Authentication)... What I am confused about is how you can see the file from the import wizard, but not from Bulk insert, if the file is not on the db server and not on a files share. Commented Aug 2, 2013 at 19:20
  • 1
    @AdamHaines The file is on a local machine, not a network share. The import wizard is from SQL management studio, not sql server
    – soandos
    Commented Aug 4, 2013 at 1:32

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