A Simplified SQL-CSV Import/Export Functionality

• Download source - 48.7 Kb

Introduction

This small but important utility has been created for users who would like to understand how to import and export data between a database and CSV files. There are many alternatives to this utility. The (BCP) bulk copy of SQL Server provides a facility to insert multiple records through a single statement. You can also create a nice package to do this job. But I know C# and this is the way I do it. I don't know if I was Googling wrong but didn't find a nice code that can do this functionality in a more generalized manner, and of course, in a way that a normal user (coder) can understand it.

Thank you all for viewing and using this utility. This has helped me to improve and provide something better.

Great News

The application has been updated. What for? Now you can export CSV or text data to your SQL Server or an Access database. Like wise you can export data from SQL Server or an Access database to a CSV or text file. The class files ClsSQLCSV.cs and ClsMSACSV.cs are updated to give more simplicity. Just plug in your control and provide some data to the boxes and you are done. Here is a utility that will allow you to use SQL Server databases as well as MS-Access databases.

Updates

• The screen-looks have been changed to give some more facility.
• Instead of separate screens for SQL Server and Access, now a single screen will provide you with a drop down list to select the database of your choice.
• A separate class file named SetProperties.cs is provided to allow you to set preferences of your choice. You can set different properties like giving the name of the table to import your CSV/text file, and save the CSV/text file to the web server at your specified location (under root folder).
• For export functionality, you can give the table name to export, give the name of the file to be stored on the web server and the location to where it will be stored.
• An additional control is provided (not in screen shot) to transfer data between SQL Server and Access. By providing the name of the table, you can transfer data.

Important Points

This sample is prepared using the Northwind database in SQL Server and the Northwind database in MS-Access. The connection string for SQL Server, Access and CSV files are kept in the Web.Config file. The string is read using the namespace System.Collections.Specialized. Remember to make changes to the connection string in the Config file according to your requirement. The sample CSV files from Northwind are in the folder named Sample (CSVSample.csv, TXTSample.txt). Here is the settings in the Web.Config file:

<appSettings>
  <add key="connsql" value="server=DATABASESERVER;database=Northwind;
                     Trusted_Connection=false;uid=sqluserid;pwd=sqlpassword"/>
  <add key="connmsa" value="Provider=Microsoft.Jet.OleDb.4.0;
                     datasource=C:\\Inetpub\\wwwroot\\exportcsv\\Northwind.mdb" />
  <add key="connxls" value="Provider= Microsoft.Jet.OLEDB.4.0;
                     Extended Properties='text;HDR=Yes;FMT=Delimited';Data Source=" />
</appSettings>

How to Run the Application

• Unzip the files in to a folder (ExportCSV).
• Create a virtual directory in your IIS, named ExportCSV.
• Make appropriate changes to the config file as mentioned above.
• View the sample in the browser using the path: http://localhost/ExportCSV/ImportExport.aspx
• Select the CSV/TXT file from anywhere on your system and click the "Import" button.
• Give the name of the existing table to import the records, or provide a new table name.
• The result of successful completion or failure will be displayed as shown in the above image.
• Click "Export To CSV" to see the same table being exported to the folder named "exported".

Using the Code

Here is some explanation about using the source:

ImportCSV.ascx.cs

Submit_Click

This event is mentioned here to give you an idea about how to pass the data to the functions and complete your task. You can see an instance of SetProperties() to set the various parameters for importing a file to a database. Check out this list of properties and their use:

• TableName: Name of the table (new or existing) where the records will be stored.
• FileInformation: Stores the details of the selected file.
• DropExistingTable: Checks for the table in your database and deletes it only when this flag is set to true (default is false).
• CsvDirOnServer: Name of the folder on your web server where the CSV/text file will be stored. This is useful only when you set SaveFileOnServer is set to true.

Note: Make sure that two of the properties TableName and FileInformation are provided. These two are necessary because without them your CSV/text file will not be exported.

private void Submit_Click(object sender, System.EventArgs e)
{
    try
    {
        FileInfo FICSV = new FileInfo(OFDSelectFile.PostedFile.FileName);
        SetProperties sp = new SetProperties();
    
        if (IsValidFile(FICSV))
        {
            sp.TableName = txtTableName.Text;
            sp.CsvDirOnServer = txtCSVDir.Text;
            sp.DropExistingTable=true;
            sp.SaveFileOnServer=true;
            sp.FileInformation = FICSV;

            switch(ddlDB.SelectedItem.Value)
            {
                case "SQL":    
                    ClsSQLCSV objSQL = new ClsSQLCSV();
                    importstatus.Text = objSQL.GenerateTable(sp);
                    break;
                case "MSA":
                    ClsMSACSV objMSA = new ClsMSACSV();
                    importstatus.Text = objMSA.GenerateTable(sp);
                    break;
            }
        }
    }
    catch(Exception ex)
    {
        importstatus.Text = ex.Message.ToString() + "<br>";
        importstatus.Text += "Error importing. Please try again";
    }
}

ExportCSV.ascx.cs

Submit_Click

This event gives you an idea about how to pass the data to the functions and complete your task of export. Here too you can see an instance of SetProperties() to set various parameters for importing a file to a database. Check out this list of properties and their use:

• ExportTableName: Name of the database table (new or existing) from where the records will be fetched.
• ExportCSVasName: Name of the CSV/text file to store the data.
• ExportCSVDirOnServer: Name of the folder where the CSV/text file will be stored on the web server (note that the folder will take the root path of your application). Default is the root path.
• ExportAsCsvOrText: Default will be "C". This means that the file will be stored with a .csv extension. Not setting this property will also save your file as .csv. You can just provide the name of the file without the extension in the textbox provided to save the file as .csv.

Note: Make sure that two of the properties ExportTableName and ExportCSVasName are provided. These two are necessary because without them your CSV/text file will not be generated.

private void Submit_Click(object sender, System.EventArgs e)
{
    try
    {                
        SetProperties sp = new SetProperties();
        sp.ExportTableName = txtTableName.Text;
        sp.ExportCSVasName = txtCSVFileName.Text;
        sp.ExportCSVDirOnServer = txtCSVDir.Text;
        sp.ExportAsCsvOrText = "T"; //"C" for CSV or "T" for Text
        switch(ddlDB.SelectedItem.Value)
        {
            case "SQL":
                ClsSQLCSV objSQL = new ClsSQLCSV();
                importstatus.Text = objSQL.GenerateCSVFile(sp);
                break;
            case "MSA":
                ClsMSACSV objMSA = new ClsMSACSV();
                importstatus.Text = objMSA.GenerateCSVFile(sp);
                break;
        }
    }
    catch(Exception ex)
    {
        importstatus.Text = ex.Message.ToString() + "<br>";
        importstatus.Text += "Error exporting. Please try again";
    }
}

Library Usage

• System.Data.Odbc (Connection and fetch records from CSV file).
• System.Data.SqlClient (Connection and fetch records from SQL Server database table).
• System.Data.OleDb (Connection and fetch records from Access database table).
• System.Collections.Specialized (NameValueCollection - get key value pair from configuration settings).
• System.Configuration (ConfigurationSettings - provides access to configuration settings in a specified configuration section).
• System.IO (FileStream - read and write the CSV file).

Points of Interest

The two controls for import and export are used in the ImportExport.aspx file. Use them as a separate controls and place them wherever you want on the page/s.

History of Previous Version

The previous version of the utility had no facility to transfer data from a text file. There was no provision for allowing the user to select a CSV file from other locations (except from the web path). No facility was provided for allowing different names to the table other than "TempTable". TempTable was dropped every time before creating a new table. This allowed only new records to be stored.