Import CSV file to Mongo database

Question

If I want to import a CSV file from the command line I simply use:

mongoimport -d <database> -c <collection name> --type csv --file <path to csv> --headerline

Of course headerline is optional. In my case, the CSV files do have a header.

How can I do the same via C#? Is there a similar one line command? I know how to read a CSV file but I'm surprised I cannot find (a single?) simple command(s).

I have looked at a lot of the online documentation but much of it seems to be for a different .NET driver version; mine is version 2.2.4.

Here is the long way around code so far (it works but I'm thinking it can be done more easily):

MongoClient client = new MongoClient("mongodb://127.0.0.1:27017/test"); // local database
var db = client.GetDatabase("test");

var reader = new StreamReader(File.OpenRead(@"<full path to csv")); // where <full path to csv> is the file path, of course
IMongoCollection<BsonDocument> csvFile = db.GetCollection<BsonDocument>("test");

reader.ReadLine(); // to skip header

while (!reader.EndOfStream)
{
    var line = reader.ReadLine();
    var values = line.Split(',');

        BsonDocument row = new BsonDocument
        {
            {"Column0", values[0]},
            {"Column1", values[1]},
            {"Column2", values[2]},
            {"Column3", values[3]}
        };

    csvFile.InsertOne(row);
}

One disadvantage of this format is that I must have exactly four columns - which I cannot guarantee.

The perfect answer will include a way to skip the header row.

_{In case it is relevant: I'm looking to import multiple CSV files so I will find each in a directory - but I know how to do that.}

Rather than add the key/value pairs in the BsonDocument constructor you can create a Dictionary containing the pairs, and then add that to the BsonDocument using row.AddRange(dictionary). — ProfNimrod, Mar 29 '17 at 23:17

score 2 · Answer 1 · answered Aug 27 '19 at 08:57

I used CSVHelper to read it. PM console -> https://www.nuget.org/packages/CsvHelper/

I've done it in 3 classes :

Program.cs (Where I read the actual .csv and call for object creating class)
Products.cs (The collection details and .csv cols must be the same)
Mongo.cs (Connecting to MongoDB and inserting or updating documents)

Program.cs:

using CsvHelper;
using System;
using System.IO;
using System.Linq;

namespace DataImport
{
    class Program
    {
        static void Main(string[] args)
        {
            // .CSV file path
            Console.WriteLine("Absolute path to .csv file: ");
            string csvFilePath = Console.ReadLine();

            // Reading .csv file line by line and calling for SendingRecord method
            using (var reader = new StreamReader(csvFilePath))
            using (var csv = new CsvReader(reader))
        {
            csv.Configuration.HasHeaderRecord = false; // My file has no header lines, if yours have this must be 'true'
            csv.Configuration.ShouldSkipRecord = record => record.All(string.IsNullOrEmpty); // Skipping empty lines in .CSV file
            var records = csv.GetRecords<Products>().ToList();
            for (int i = 0; i < records.Count; i++)
            {
                Mongo.SendingRecord(records[i]);
            }
        }
    }
}

That creates List of objects witch can be put separately in mongoDB documents.

Products.cs:

using CsvHelper.Configuration;
using CsvHelper.Configuration.Attributes;

namespace DataImport
{
public class Products
{
    [Index(0)]
    public string prop1
    {
        get;
        set;
    }

    [Index(1)]
    public decimal prop2
    {
        get;
        set;
    }

    [Index(2)]
    public int prop3
    {
        get;
        set;
    }

public class ProductsMap : ClassMap<Products>
{
    public ProductsMap()
    {
        Map(m => m.prop1);
        Map(m => m.prop2);
        Map(m => m.prop3);
    }
}

Here you need to specify all rows of the .csv file and map them properly. The indexes represent row in excell (index0 = A, index1 = B ...)

Mongo.cs:

using MongoDB.Driver;

namespace DataImport
{
class Mongo
{
    public static void SendingRecord(Products output)
    {
        // Connecting to MongoDB
        string connectionString = "mongodb://localhost:27017";
        MongoClient mongoClient = new MongoClient(connectionString);

        // Navigating to DB and Collection
        var db = mongoClient.GetDatabase("DB-name");

        var products = db.GetCollection<Products>("Collection-Name");

        // Importing new documents or updating existing ones
        var options = new UpdateOptions();
        options.IsUpsert = true;
        products.ReplaceOne(filter: x => x.uniqueField == output.uniqueField, replacement: output, options: options);
    }
}

This is with connection string for localhost. For this to work u need at least one unique value for the records - id, some sort of number, etc.

If u have any questions, don't hesitate. Just ask.

Thanks! Unfortunately, I can no longer verify this solution (I've moved on to a new project). If someone else confirms that this works, I will accept. — Reinstate Monica - Goodbye SE, Aug 27 '19 at 09:01
Yeah. I saw it was from 3y ago, but i tried to do this for like 2-3 days and the are no realy usefull docs, so when i managed to do it, i decided to share. — Hristo Kovachev, Aug 27 '19 at 10:49
Great, and you have my +1 for sharing! Hope it helps more people. — Reinstate Monica - Goodbye SE, Aug 27 '19 at 12:51
Excellent solution, it's a pity this is not the selected answer — Krishnan Venkiteswaran, Jan 28 '20 at 10:11

RakouskyS · Answer 2 · 2016-07-19T13:26:41.973

0

You use that command on the cmd? I suggest you create a .bat file with a StreamWriter then execute that .bat File with Process.Start(), passing it the filename as a parameter.

Way better: To execute on the commandline use this snippet

string command = ""; //enter any command you want
System.Diagnostics.Process process = new System.Diagnostics.Process();
System.Diagnostics.ProcessStartInfo startInfo = new System.Diagnostics.ProcessStartInfo();
startInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;
startInfo.FileName = "cmd.exe";
startInfo.Arguments = "/C " + command;
process.StartInfo = startInfo;
process.Start();

edited Jul 19 '16 at 13:26

answered Jul 19 '16 at 13:19

RakouskyS

54
6

Indeed, I was considering that as a backup plan. But there must be a way to do it directly in `C#` code, I think? – Reinstate Monica - Goodbye SE Jul 19 '16 at 13:20
Thanks, but I would rather do it directly in the code. I can now but am thinking that there must be a more efficient way... – Reinstate Monica - Goodbye SE Jul 19 '16 at 13:52

score 0 · Answer 3 · answered Nov 22 '22 at 22:13

No need to create an external .bat file. Just run the command using windows process start :-)

Download "MongoDB Command Line Database Tools"-

https://www.mongodb.com/try/download/database-tools

(1) Unzip the file and copy mongoimport.exe and paste it in your project (right-click on the project from solution explorer and paste)

(2) after paste, right-click on the mongoimport.exe file from solution explorer and choose properties.

(3) then change the property value of "Copy to Output Directory" to "Copy always"

(4) follow the bellow code-

namespace ConsoleApp1
{
  class Program
  {
    static void Main(string[] args)
    {
     string command = "--db yourDBName --collection yourCollectionName --type csv --file D:\\yourFileName.csv --headerline"; 
        System.Diagnostics.Process process = new System.Diagnostics.Process();
        System.Diagnostics.ProcessStartInfo startInfo = new System.Diagnostics.ProcessStartInfo();
        startInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;
        startInfo.FileName = "mongoimport.exe";
        startInfo.Arguments =  command;
        process.StartInfo = startInfo;
        process.Start();
      }
    }
}

Import CSV file to Mongo database

3 Answers3