Click here to Skip to main content
14,775,673 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello friends,

I am trying to import CSV file but I have many problems.

Like, how to read csv file and then how to insert the value in the database?

Can any one guide me ?


Regards
Deepak
Posted
Updated 15-Mar-16 0:27am
v2
Comments
Dalek Dave 17-Jun-11 3:38am
   
Edited for Grammar and Readability.
guptaadeepak 17-Jun-11 5:29am
   
next time i will be remember.

try this link

Import CSV File[^]
   
Comments
Dalek Dave 17-Jun-11 3:38am
   
Good Link
There are many ways to achieve this.
One ways can be
Step1:Try to read all your data in to a datatable ( assuming 1st line of CSV represents the ColumnName)
var values = GetAllLines();
                DataTable dt = new DataTable();
                foreach (var item in values.FirstOrDefault())
                {
                    if (!dt.Columns.Contains(item))
                        dt.Columns.Add(item);
                }
                var allvalues=values.Skip(1).ToList();
                for (int i = 0; i < allvalues.Count; i++)
                {
                    dt.Rows.Add(allvalues.Take(dt.Columns.Count).ToArray());
                }
Description for Get All Lines function
private IEnumerable<string[]> GetAllLines()
        {
            string str;
            using (StreamReader rd=new StreamReader(txtFileName.Text))
            {
                while ((str = rd.ReadLine()) != null)
                {
                    yield return str.Split(',');
                }
            }
        }
STEP2Then you can use SQL Bulk Copy to Copy the Entire datatable to database. Where you need to set the destination table and Column Mapping, in my case i assume the column name is same as that of datatable
SqlConnection dbconnection = new SqlConnection("your Connection string.");
SqlBulkCopy sqlblkcpy=new SqlBulkCopy(dbconnection);
dbconnection.Open();
sqlblkcpy.DestinationTableName = "<Table Name To Which you want to Copy your Data>";
foreach (DataColumn item in dt.Columns)
{
    sqlblkcpy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(item.ColumnName, item.ColumnName));
}
if(dt.RecordCount>0)
    sqlblkcpy.WriteToServer(dt);
   
Comments
Dalek Dave 17-Jun-11 3:38am
   
Good Answer.
Robin Saini 22-May-12 8:07am
   
var values = GetAllLines();



foreach (var item in values.FirstOrDefault())
{
if (!dt.Columns.Contains(item))
dt.Columns.Add(item);
}
var allvalues = values.Skip(1).ToList();
for (int i = 0; i < allvalues.Count; i++)
{
dt.Rows.Add(allvalues[i].Take(dt.Columns.Count).ToArray());
}
gridView.DataSource = dt;
gridView.DataBind();
Try to this code.
Please start properly

STEP1: IN THE PAGE ( sample.aspx)

INSERT THE FOLLOWING CODE:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="sample.aspx.cs" Inherits="sample" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        &nbsp;Select File:
        <asp:FileUpload ID="FileUploader" runat="server" />
        <br />
        <br />
        <asp:Button ID="UploadButton" runat="server" Text="Upload" OnClick="UploadButton_Click" /><br />
        <br />
        <asp:Label ID="Label1" runat="server"></asp:Label></div>
    </form>
</body>
</html>


STEP2:
in the code page say for example ( sample.aspx.cs)

INSERT THE FOLLOWING CODE:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class sample : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void UploadButton_Click(object sender, EventArgs e)
    {
        if (FileUploader.HasFile)
            try
            {
                FileUploader.SaveAs(Server.MapPath("confirm//") +
                     FileUploader.FileName);
                Label1.Text = "File name: " +
                     FileUploader.PostedFile.FileName + "<br>" +
                     FileUploader.PostedFile.ContentLength + " kb<br>" +
                     "Content type: " +
                     FileUploader.PostedFile.ContentType + "<br><b>Uploaded Successfully";
            }
            catch (Exception ex)
            {
                Label1.Text = "ERROR: " + ex.Message.ToString();
            }
        else
        {
            Label1.Text = "You have not specified a file.";
        }

    }
}
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900