Click here to Skip to main content
Click here to Skip to main content

A Simplified SQL-CSV Import/Export Functionality

, 20 Oct 2005
Rate this:
Please Sign up or sign in to vote.
A simplified SQL-CSV import/export functionality.

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

codeajay
Software Developer (Senior)
India India
He is a Professional developer and project leader, working on .Net Technologies. He works in a firm based in the Hub of IT - Bangalore. His core interests are C#, ASP.net and any microsoft technology that brings him near to Web. Likes to share his views and ideas that help other developers.

Comments and Discussions

 
QuestionWorking locally on SQL server but not when connecting from networked computers PinmemberEssbe16-Dec-11 6:25 
QuestionChanging the Text Field to a DDL PinmemberGRJThomas28-Sep-11 4:19 
AnswerRe: Changing the Text Field to a DDL Pinmembercodeajay29-Sep-11 22:09 
QuestionThe Microsoft Jet database engine could not find the object 'test.txt'. Make sure the object exists and that you spell its name and the path name correctly. PinmemberLam pat11-May-11 20:59 
AnswerRe: The Microsoft Jet database engine could not find the object 'test.txt'. Make sure the object exists and that you spell its name and the path name correctly. PinmemberLam pat11-May-11 21:01 
GeneralMy vote of 4 Pinmemberthinkpad_r50024-Apr-11 20:20 
GeneralMy vote of 1 PinmemberMember 28192073-Mar-11 0:18 
GeneralMy vote of 5 PinmemberAxim8-Nov-10 13:29 
QuestionMaintain the original data type? PinmemberDimitro913-Jan-10 11:55 
Generalcannot find table but can export from db to csv Pinmembernicol_kun14-Sep-09 19:22 
QuestionWill the functionality work correctly if even the data in the rows has commas? PinmemberParag Sawant17-Apr-09 4:59 
GeneralRe: Great Utility - Needs Support Pinmembercodeajay19-Jan-09 20:02 
GeneralRe: Great Utility - Needs Support Pinmembercodeajay22-Jan-09 7:26 
GeneralVisual Studio 2008 PinmemberPierre6519-Dec-08 2:13 
GeneralRe: Visual Studio 2008 Pinmembercodeajay31-Dec-08 21:34 
QuestionAre you missing an assembly reference? PinmemberSwank_e10-Oct-08 11:57 
GeneralExcellent Article PinmemberMember 17614062-Sep-08 22:36 
QuestionCan not find table 0 Pinmemberhirens126-May-08 21:54 
Questionwhy tables coloum only 1 ?? Pinmemberslayer_stb5-Nov-07 23:05 
QuestionCannot Find Table 0 Pinmemberstfu_thx1-Oct-07 18:04 
AnswerRe: Cannot Find Table 0 PinmemberMember 501173520-Jan-10 1:12 
Questionanyway to get this in vb? Pinmembersmcmiata27-Aug-07 10:16 
QuestionCan't find table 0 Pinmemberiwebhost4u6-Aug-07 9:32 
AnswerRe: Can't find table 0 Pinmemberdeepakdhakal7-Aug-07 8:00 
GeneralRe: Can't find table 0 Pinmemberdeepakdhakal7-Aug-07 8:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 20 Oct 2005
Article Copyright 2005 by codeajay
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid