2

I am very new to C# (experienced in VB though) and I want to import some very large pipe (|) delimited files into my SQL Server database.

What I have done so far is created a C# project (using SharpDevelop) and added code to scan a directory for text files with a certain name and identify what the destination table in SQL Server will be based on the file name. I have also created a SQL connection which runs without errors but that is as far as I have got.

So I thought I'd as for advise on how best to take these types of files (some of which are a few thousand KB in size to almost 2GB in size) and import these into SQL Server. The text files also have a header row of column names and a few columns I would like to have formatted as a date. I also want to verify if the table exists first before importing so I know to create it or not.

Should I use some sort of file reader and read the file that way by line or read the entire file? Just not sure the best way to approach this.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Baub
  • 723
  • 4
  • 21
  • 36
  • 3
    Do you have to use C# and write it all yourself? You could check out SSIS (Integration Services) which offers lots of power to handle such cases quite easily. – marc_s Dec 09 '13 at 17:05
  • to import large amounts of data to sql server i previously used [bulk insert](http://msdn.microsoft.com/en-us/library/ms188365.aspx). i would suggest you to wirte a stored procedure in sql server. use parameters for filename, target-table etc. within the procedure do the bulk insert. /edit SSIS is also nice, depends mostly on your type of data how often you have to import and if its always the same format – Koryu Dec 09 '13 at 17:07
  • Is this a one time import or a continual process? If one time import, you can possibly just use the Import Data task available on a database in SSMS. If you need to do this import process repeatedly, I'd look into SSIS as marc_s suggested. – Aaron Palmer Dec 09 '13 at 17:09
  • I don't have access to SSIS but that would be a good thing to use if I did. This will be a process that we do over and over again as we get new files, which is why I wanted to make something in C#. – Baub Dec 09 '13 at 17:13
  • you should do it on serverside and import the whole file at once. client side will be bad performance and more trouble on error handling. you can also think about a bridge, means to import the file into a table with just two columns (line_no int, line varchar). then do the parsing separately. – Koryu Dec 09 '13 at 17:26

2 Answers2

2

Look at this post about using a Buffered Stream to read your large text files in. Splitting the pipe and calling the SQL function should be relatively easy after that.

Community
  • 1
  • 1
Dillie-O
  • 29,277
  • 14
  • 101
  • 140
  • So I have got this working and my only complaint would be that it runs really slow. A 3MB text file tables ~20 minutes to import. Are there other methods I could use to speed this up? Right now I am just reading one line at a time and inserting that. Maybe I could read 50 lines at a time and try that insert, not sure. – Baub Dec 11 '13 at 15:27
  • You're right. Individual insert statements are going to take forever. Look into the SqlBulkCopy class to setup your inserts to run in batch - http://msdn.microsoft.com/en-us/library/7ek5da1a(v=vs.110).aspx – Dillie-O Dec 11 '13 at 16:24
2

BCP would be the fastest way to import raw text data. SSIS is another option but is has some overhead reading data line-by-line and running through a translator.

D Stanley
  • 149,601
  • 11
  • 178
  • 240