[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

CSV to Access DB

Posted on 2004-04-18
4
Medium Priority
?
986 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 5

Accepted Solution

by:
tgannetts earned 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
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.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

649 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