Take the 2-minute tour ×

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

share|improve this question
add comment

closed as unclear what you're asking by Paul White, StanleyJohns, Mark Storey-Smith, RolandoMySQLDBA, dezso Aug 4 '13 at 6:46


1 Answer

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.

share|improve this answer
This does not work as the file is not on the same machine as the server (see note) –  soandos Aug 2 '13 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? –  Adam Haines Aug 2 '13 at 18:58
No, it is not allowed –  soandos Aug 2 '13 at 19:02
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. –  Adam Haines Aug 2 '13 at 19:20
@AdamHaines The file is on a local machine, not a network share. The import wizard is from SQL management studio, not sql server –  soandos Aug 4 '13 at 1:32
show 4 more comments
