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

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!

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;
    }