mongodbmongoosemongodb-querydata-migrationmongoimport

Not able to migrate data to mongodb from csv file include unix timestamp

csv sample

timestamp,name,amount
1571967208,Rahul,15.7
1571967200,Raju,25.7

code sample

mongoimport --db=crypto --collection=t --type=csv \
   --columnsHaveTypes \
   --fields="timestamp.date(), name.string(), amount.double()" \
   --file="text.csv"

result

Failed: type coercion failure in document #1 for column 'timestamp', could not parse token '1571967208' to type date
Solution

  • You could pre-process your CSV into MongoDB Extended JSON (v2) using jq with something like:

    jq --null-input --raw-input 'input | split(",") as $fields | inputs | split(",") as $values |{($fields[0]): {"$date": {"$numberLong": ($values[0]+"000")}}, ($fields[1]): $values[1], ($fields[2]): ($values[2] | tonumber)}' yourFile.csv > yourFile.json

    This would transform your example CSV into:

    {
  "timestamp": {
    "$date": {
      "$numberLong": "1571967208000"
    }
  },
  "name": "Rahul",
  "amount": 15.7
}
{
  "timestamp": {
    "$date": {
      "$numberLong": "1571967200000"
    }
  },
  "name": "Raju",
  "amount": 25.7
}

    See jqplay.org example.

    and then:

    mongoimport --db=crypto --collection=t --file=yourFile.json