Wrox Programmer Forums
Go Back   Wrox Programmer Forums > C# and C > C# 1.0 > C#
|
C# Programming questions specific to the Microsoft C# language. See also the forum Beginning Visual C# to discuss that specific Wrox book and code.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the C# section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 28th, 2007, 04:07 AM
Authorized User
 
Join Date: Jun 2007
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default Import CSV files to Database in C#

Hello Expertise,

I want to know how to import csv file and load them in database. I am working using C# on windows based project. A small code snippet will be useful.
I request if you provide me with sample code or direction leading to the code.



Thanks

Prasanna
__________________
Thanks

Prasanna
 
Old August 28th, 2007, 07:53 AM
Authorized User
 
Join Date: Nov 2006
Posts: 93
Thanks: 0
Thanked 1 Time in 1 Post
Default

Prasanna,

I am sure there are several ways to do this. One might be:
1. read the file in using StreamReader (or TextReader) ...

            ' Create an instance of StreamReader to read from a file.
            Using sr As StreamReader = New StreamReader("DataFile.csv")
                Dim line As String
                ' Read and display the lines from the file until the end
                ' of the file is reached.
                Do
                    line = sr.ReadLine()
                    Console.WriteLine(Line)
                Loop Until line Is Nothing
                sr.Close()
            End Using

2. Parse each line using String.Split ...

     string[] arData = line.Split(new Char[] {','});

3. Insert the data into the SQL compliant database using the System.Data.SqlClient namespace classes. This becomes too complex
to give you much code. You need to know the table and column names for each item in the array arData in the input file. And I assume you know some SQL if not, you should probably seek some guidence on an SQL forum.

Good Luck

What you don't know can hurt you!
 
Old September 4th, 2007, 11:03 AM
Registered User
 
Join Date: Sep 2007
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

1. Read in your .csv properly. Take into account that the file can be written in any codepage and if you have to repeat the operation for files from several sources, make sure you can configure the separator.

2. Convert your data and store it in a DataTable. This is very convenient if you want to manipulate your data, or preview it, in for example a DataGridView. Afterwards you can also easily transfer the DataTable to a database with ADO.

3. Possible additions to the code: you could configure the datatype of the columns to be created, add extra logic to avoid adding invalid data etc. Anyway, here's a start. I hope it helps,

Regards,
Joachim

    // Make sure you have these:
    using System.Data;
    using System.IO;

    /// <summary>
    /// Reads in a .csv file.
    /// </summary>
    /// <param name="fileName">File path.</param>
    /// <param name="encoding">Encoding in which the file is written.</param>
    /// <param name="separator">The separator used to separate the fields</param>
    /// <returns></returns>
    public static DataTable ReadFromCsv(string fileName, Encoding encoding, char separator)
    {
        DataTable table = null;

        if (fileName != null &&
        !fileName.Equals(string.Empty) &&
        File.Exists(fileName))
        {
        try
        {
            // If required, you can collect some useful info from the file
            FileInfo info = new FileInfo(fileName);
            string tableName = info.Name;

            // Prepare for the data to be processed into a DataTable
            // We don't know how many records there are in the .csv, so we
            // use a List<string> to store the records in it temporarily.
            // We also prepare a DataTable;
            List<string> rows = new List<string>();

            // Then we read in the raw data
            StreamReader reader = new StreamReader(fileName, encoding);
            string record = reader.ReadLine();
            while (record != null)
            {
            rows.Add(record);
            record = reader.ReadLine();
            }

            // And we split it into chuncks.
            // Note that we keep track of the number of columns
            // in case the file has been tampered with, or has been made
            // in a weird kind of way (believe me: this does happen)

            // Here we will store the converted rows
            List<string[]> rowObjects = new List<string[]>();

            int maxColsCount = 0;
            foreach (string s in rows)
            {
            string[] convertedRow = s.Split(new char[] { separator });
            if (convertedRow.Length > maxColsCount)
                maxColsCount = convertedRow.Length;
            rowObjects.Add(convertedRow);
            }

            // Then we build the table
            table = new DataTable(tableName);
            for (int i = 0; i < maxColsCount; i++)
            {
            // Change this if you want other datatypes
            // make sure you also convert the string[] to
            // the corect datataype
            table.Columns.Add(new DataColumn());
            }

            foreach (string[] rowArray in rowObjects)
            {
            table.Rows.Add(rowArray);
            }
            table.AcceptChanges();
        }
        catch
        {
            throw new Exception("Error in ReadFromCsv: IO error.");
        }
        }
        else
        {
        throw new FileNotFoundException("Error in ReadFromCsv: the file path could not be found.");
        }
        return table;
    }





Similar Threads
Thread Thread Starter Forum Replies Last Post
Import from CSV to MSDE tables dhol SQL Server DTS 14 June 5th, 2007 04:53 AM
Import data to CSV itHighway Classic ASP Basics 0 September 15th, 2006 10:31 AM
Import data in CSV file itHighway Classic ASP Professional 0 September 15th, 2006 10:29 AM
Excel Vs CSV file - import performance itHighway Classic ASP Databases 0 August 5th, 2006 03:40 PM
CSV and Excel sheet import itHighway Classic ASP Professional 0 August 5th, 2006 08:25 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.