Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

3rd party tool to import VERY LARGE CSV files?

Posted on 2004-11-15
10
Medium Priority
?
329 Views
Last Modified: 2010-04-15
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?
0
Comment
Question by:Tom Knowlton
  • 5
  • 5
10 Comments
 
LVL 1

Expert Comment

by:tstoeckigt
ID: 12585812
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
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 12585834
DTS is an option....and I've used DTS for similar projects.

However...for this project I do not want to use DTS.
0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 12585850
Unless I can invoke a DTS script from within a C# Windows Service?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 1

Expert Comment

by:tstoeckigt
ID: 12585900
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
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 12585939
Okay....this is a good option.

Suppose I do not want to use DTS at all?

What are my options?
0
 
LVL 1

Expert Comment

by:tstoeckigt
ID: 12586049
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
 
LVL 1

Expert Comment

by:tstoeckigt
ID: 12586060
It should be "Then you will _read_ for example 8096 bytes or whatever the best pagesize of your disk is." of course.
0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 12586123
Assume there will be about 100 CSV files, each one is about 9 MB.
0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 12586129
The files are not very complex....pretty much just strings
0
 
LVL 1

Accepted Solution

by:
tstoeckigt earned 2000 total points
ID: 12586193
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question