Importing CSV Data
🔧 Configuration
RESTHeart provides a simple and efficient way to import data from CSV files directly into MongoDB collections. This service makes it easy to migrate data from spreadsheets or other tabular data sources.
What is CSV?
CSV (Comma-Separated Values) is a simple file format used to store tabular data. Each line represents a row, and values are separated by commas. CSV files can be exported from and imported into most spreadsheet applications like Microsoft Excel, Google Sheets, and Apple Numbers.
CSV Upload Service
RESTHeart’s CSV Upload Service allows you to:
-
Import data from CSV files into MongoDB collections
-
Create one document per row of the CSV file
-
Map CSV columns to document fields
-
Update existing documents when needed
-
Add additional properties to all imported documents
This service is bound to the /csv
API endpoint by default.
Basic CSV Import
To import CSV data, send a POST
request with the CSV content to the /csv
endpoint:
cURL
curl -X POST "[INSTANCE-URL]/csv?db=mydb&coll=locations" \
-H "Authorization: Basic [BASIC-AUTH]" \
-H "Content-Type: text/csv" \
-d 'id,name,city,lat,lon,note
1,Coliseum,Rome,41.8902614,12.4930871,Famous landmark
2,Eiffel Tower,Paris,48.8583701,2.2944813,Iconic structure
3,Statue of Liberty,New York,40.6892494,-74.0445004,Symbol of freedom'
HTTPie
http POST "[INSTANCE-URL]/csv?db=mydb&coll=locations" \
Authorization:"Basic [BASIC-AUTH]" \
Content-Type:text/csv \
id,name,city,lat,lon,note
1,Coliseum,Rome,41.8902614,12.4930871,Famous landmark
2,Eiffel Tower,Paris,48.8583701,2.2944813,Iconic structure
3,Statue of Liberty,New York,40.6892494,-74.0445004,Symbol of freedom
JavaScript
const csvData = `id,name,city,lat,lon,note
1,Coliseum,Rome,41.8902614,12.4930871,Famous landmark
2,Eiffel Tower,Paris,48.8583701,2.2944813,Iconic structure
3,Statue of Liberty,New York,40.6892494,-74.0445004,Symbol of freedom`;
fetch('[INSTANCE-URL]/csv?db=mydb&coll=locations', {
method: 'POST',
headers: {
'Authorization': 'Basic [BASIC-AUTH]',
'Content-Type': 'text/csv'
},
body: csvData
})
.then(response => response.json())
.then(data => console.log(data))
.catch(error => console.error('Error:', error));
Important
|
The Content-Type header must be set to text/csv .
|
This creates three documents in the locations
collection of the mydb
database, with fields corresponding to the CSV columns.
Query Parameters
The CSV upload service is controlled by these query parameters:
Parameter | Description | Default |
---|---|---|
|
Required. The database name |
None |
|
Required. The collection name |
None |
|
Column index to use as document |
None |
|
Column separator character |
|
|
If |
|
|
When |
|
|
Additional property names to add to each row |
None |
|
Values for the additional properties |
None |
Advanced Usage Examples
Using a Custom Column as Document ID
To use a specific column as the document _id
, specify its index (starting from 0):
cURL
curl -X POST "[INSTANCE-URL]/csv?db=mydb&coll=products&id=0" \
-H "Authorization: Basic [BASIC-AUTH]" \
-H "Content-Type: text/csv" \
-d 'product_code,name,price,category
ABC123,Deluxe Widget,29.99,Tools
XYZ456,Premium Gadget,49.99,Electronics'
HTTPie
http POST "[INSTANCE-URL]/csv?db=mydb&coll=products&id=0" \
Authorization:"Basic [BASIC-AUTH]" \
Content-Type:text/csv \
product_code,name,price,category
ABC123,Deluxe Widget,29.99,Tools
XYZ456,Premium Gadget,49.99,Electronics
JavaScript
const csvData = `product_code,name,price,category
ABC123,Deluxe Widget,29.99,Tools
XYZ456,Premium Gadget,49.99,Electronics`;
fetch('[INSTANCE-URL]/csv?db=mydb&coll=products&id=0', {
method: 'POST',
headers: {
'Authorization': 'Basic [BASIC-AUTH]',
'Content-Type': 'text/csv'
},
body: csvData
})
.then(response => response.json())
.then(data => console.log(data))
.catch(error => console.error('Error:', error));
This uses the product_code
column (index 0) as the _id
for each document.
Using a Different Separator
For tab-separated or other delimiter formats:
cURL
curl -X POST "[INSTANCE-URL]/csv?db=mydb&coll=employees&sep=%09" \
-H "Authorization: Basic [BASIC-AUTH]" \
-H "Content-Type: text/csv" \
-d $'id\tname\tdepartment\tsalary\n1001\tJohn Smith\tEngineering\t75000\n1002\tMaria Garcia\tMarketing\t82000'
HTTPie
http POST "[INSTANCE-URL]/csv?db=mydb&coll=employees&sep=%09" \
Authorization:"Basic [BASIC-AUTH]" \
Content-Type:text/csv \
$'id\tname\tdepartment\tsalary\n1001\tJohn Smith\tEngineering\t75000\n1002\tMaria Garcia\tMarketing\t82000'
JavaScript
const csvData = `id\tname\tdepartment\tsalary
1001\tJohn Smith\tEngineering\t75000
1002\tMaria Garcia\tMarketing\t82000`;
fetch('[INSTANCE-URL]/csv?db=mydb&coll=employees&sep=%09', {
method: 'POST',
headers: {
'Authorization': 'Basic [BASIC-AUTH]',
'Content-Type': 'text/csv'
},
body: csvData
})
.then(response => response.json())
.then(data => console.log(data))
.catch(error => console.error('Error:', error));
Note
|
URL-encode the separator character. For tabs, use %09 .
|
Updating Existing Documents
To update documents that already exist (based on _id
):
cURL
curl -X POST "[INSTANCE-URL]/csv?db=mydb&coll=products&id=0&update=true" \
-H "Authorization: Basic [BASIC-AUTH]" \
-H "Content-Type: text/csv" \
-d 'product_code,name,price,category,in_stock
ABC123,Deluxe Widget,24.99,Tools,true
XYZ456,Premium Gadget,44.99,Electronics,false'
HTTPie
http POST "[INSTANCE-URL]/csv?db=mydb&coll=products&id=0&update=true" \
Authorization:"Basic [BASIC-AUTH]" \
Content-Type:text/csv \
product_code,name,price,category,in_stock
ABC123,Deluxe Widget,24.99,Tools,true
XYZ456,Premium Gadget,44.99,Electronics,false
JavaScript
const csvData = `product_code,name,price,category,in_stock
ABC123,Deluxe Widget,24.99,Tools,true
XYZ456,Premium Gadget,44.99,Electronics,false`;
fetch('[INSTANCE-URL]/csv?db=mydb&coll=products&id=0&update=true', {
method: 'POST',
headers: {
'Authorization': 'Basic [BASIC-AUTH]',
'Content-Type': 'text/csv'
},
body: csvData
})
.then(response => response.json())
.then(data => console.log(data))
.catch(error => console.error('Error:', error));
This updates the price of existing products and adds the in_stock
field.
Adding Additional Properties
To add common properties to all imported documents:
cURL
curl -X POST "[INSTANCE-URL]/csv?db=mydb&coll=sales&props=region&props=imported_date&values=Europe&values=2023-06-15" \
-H "Authorization: Basic [BASIC-AUTH]" \
-H "Content-Type: text/csv" \
-d 'id,product,amount,customer
1,Widget,100,Acme Inc
2,Gadget,50,Example Corp'
HTTPie
http POST "[INSTANCE-URL]/csv?db=mydb&coll=sales&props=region&props=imported_date&values=Europe&values=2023-06-15" \
Authorization:"Basic [BASIC-AUTH]" \
Content-Type:text/csv \
id,product,amount,customer
1,Widget,100,Acme Inc
2,Gadget,50,Example Corp
JavaScript
const csvData = `id,product,amount,customer
1,Widget,100,Acme Inc
2,Gadget,50,Example Corp`;
const url = new URL('[INSTANCE-URL]/csv');
url.searchParams.append('db', 'mydb');
url.searchParams.append('coll', 'sales');
url.searchParams.append('props', 'region');
url.searchParams.append('props', 'imported_date');
url.searchParams.append('values', 'Europe');
url.searchParams.append('values', '2023-06-15');
fetch(url, {
method: 'POST',
headers: {
'Authorization': 'Basic [BASIC-AUTH]',
'Content-Type': 'text/csv'
},
body: csvData
})
.then(response => response.json())
.then(data => console.log(data))
.catch(error => console.error('Error:', error));
This adds region: "Europe"
and imported_date: "2023-06-15"
to each document.
CSV Data Transformation
For more complex transformations, you can create a custom interceptor plugin that modifies the data before it’s stored in MongoDB.
Example: Converting Coordinates to GeoJSON
Here’s a simplified example using a custom interceptor:
-
Create the interceptor:
@RegisterPlugin(name = "coordsToGeoJson")
public class CoordsToGeoJson implements Interceptor<BsonFromCsvRequest, BsonResponse> {
@Override
public void handle(BsonFromCsvRequest request, BsonResponse response) throws Exception {
// Get the documents from the request
var docs = request.getContent();
if (docs == null) {
return;
}
// Process each document
docs.stream()
.map(doc -> doc.asDocument())
.filter(doc -> doc.containsKey("lon") && doc.containsKey("lat"))
.forEach(doc -> {
// Create coordinates array
var coordinates = new BsonArray();
coordinates.add(doc.get("lon"));
coordinates.add(doc.get("lat"));
// Create GeoJSON point
var point = new BsonDocument();
point.put("type", new BsonString("Point"));
point.put("coordinates", coordinates);
// Add to document
doc.append("location", point);
});
}
@Override
public boolean resolve(BsonFromCsvRequest request, BsonResponse response) {
return request.isHandledBy("csvLoader")
&& request.isPost()
&& "/csv".equals(request.getPath());
}
}
-
Deploy the interceptor as a plugin
-
Import CSV data with coordinates:
cURL
curl -X POST "[INSTANCE-URL]/csv?db=mydb&coll=poi&id=0" \
-H "Authorization: Basic [BASIC-AUTH]" \
-H "Content-Type: text/csv" \
-d 'id,name,city,lat,lon
1,Eiffel Tower,Paris,48.8583701,2.2944813
2,Coliseum,Rome,41.8902614,12.4930871'
HTTPie
http POST "[INSTANCE-URL]/csv?db=mydb&coll=poi&id=0" \
Authorization:"Basic [BASIC-AUTH]" \
Content-Type:text/csv \
id,name,city,lat,lon
1,Eiffel Tower,Paris,48.8583701,2.2944813
2,Coliseum,Rome,41.8902614,12.4930871
JavaScript
const csvData = `id,name,city,lat,lon
1,Eiffel Tower,Paris,48.8583701,2.2944813
2,Coliseum,Rome,41.8902614,12.4930871`;
fetch('[INSTANCE-URL]/csv?db=mydb&coll=poi&id=0', {
method: 'POST',
headers: {
'Authorization': 'Basic [BASIC-AUTH]',
'Content-Type': 'text/csv'
},
body: csvData
})
.then(response => response.json())
.then(data => console.log(data))
.catch(error => console.error('Error:', error));
-
The resulting documents will include a GeoJSON location field:
{
"_id": "1",
"name": "Eiffel Tower",
"city": "Paris",
"lat": 48.8583701,
"lon": 2.2944813,
"location": {
"type": "Point",
"coordinates": [2.2944813, 48.8583701]
}
}
Best Practices
-
Validate CSV data before importing to ensure it’s well-formed
-
Use unique IDs in the CSV to avoid duplicate documents
-
Start with small imports to verify the correct transformation
-
Consider indexes for collections where you’ll be importing large datasets
-
Use transactions for critical imports to ensure atomic operations
Error Handling
If your CSV data contains errors or violates any validation rules set on the collection, RESTHeart will return an appropriate error response:
-
400 Bad Request
- Malformed CSV or invalid parameters -
409 Conflict
- ID conflicts when not using update mode -
422 Unprocessable Entity
- Data validation errors
Always check response status codes to confirm successful imports.