3rd party tool to import VERY LARGE CSV files?

My home-grown import utility doesn't seem to be cutting it.

Are there some reliable 3rd party components I can wire-up to a C# Windows Service that will import very large CSV files into a SQL Server table?
LVL 5
Tom KnowltonWeb developerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tstoeckigtCommented:
Did you try using a DTS package?

1. Create a new package
2. Add a new SQL-Server connection
3. Add a new connection 'Text file (Source)
4. Click on the new Text-connection
5. Add Task "Transform data"
6. Select the target table
7. Map the columns
8. Run the transformation

Ciao
Timo
0
Tom KnowltonWeb developerAuthor Commented:
DTS is an option....and I've used DTS for similar projects.

However...for this project I do not want to use DTS.
0
Tom KnowltonWeb developerAuthor Commented:
Unless I can invoke a DTS script from within a C# Windows Service?
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

tstoeckigtCommented:
You can either run DTS as an external process or use COM.
The library is Microsoft DTSPackage Object Library.

You can also look here http://www.svek.co.uk/DTSForWeb.htm.

Ciao
Timo
0
Tom KnowltonWeb developerAuthor Commented:
Okay....this is a good option.

Suppose I do not want to use DTS at all?

What are my options?
0
tstoeckigtCommented:
What are the reasons to avoid DTS?
How many files do you have and how complex are they?

Plain CSV-files should be easy to import but doing this with high performance isn't.

The best way will probably be to load the file not with an text reader but with an BinaryReader. Then you will for example 8096 bytes or whatever the best pagesize of your disk is.
Then you can handle these pages for yourself (perhaps it's worth to do this with another thread. You could synchronize with a ringbuffer.)

As you see you can have every complexity you want ;-).

I wouldn't buy another tool because you already have two good options (and I don't know other tools :-) which doesn't mean there aren't any).

You have to think about some aspects of this problem before you deep further in some other suspicious options:
- how often do you load such files
- how long does it take with the simplest solution (you can try with a simple DTS)
- how long does it take with the fastest solution (you can copy the file from one physical disk to another)
- how much time can you win
- how much will you have to pay more to achieve the best solution than the worst

Ciao
Timo
0
tstoeckigtCommented:
It should be "Then you will _read_ for example 8096 bytes or whatever the best pagesize of your disk is." of course.
0
Tom KnowltonWeb developerAuthor Commented:
Assume there will be about 100 CSV files, each one is about 9 MB.
0
Tom KnowltonWeb developerAuthor Commented:
The files are not very complex....pretty much just strings
0
tstoeckigtCommented:
Well than I assume you won't do 100 DTS packages.
But these files aren't very big so I would suggest following solution:
- Read with a TextReader (9 MB is not worth to handle the file bytewise - I thought of files of >1GB...)
- either the files have headers so you can read the column names from the header or you have to create a description of the file:
  - the main thing will be to map the columns of the files to columns in the tables
  - i would suggest some configuration tables:
    - one for the tables (or two if you want normalized tables) which contains all possible tables and columns
    - one for the files (or two if you want normalized tables) which contains all possible files and columns
    - one mapping table
  - with this configuration it should be simple to generate the sql-statements on the fly

Ciao
Timo
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.