Click here to Skip to main content
15,498,859 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[^]
 
Share this answer
 
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);
 
Share this answer
 
Comments
Dalek Dave 17-Jun-11 3:38am    
Good Answer.
Robin Saini 22-May-12 8:07am     CRLF
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

XML
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.";
        }

    }
}
 
Share this answer
 

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