Import flat file to SQL server using C#

Question

I want to make a program in C# that imports two types of files to SQL Server: tab delimited and fixed columns. Actually, I need to download a file every day and import that file into my database. I could make a console app with batch script. I saw some examples like this, but I don´t know if it is the best object-oriented way to do it.

I could use StreamReader, Regex and so on, but I don't want to re-invent the wheel.

PS: In VBA I used "QueryTables.Add".

Could you show the format for what the file is supposed to look like related to the SQL server? — msarchet, Nov 24 '11 at 21:31
Please note that Batch tag is "used for Windows batch file questions". Don't use it just because your question is related to "batch" word in any way... — Aacini, Nov 25 '11 at 02:39

score 2 · Answer 1 · answered Nov 24 '11 at 21:34

2

You can import in fully managed code via SqlBulkCopy; all you need to do is pass SqlBulkCopy an IDataReader that handles TSV. Fortunately the FastCsvReader on codeproject can do exactly that.

answered Nov 24 '11 at 21:34

Marc Gravell

1,026,079
266
2,566
2,900

I have some trash rows in my file. I need something like row patterns (Regex). I could create some file converter then use SQLBulk – Makah Nov 25 '11 at 15:10

score 1 · Accepted Answer · answered Nov 24 '11 at 21:31

1

If you don't want to reinvent the wheel, then you should look at the native tools that SQL Server provides for this, namely bcp. Here is a list of FAQs about bcp.

answered Nov 24 '11 at 21:31

competent_tech

44,465
11
90
113

score 0 · Answer 3 · edited Apr 08 '13 at 06:30

0

bulk insert [dbo].[CoursesTemp]

from 'C:\Users\Public\Downloads\Courses.csv'

with (fieldterminator = ',', rowterminator = '\n')
go
insert [dbo].[Courses]
  (code, description, instructor, date, venue, duration)
select 
   code, description, instructor, cast(date as date), venue,
   duration
from [dbo].[CoursesTemp]

edited Apr 08 '13 at 06:30

Anthon

69,918
32
186
246

answered Apr 08 '13 at 06:14

Santosh Wavare

1

score 0 · Answer 4 · answered Nov 24 '11 at 21:32

0

Sounds like a perfect job for SQL Server Integration Services (SSIS). You can easily define a data import task in SSIS and then schedule it to run by using a SQL job.

answered Nov 24 '11 at 21:32

tom redfern

30,562
14
91
126

I'm using SQL Server 2008 express – Makah Nov 24 '11 at 21:50

Import flat file to SQL server using C#

4 Answers4