Load CSV file in Database

Question

I just started learning C# and I’ve made a few simple applications. The application I’m working on now is an application that reads and enters data to a (Access) database. I connected successfully to the database and I can enter data to it. Now the problem.

I have a csv file with data (orders). I want to load these orders to the database. The csv file looks like:

Order 1: 2 cakes,01-01-2013,chocolate,Jan|Order 2: 5 cakes,01-08-2013,vanilla,Piet|

As you can see it is ordered by (same as in my database table): [Order #], [Amount], [Date], [KindOfCake],[Buyer] and the new order comes right after the halfpipe (|).

This is what I've got:

String[] orders1= File.ReadAllText(@"c:\\orders.csv").Split('|');
for (int i = 0; i < orders1.Length; i++)
{
    textBox1.AppendText(orders[i] + Environment.NewLine);
}

But this will load all the text into one textBox. I'm thinking about loading the data in the csv (seperated with a ',') to different textBoxes and then loading the values of the textBoxes to a database. But that is not the best way I'm guessing.

What is the best way for me to load all data in the CSV to my database?

Thank you very much for the tips.

"CSV can get complicated *real* fast." http://stackoverflow.com/questions/3507498/reading-csv-file and also http://filehelpers.sourceforge.net/ — Corak, Apr 07 '13 at 18:15
Also, it looks like you use textboxes to hold values. Don't do that! textboxes are there to *display* data. If every Order (CSV line; btw, you might want to replace all the | with line breaks (`Environment.NewLine`)) looks the same, make a `class Order` with all the properties you need (OrderNo, Amount etc.) and read the CSV into a `List` and work from there. — Corak, Apr 07 '13 at 18:25

score 2 · Accepted Answer · answered Apr 07 '13 at 18:12

2

Just like you're splitting on the pipe character, you can then split each string on the comma.

String[] orders = File.ReadAllText(@"c:\\orders.csv").Split('|');
foreach (string order in orders) {
    String[] orderFields = order.Split(',');
    // Now you have your fields, put them in the DB. No need to put them
    // into text boxes
}

But, what happens if a comma appears in a field? This wouldn't work for that case.

answered Apr 07 '13 at 18:12

Steve

6,334
4
39
67

Thanks for your reply. How would I insert this data in my database? I would've used this code to enter the data from the values of the textBoxes: cmd.CommandText = "INSERT INTO orders (Amount, Date, KindOfCake, Buyer) values ('" + textBox1.Text + "','" + textBox2.Text + "','" + comboBox2.Text + "','" + comboBox1.Text + "')"; But how do I rewrite this so it'll will fill the database with the content of the array we just made? There won't be a comma in the field, the data is preloaded and a user can't add his/her own data. – David Asssad Apr 07 '13 at 18:20
instead of textBox1.Text you would write something like orderFields[0] etc. – Casperah Apr 07 '13 at 18:23
Here is what I've got: cmd.CommandText = "INSERT INTO tafel (Aantal, Koper, Datum, tijd) values ('" + orderFields[0] + "','" + orderFields[1] + "','" + orderFields[2] + "','" + orderFields[3] + "')"; MessageBox.Show("Done!"); But it somehow does not enter the data into the database. It also gives me no errors. – David Asssad Apr 07 '13 at 18:41
I would recommend reading up on the C# language itself, and then become familiar with the .NET API as well. It's not as simple as creating a SQL command in a string variable - you need to connect to the database and execute the command somehow. If you have yet to discover it, start with http://msdn.microsoft.com/en-us/library/ff361664.aspx, and choose the .NET version you're using. – Steve Apr 08 '13 at 01:10
Forgot a link to the C# guide: http://msdn.microsoft.com/en-us/library/67ef8sbd.aspx. The Entity Framework is a decent way to communicate with databases: http://msdn.microsoft.com/en-us/library/bb399572.aspx. – Steve Apr 08 '13 at 01:16
Thanks for your reply Steve. I am connected with the database and I am already able to execute queries (insert, delete, et cetera) but this querie is somehow not working. Thanks for the useful links! – David Asssad Apr 08 '13 at 08:50
Oh, sorry about that. It wasn't apparent to me that you were already doing that. Re-reading your question and it's now obvious :) – Steve Apr 08 '13 at 12:56

score 1 · Answer 2 · answered Apr 07 '13 at 18:12

I would load a DataTable with the information from your CSV. Then create a connection to your database and insert that DataTable into the database. This link appears to do what you are attempting, with some good code samples: http://www.codeproject.com/Articles/11435/Importing-CSV-Data-and-saving-it-in-database or http://www.codeproject.com/Articles/9258/A-Fast-CSV-Reader.

Load CSV file in Database

2 Answers2