Not able to migrate data from csv file include unix timestamp

Working with Data
, ,
1

Not able to migrate data from csv file include unix timestamp

csv sample ;
timestamp,transaction_type,token,amount
1571967208,DEPOSIT,BTC,0.298660
1571967200,DEPOSIT,ETH,0.683640
1571967189,WITHDRAWAL,ETH,0.493839

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

error output

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

It does not appear the the unix timestamp is supported with csv mongoimport.

Assuming you don’t have control over the csv export process you can preprocess the file before or during import.

Here awk is being used to process the the first field to a timestamp. The mongoimport field is updated to date_ms(yyyy-MM-dd H:mm:ss) and will consume the output of awk.

You may need to use a different awk program(depending on version your have) or another program entirely.

awk -F, '{OFS="," ;$1=strftime("%Y-%m-%d %H:%M:%S", $1); print $0}' import.csv  | \
mongoimport --db=database --collection=collection --type csv --drop --columnsHaveTypes \
--fields="timestamp.date_ms(yyyy-MM-dd H:mm:ss), transaction_type.string(), token.string(), amount.double()"
1 Like