Saturday, August 21, 2010

How to import CSV or Text file to SQL table using SqlBulkCopy in C#

I will try to keep the story brief as much as I can.
We need 3 separate elements in order to import data to SQL table. I use SQL Server as the database engine.

Download Source Files

1.  Schema.ini 
CSV Delimited Format - For CSV files


TAB Delimited Format - For text files


2.  CSV or Text File
CSV File - Separated by comma


Text File - Separated by TAB


3.  SQL Table 


To simply the process I have developed a DelimitedDataImporter class which can be found at the end of this post. You can use an instance of it for this purpose.  Below are the five steps that you may need to follow in-order to import data to SQL table.
  1. Upload a file
  2. Instantiate DelimitedDataImporter and set the connection string
  3. Find existing records and set filter statement [OPTIONAL]
  4. Set column mappings
  5. Import data to SQL table
Example page:
<%@ Page Language="C#" %>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <script runat="server">
        protected void Import(object sender, EventArgs e)
        {
            String path = Server.MapPath("~/Data/");
            try
            {
                if (this.fuFile.HasFile)
                {
                    /// 
                    /// Upload file
                    ///
                    string fileName = this.fuFile.FileName;
                    string fullName = Path.Combine(path, fileName);
                    if (File.Exists(fullName)) File.Delete(fullName);
                    this.fuFile.PostedFile.SaveAs(fullName);
                    ///
                    /// Instantiate DelimitedDataImporter and set connection string
                    ///
                    DelimiterType type = DelimiterType.CsvDelimited;
                    if (this.ddlFileType.SelectedValue.Equals("TAB")) type = DelimiterType.TabDelimited;
                    DelimitedFileImporter importer = new DelimitedFileImporter(type);
                    importer.ConnectionString =
                        ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                    ///
                    /// Find existing records and set filter statement [OPTIONAL]
                    ///
                    StringBuilder sb = new StringBuilder(string.Empty);
                    using (SqlConnection con = new SqlConnection(
                        ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
                    {
                        SqlCommand cmd = new SqlCommand("SELECT Id FROM Student", con);
                        con.Open();
                        SqlDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                            sb.AppendFormat("{0},", reader.GetInt32(0).ToString());
                        reader.Close();
                        con.Close();
                    }
                    string ids = string.Empty;
                    if (!string.IsNullOrEmpty(sb.ToString())) ids = sb.ToString().Remove(sb.Length - 1, 1);
                    if (!string.IsNullOrEmpty(ids)) importer.Filter =
                        string.Format("Id NOT IN ({0})", ids);
                    ///
                    /// Set column mappings
                    ///
                    importer.ColumnMappings.Add(new ColumnMapping("Id""Id"));
                    importer.ColumnMappings.Add(new ColumnMapping("Name""Name"));
                    importer.ColumnMappings.Add(new ColumnMapping("Grade""Grade"));
                    ///
                    /// Import data to sql table
                    ///
                    importer.ImportToDatabase(fullName, "Student");
                }
            }
            catch
            {
                throw new Exception("Import Faild");
            }
        }        
    </script>
</head>
<body>
    <form id="form1" runat="server">
        <asp:FileUpload runat="server" ID="fuFile" />
        <asp:Button runat="server" ID="btnImport" OnClick="Import" Text="Import" />
        <asp:DropDownList runat="server" ID="ddlFileType">
            <asp:ListItem Value="CSV" Selected="True">CSV Delimited</asp:ListItem>
            <asp:ListItem Value="TAB">Tab Delimited</asp:ListItem>
        </asp:DropDownList>
    </form>
</body>
</html>
 

DelimitedDataImporter implementation:
#region EventArgs Specification
 
public enum DelimiterType
{
    TabDelimited,
    CsvDelimited,
    CustomDelimited
}
public class ImportDelimitedEventArgs : EventArgs
{
    private ReadOnlyCollection<object> content;
    private int lineNumber;
 
    public ImportDelimitedEventArgs(OleDbDataReader reader, int number)
    {
        object[] columns = new object[reader.FieldCount];
        reader.GetValues(columns);
        content = new ReadOnlyCollection<object>(columns);
        lineNumber = number;
    }
    public bool BreakImport { getset; }
    public int LineNumber
    {
        get { return lineNumber; }
    }
    public ReadOnlyCollection<object> Content
    {
        get { return content; }
    }
}
 
#endregion
 
#region Mappings
 
public class ColumnMapping
{
    public string SourceColumn { getset; }
    public string DestinationColumn { getset; }
    public ColumnMapping(string sourceColumn, string destinationColumn)
    {
        this.SourceColumn = sourceColumn;
        this.DestinationColumn = destinationColumn;
    }
}
 
#endregion
 
#region Importer
 
public class DelimitedFileImporter
{
    #region Instance values
 
    private string customDelimiter;
    private string filter;
    private DelimiterType delimiter = DelimiterType.TabDelimited;
 
    #endregion
 
    #region Event handler
 
    public event EventHandler<ImportDelimitedEventArgs> ProcessLine;
    private bool HandleLine(OleDbDataReader reader, int number)
    {
        bool result = true;
        EventHandler<ImportDelimitedEventArgs> temp = ProcessLine;
        if (temp != null)
        {
            ImportDelimitedEventArgs args = new ImportDelimitedEventArgs(reader, number);
            temp(this, args);
            result = !args.BreakImport;
        }
        return result;
    }
 
    #endregion
 
    #region Constructors
 
    public DelimitedFileImporter()
        : this(DelimiterType.TabDelimited, null)
    {
        this.Initialize();
    }
    public DelimitedFileImporter(DelimiterType delimiter)
        : this(delimiter, null)
    {
        this.Initialize();
    }
    public DelimitedFileImporter(DelimiterType delimiterType, string delimiter)
    {
        this.delimiter = delimiterType;
        customDelimiter = delimiter;
        this.Initialize();
    }
    private void Initialize()
    {
        this.ColumnMappings = new List<ColumnMapping>();
    }
 
    #endregion
 
    #region Properties
 
    public DelimiterType Delimiter
    {
        get { return delimiter; }
        set { delimiter = value; }
    }
    public string CustomDelimiter
    {
        get { return customDelimiter; }
        set { customDelimiter = value; }
    }
    public string Filter
    {
        get { return filter; }
        set { filter = value; }
    }
    public string ConnectionString { getset; }
    public List<ColumnMapping> ColumnMappings { getset; }
 
    #endregion
 
    #region Methods
        
    public void ImportToDatabase(string fileName, string tableName)
    {
        if (string.IsNullOrEmpty(this.ConnectionString)) 
            throw new ArgumentNullException("Connection String");
        FileInfo file = new FileInfo(fileName);
        WriteSchemaIniFile(file);
        using (OleDbConnection con = JetConnection(file))
        {
            using (OleDbCommand cmd = JetCommand(file, con))
            {
                con.Open();
                using(OleDbDataReader reader = cmd.ExecuteReader())
                {
                    SqlBulkCopy sqlBulk = new SqlBulkCopy(this.ConnectionString);
                    sqlBulk.DestinationTableName = tableName;
                    foreach (ColumnMapping map in this.ColumnMappings)
                        sqlBulk.ColumnMappings.Add(map.DestinationColumn, map.SourceColumn);
                    sqlBulk.WriteToServer(reader);                    
                }                    
            }
        }            
    }
    private OleDbConnection JetConnection(FileInfo file)
    {
        StringBuilder connection = new StringBuilder("Provider=Microsoft.Jet.OLEDB.4.0");
        connection.AppendFormat(";Data Source=\"{0}\"", file.DirectoryName);
        connection.Append(";Extended Properties='text;HDR=Yes");
        if (delimiter == DelimiterType.CustomDelimited)
        {
            if (customDelimiter == null)
                throw new InvalidOperationException("Custom delimiter is not specified");
            connection.AppendFormat(";FMT=Delimited({1})", customDelimiter);
        }
        connection.Append("';");
        return new OleDbConnection(connection.ToString());
    }
    private OleDbCommand JetCommand(FileInfo file, OleDbConnection con)
    {
        StringBuilder commandText = new StringBuilder("SELECT * FROM ");
        commandText.AppendFormat("[{0}]", file.Name);
        if (filter != null)
        {
            commandText.Append(" WHERE ");
            commandText.Append(filter);
        }
        OleDbCommand cmd = new OleDbCommand(commandText.ToString(), con);
        cmd.CommandTimeout = 60000;
        return cmd;
    }
    private void WriteSchemaIniFile(FileInfo file)
    {
        string schema = Path.Combine(file.DirectoryName, "Schema.ini");
        if (!File.Exists(schema)) File.Delete(schema);
        using (StreamWriter writer = new StreamWriter(schema))
        {
            writer.WriteLine(string.Format(CultureInfo.InvariantCulture, "[{0}]", file.Name));
            switch (delimiter)
            {
                case DelimiterType.CustomDelimited:
                    writer.WriteLine(string.Format(CultureInfo.InvariantCulture, 
                        "Format=Delimited({0})", customDelimiter));
                    break;
                case DelimiterType.CsvDelimited:
                case DelimiterType.TabDelimited:
                default:
                    writer.WriteLine(string.Format(CultureInfo.InvariantCulture, 
                        "Format={0}", delimiter));
                    break;
            }
        }
    }
 
    #endregion
}
 
#endregion

Reference

No comments:

iPhone Launch Screen Sizes

iPhone Portrait iOS 8 Retina HT 5.5 = 1242 X 2208 Retna HD 4.7 = 750 X 1134 iPhone Landscape iOS 8 Retina HD 5.5  2208 X 1242 iPho...