Importing delimited files into SQL server [closed]

Question

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

Answer 1

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

http://msdn.microsoft.com/en-us/library/ms188365.aspx

A sample looks like this

BULK INSERT dbo.MyTable
   FROM 'c:\test\test.txt'
   WITH 
      (
         FIELDTERMINATOR ='|',
         ROWTERMINATOR =' \n'
      );

EDIT:

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.