Solved

CSV to Access DB

Posted on 2004-04-18
4
954 Views
Last Modified: 2008-03-03
I'm creating a dataset from a CSV file and would like to put it into an Access DB in the fastest way possible.  
-- I know all of the strucutre and data type information which doesn't change.  
-- I have a table in Access that "mirrors" my CSV file structure.

I've been trying to use an OleDbDataAdaptor connected to my access database to call the Update() method with the CSV dataset and the Access table to simply move the contents without looping, but I haven't been able to make it work.  

Any advise you can give that helps me:
1 (main goal) create a procedure to quickly load my CSV data into Access (I have 40K or less records)
2 (secondary goal) figure out how to my Update() idea to work

Would be greatly appreciated.
0
Comment
Question by:mocs
  • 2
  • 2
4 Comments
 
LVL 5

Accepted Solution

by:
tgannetts earned 500 total points
ID: 10857980
Are you thinking of something like this?

The following code takes the schema of the table in the database and loads it into a dataset. The values from the CSV File are then loaded row-by-row into the table, and then updated using the Update command.

using System.Data.OleDb;

...

//Create connection strings
string sCSVConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\;Extended Properties=""text;HDR=Yes;FMT=Delimited""";      
string sDBConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\Test.mdb;";
string sCSVFile = "Test.csv";
      
try
{
     //Create Empty Dataset
     DataSet ds = new DataSet("Test");

     //Create Connection Classes  
     OleDbConnection dbcnn = new OleDbConnection(sDBConnectionString);
     OleDbConnection csvcnn = new OleDbConnection(sCSVConnectionString);

     //Open Data Adapter
     OleDbDataAdapter daDB = new OleDbDataAdapter("SELECT * FROM tbl_Test", dbcnn);

     //Map Schema to Dataset
     daDB.FillSchema(ds, SchemaType.Mapped, "tbl_Test");
                  
     //Open CSV File
     csvcnn.Open();

     OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sCSVFile + "]", csvcnn);
     OleDbDataReader dr = cmd.ExecuteReader();

     //Read CSV Values into DataTable
     while(dr.Read())
     {
          //Get array of values and add to datatable held in dataset
          object[] values = new object[(ds.Tables[0].Columns.Count)];
          dr.GetValues(values);
          ds.Tables[0].Rows.Add(values);
     }

     //Close connection to CSV File
     csvcnn.Close();
                  
     //Add Rows from CSV File to Database Table
     OleDbCommandBuilder cb = new OleDbCommandBuilder(daDB);
     daDB.Update(ds, "tbl_Test");
                        
}
catch(Exception ex)
{
     MessageBox.Show(ex.Message);
}
0
 

Author Comment

by:mocs
ID: 10859482
Thanks, tgannetts.  This is exactly what I've been playing with, and I have a couple of questions about your implementation:
1.  You're using OleDbCommandBuilder to overide the need for an InsertCommand; is that correct?  Is this faster running than an insertCommand with a parameterized query?
2.  I noticed you're using a dataReader and looping through the rows of the CSV file versus using a second DataApater for the CSV file and doing something like: daCSV.Fill(ds);  Why is that?
3.  I found that with a parameterized InsertCommand (the only way I could get this to work), I had to set all of the ColumnName properties on the CSV ds in order to get the daDB.Update(ds,"tbl"); to work.  Why doesn't your solution need that?
0
 
LVL 5

Expert Comment

by:tgannetts
ID: 10859959
Hi Mocs,

Re your questions:

1. I've found from my experience that using the CommandBuilder as opposed to the InsertCommand makes little difference in time. Having said this, I would definitely use a DeleteCommand and UpdateCommand instead of the CommandBuilder as the CommandBuilder generates a lot of unnecessary SQL code and makes an extra round trip to the database for these actions.
2. The Update method checks the rowstate of each row to determine the correct command action to take, if any. The Fill method using a dataadapter fills the datatable with the rows from the CSV and sets the rowstate to 'Unchanged', which will be ignored by the Update. The new rows added to the datatable whilst reading from the Data Reader are set to 'Added', which will be picked up by the Update method.
3. In this method, the data adapter creates the datatable to store the CSV values using the FillSchema method, which sets all the column properties as per the database table. When the values from the CSV are filled into this table, each one has to match this schema otherwise they will be ignored.

Hope this explains my methods a little...

Tom.

0
 

Author Comment

by:mocs
ID: 10861410
"2. The Update method checks the rowstate of each row to determine the correct command action to take, if any. The Fill method using a dataadapter fills the datatable with the rows from the CSV and sets the rowstate to 'Unchanged', which will be ignored by the Update. The new rows added to the datatable whilst reading from the Data Reader are set to 'Added', which will be picked up by the Update method."

Oh, I see.  Would it work to set daCSV.AcceptChangesDuringFill = false;?
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now