?
Solved

CSV to Access DB

Posted on 2004-04-18
4
Medium Priority
?
974 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Suggested Courses

762 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