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
– msarchet, Commented Nov 24, 2011 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
– Aacini, Commented Nov 25, 2011 at 2:39

Marc Gravell · Accepted Answer · 2011-11-24 21:34:45Z

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, 2011 at 21:34

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Makah Over a year ago

I have some trash rows in my file. I need something like row patterns (Regex). I could create some file converter then use SQLBulk

competent_tech · Accepted Answer · 2011-11-24 21:31:57Z

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, 2011 at 21:31

competent_tech

45.1k12 gold badges96 silver badges114 bronze badges

Comments

tom redfern · Accepted Answer · 2011-11-24 21:32:41Z

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, 2011 at 21:32

tom redfern

32k15 gold badges97 silver badges138 bronze badges

1 Comment

Makah Over a year ago

I'm using SQL Server 2008 express

Anthon · Accepted Answer · 2013-04-08 06:30:59Z

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 8, 2013 at 6:30

Anthon

78.3k35 gold badges207 silver badges290 bronze badges

answered Apr 8, 2013 at 6:14

Santosh Wavare

1

Collectives™ on Stack Overflow

Import flat file to SQL server using C#

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related