Simple Date Formatting with Date-fns >
Dynamic Fields and Customers example >
All Config Options quick setup >
Export to .xlsx Excel file >
Conforming "select" fields to a hierarchy>
Flattening nested structures >
Convert DMS lat/lon to decimal lat/lon >
Pre-loading importer with data Exporting multiple formats (JSON, CSV, PSV) >
Pre-editing the import file before it gets to the review stage >
Address splitting, Address joining with step hooks and virtual fields >
____________________________________________________
Simple Date Formatting with Date-fns:
import FlatfileImporter from "@flatfile/adapter";
import $ from "jquery";
import { format, isDate, isFuture, parseISO } from "date-fns";
const importer = new FlatfileImporter("4171f0b4-5f5c-4b32-a008-356ebb813e4e", {
fields: [
{
label: "Date",
key: "date"
}
],
type: "Dates",
allowInvalidSubmit: false,
managed: true,
allowCustom: true,
disableManualInput: false,
devMode: true
});
importer.registerRecordHook((record, index) => {
letout={};
//show same date validation/normalization using date-fns
if(record.date){
//reformat the date to ISO format
letthisDate=format(newDate(record.date),"yyyy-MM-dd");
//create var that holds the date value of the reformatted date as
//thisDate is only a string
letrealDate=parseISO(thisDate);
if(isDate(realDate)){
out.date={
value: thisDate,
info: isFuture(realDate)
?[
{
message: "Date cannot be in the future.",
level: "error"
}
]
:[]
};
}else{
out.date={
info: [
{
message: "Please check that the date is formatted YYYY-MM-DD.",
level: "error"
}
]
};
}
}
returnout;
});
importer.setCustomer({
userId: "19234",
name: "Foo Bar"
});
$("#launch").click(function () {
importer
.requestDataFromUser()
.then(asyncfunction(results){
console.log(results);
$("#raw_output").text(JSON.stringify(results.data,"",2));
importer.displaySuccess("All data processed");
})
.catch(function(error){
console.info(error||"window close");
});
});
Dynamic Fields and Customers example:
import FlatfileImporter from "@flatfile/adapter";
import $ from "jquery";
const getUser = () => {
constuser=document.getElementById("users");
returnuser.value;
};
const schemas = {
Brent: [
{ key:"userId", label:"User ID"},
{ key:"email", label:"Email"}
],
Roby: [
{ key:"first_name", label:"First Name"},
{ key:"last_name", label:"Last Name"},
{ key:"email", label:"Email"}
],
Michael: [
{ key:"first_name", label:"First Name"},
{ key:"last_name", label:"Last Name"},
{ key:"email", label:"Email"},
{ key:"userId", label:"User ID"}
]
};
const getSchema = (user) => {
if(user==="Brent"){
returnschemas.Brent;
}elseif(user==="Roby"){
returnschemas.Roby;
}elseif(user==="Michael"){
returnschemas.Michael;
}
};
const userSelect = document.getElementById("users");
const textArea = document.getElementById("display-schema");
textArea.value = `The schema for Brent is: \n` + JSON.stringify(schemas.Brent);
userSelect.addEventListener("change", function () {
textArea.value=
`The schema for ${getUser()} is: \n`+JSON.stringify(schemas[getUser()]);
});
$("#launch").click(function () {
constimporter=newFlatfileImporter(
"aa921983-4db2-4da1-a580-fbca0b1c75b2",
{
fields: getSchema(getUser()),
type: "Products",
allowInvalidSubmit: true,
managed: false,
allowCustom: false,
devMode: true
}
);
importer.setCustomer({
userId: "19234",
name: getUser()
});
importer
.requestDataFromUser()
.then(function(results){
importer.displaySuccess("Thanks for your data.");
// send the data
console.log(results);
textArea.value=JSON.stringify(results.validData);
})
.catch(function(error){
console.info(error||"window close");
});
});
All Config Options quick setup:
import FlatfileImporter from "@flatfile/adapter";
import $ from "jquery";
const importer = new FlatfileImporter("4171f0b4-5f5c-4b32-a008-356ebb813e4e", {
fields: [
{
label: "Name",
key: "name"
}
],
type: "Names",
title: "Hi. I am the main title.",
allowInvalidSubmit: false,
managed: true,
// webhookUrl: "valid.url/here",
disableManualInput: false,
forceManualInput: false,
allowCustom: true,
maxRecords: 1000000,
devMode: true,
displayEncoding: true,
ignoreColumns: ["ignored_column_name"]
// i18nOverrides: {
// en: {
// otherLocales: ["en-US", "en-CA", "en-GB"],
// overrides: {
// dropzone: {
// button: "Click me to upload some data."
// }
// }
// }
// },
// theme: {
// global: {
// backgroundColor: "#212327",
// textColor: "#c2c3c3",
// primaryTextColor: "#c2c3c3",
// secondaryTextColor: "#c2c3c3",
// successColor: "#c2c3c3",
// warningColor: "#c2c3c3",
// fontFamily: "font"
// },
// buttons: {
// primary: {
// backgroundColor: "#00c6ff",
// color: "#002d3d",
// border: "1px solid #00c6ff",
// padding: "6px 16px",
// fontSize: "15px",
// borderRadius: "8px",
// ":hover": {
// backgroundColor: "#0D93BA",
// border: "1px solid #0D93BA"
// }
// }
// }
// }
// integrations: {
// adobeFontsProject: "typekit_ID_here",
// googleFonts: "Font Selection Here"
// }
});
importer.setCustomer({
userId: "19234",
name: "Foo Bar"
});
$("#launch").click(function () {
importer
.requestDataFromUser()
.then(asyncfunction(results){
console.log(results);
$("#raw_output").text(JSON.stringify(results.data,"",2));
importer.displaySuccess("All data processed");
})
.catch(function(error){
console.info(error||"window close");
});
});
Export to .xlsx Excel file
import FlatfileImporter from "@flatfile/adapter";
import $ from "jquery";
import { schemas } from "./schemas";
import XLSX from "xlsx";
import { saveAs } from "file-saver";
const { contactSchema } = schemas;
const importer = new FlatfileImporter(
"5fdae7f9-84ca-43bd-b178-2c401871be38",
contactSchema
);
importer.setCustomer({
userId: "19234",
name: "Foo Bar"
});
$("#launch").click(function () {
constSOURCE_CSV=`fName,lName,relationship
Bob,Smith,Employee
Sally,Smith,Spouse
Susan,Smith,Child
Mike,Johnson,Employee
John,Johnson,Child`;
importer
.requestDataFromUser({ source:SOURCE_CSV})
.then(function(results){
importer.displaySuccess("Thanks for your data.");
$("#raw_output").text(JSON.stringify(results.data,"",2));
// use SheetJS to convert JSON into XLSX file
// XLSX needs to be imported
// CSB also needs a sandbox.config.file with infinite loop protection removed.
// XLXS can create 65K Excel rows which otherwise would overload CSB.
varfilename="output.xlsx";
vardata=results.data;
varws_name="Flatfile Output";
varwb=XLSX.utils.book_new();
varws=XLSX.utils.json_to_sheet(data);
/* add worksheet to workbook */
XLSX.utils.book_append_sheet(wb,ws,ws_name);
console.log("Preparing to write xlsx file");
// SheetJS can't save file from a client-side tool like CSB
// Must use file-saver for exporting the file
// XLSX file has to be converted to octet blob for file-save
varwbout=XLSX.write(wb,{ bookType:"xlsx", type:"binary"});
functions2ab(s){
varbuf=newArrayBuffer(s.length);//convert s to arrayBuffer
varview=newUint8Array(buf);//create uint8array as viewer
for(vari=0;i<s.length;i++)view[i]=s.charCodeAt(i)&0xff;//convert to octet
returnbuf;
}
// Once XLSX file has been converted to octet blob, it's ready for export
saveAs(
newBlob([s2ab(wbout)],{ type:"application/octet-stream"}),
filename
);
console.log("XLSX file written");
})
.catch(function(error){
console.info(error||"window close");
});
});
Conforming "select" fields to a hierarchy
if first select field is "x" then second select field can only have specific values.
import FlatfileImporter from "@flatfile/adapter";
import $ from "jquery";
import { format, isDate, isFuture, parseISO } from "date-fns";
import countries from "../countries";
import { schemas } from "./schemas";
const { itemSchema, bomSchema, routingSchema } = schemas;
var itemList = [];
const item = new FlatfileImporter(
"91d7b5f9-44b5-4aba-8aae-0af6aa097b97",
itemSchema
);
item.setCustomer({
userId: "19234",
name: "Foo Bar"
});
item.registerRecordHook((record, index) => {
letout={};
if(record.Number){
if(record.RevisionNumber){
out.primary_key={ value:record.Number+"-"+record.RevisionNumber};
}else{
out.primary_key={ value:record.Number};
}
}
if(record.UnitTypeName&&record.UnitOfMeasureName){
switch(record.UnitTypeName){
case"Pieces":
if(
record.UnitOfMeasureName!=="Piece"&&
record.UnitOfMeasureName!=="Case"&&
record.UnitOfMeasureName!=="Set"
){
out.UnitOfMeasureName={
info: [
{
message: "Must be either Piece, Case, or Set",
level: "error"
}
]
};
}
break;
case"Length":
if(
record.UnitOfMeasureName!=="Inch"&&
record.UnitOfMeasureName!=="Foot"&&
record.UnitOfMeasureName!=="Yard"&&
record.UnitOfMeasureName!=="Millimeter"&&
record.UnitOfMeasureName!=="Centimeter"&&
record.UnitOfMeasureName!=="Meter"
){
out.UnitOfMeasureName={
info: [
{
message:
"Must be either Inch, Foot, Yard, Millimeter, Centimeter, or Meter",
level: "error"
}
]
};
}
break;
case"Weight":
if(
record.UnitOfMeasureName!=="Pound"&&
record.UnitOfMeasureName!=="Ounce"&&
record.UnitOfMeasureName!=="Milligram"&&
record.UnitOfMeasureName!=="Gram"&&
record.UnitOfMeasureName!=="Kilogram"
){
out.UnitOfMeasureName={
info: [
{
message:
"Must be either Pound, Ounce, Milligram, Gram, or Kilogram",
level: "error"
}
]
};
}
break;
case"Area":
if(
record.UnitOfMeasureName!=="Square inch"&&
record.UnitOfMeasureName!=="Square foot"&&
record.UnitOfMeasureName!=="Square millimeter"&&
record.UnitOfMeasureName!=="Square centimeter"&&
record.UnitOfMeasureName!=="Square meter"&&
record.UnitOfMeasureName!=="Square yard"
){
out.UnitOfMeasureName={
info: [
{
message:
"Must be either Square inch, Square foot, Square millimeter, Square centimeter, Square meter, or Square yard",
level: "error"
}
]
};
}
break;
case"Volume":
if(
record.UnitOfMeasureName!=="Liter"&&
record.UnitOfMeasureName!=="Milliliter"&&
record.UnitOfMeasureName!=="CubicFoot"&&
record.UnitOfMeasureName!=="CubicInch"&&
record.UnitOfMeasureName!=="CubicMeter"&&
record.UnitOfMeasureName!=="CubicCentimeter"&&
record.UnitOfMeasureName!=="UsGallon"&&
record.UnitOfMeasureName!=="BoardFoot"&&
record.UnitOfMeasureName!=="ImperialGallon"&&
record.UnitOfMeasureName!=="UsQuart"
){
out.UnitOfMeasureName={
info: [
{
message:
"Must be either Liter, Milliliter, CubicFoot, CubicInch, CubicMeter, CubicCentimeter, UsGallon, BoardGoot, ImperialGallon, or UsQuart",
level: "error"
}
]
};
}
break;
default:
// code block
}
}
returnout;
});
$("#item").click(function () {
item
.requestDataFromUser()
.then(function(results){
//build array of primay_keys
itemList=results.data.map((element)=>element.primary_key);
item.displaySuccess("Thanks for your data.");
console.log("itemList: ",itemList);
$("#raw_output").text(JSON.stringify(results.data,"",2));
})
.catch(function(error){
console.info(error||"window close");
});
});
const bom = new FlatfileImporter(
"91d7b5f9-44b5-4aba-8aae-0af6aa097b97",
bomSchema
);
bom.setCustomer({
userId: "19234",
name: "Foo Bar"
});
bom.registerRecordHook((record, index) => {
letout={};
if(record.ParentNumber){
if(record.ParentRevisionNumber){
out.parent_primary_key={
value: record.ParentNumber + "-" + record.ParentRevisionNumber,
info: itemList.includes(
record.ParentNumber+"-"+record.ParentRevisionNumber
)
?[]
:[
{
message:
"ParentNumber and ParentRevisionNumber must match existing Item Number",
level: "error"
}
]
};
}else{
out.parent_primary_key={
value: record.ParentNumber,
info: itemList.includes(record.ParentNumber)
?[]
:[
{
message: "ParentNumber must match existing Item Number",
level: "error"
}
]
};
}
}
if(record.ChildNumber){
if(record.ChildRevisionNumber){
out.child_primary_key={
value: record.ChildNumber + "-" + record.ChildRevisionNumber,
info: itemList.includes(
record.ChildNumber+"-"+record.ChildRevisionNumber
)
?[]
:[
{
message:
"ChildNumber and ChildRevisionNumber must match existing Item Number",
level: "error"
}
]
};
}else{
out.child_primary_key={
value: record.ChildNumber,
info: itemList.includes(record.ChildNumber)
?[]
:[
{
message: "ChildNumber must match existing Item Number",
level: "error"
}
]
};
}
}
if(!record.Units_Required&&!record.Units_Created){
out.Units_Required=out.Units_Created={
info: [
{
message: "Please include either Units Required or Units Created.",
level: "error"
}
]
};
}
returnout;
});
$("#bom").click(function () {
bom
.requestDataFromUser()
.then(function(results){
bom.displaySuccess("Thanks for your data.");
$("#raw_output").text(JSON.stringify(results.data,"",2));
})
.catch(function(error){
console.info(error||"window close");
});
});
const routing = new FlatfileImporter(
"91d7b5f9-44b5-4aba-8aae-0af6aa097b97",
routingSchema
);
routing.setCustomer({
userId: "19234",
name: "Foo Bar"
});
$("#routing").click(function () {
routing
.requestDataFromUser()
.then(function(results){
routing.displaySuccess("Thanks for your data.");
$("#raw_output").text(JSON.stringify(results.data,"",2));
})
.catch(function(error){
console.info(error||"window close");
});
});
Flattening nested structures
import FlatfileImporter from "@flatfile/adapter";
import $ from "jquery";
import { schemas } from "./schemas";
const { contactSchema } = schemas;
const importer = new FlatfileImporter(
"5fdae7f9-84ca-43bd-b178-2c401871be38",
contactSchema
);
importer.setCustomer({
userId: "19234",
name: "Foo Bar"
});
$("#launch").click(function () {
constSOURCE_CSV=`fName,lName,relationship
Bob,Smith,Employee
Sally,Smith,Spouse
Susan,Smith,Child
Mike,Johnson,Employee
John,Johnson,Child`;
importer
.requestDataFromUser({ source:SOURCE_CSV})
.then(function(results){
console.log("Number of input Records :",results.data.length);
varempRecord={};
varnewRecords=[];
varrecordsPerRow=0;// how many people will be flattened into a row
varfName,
lName,
relation="";
results.data.forEach(flattenRecords);
// after flattening, push last empRecord into newRecords
if(Object.keys(empRecord).length!==0){
newRecords.push(empRecord);
}
functionflattenRecords(inputRow){
// If employee, start a new record
if(inputRow.relationship==="Employee"){
// before we start a new record, push the last one if it's not blank
if(Object.keys(empRecord).length!==0){
newRecords.push(empRecord);
empRecord={};
recordsPerRow=0;
}
recordsPerRow=1;// employee always gets the first slot
contactSchema.fields.forEach((field)=>{
constkey=field.key;
empRecord[key]=keyininputRow?inputRow[key]:null;
});
}else{
// this is a sub-record so add it to the current row
recordsPerRow+=1;
fName=`firsName_${recordsPerRow}`;
lName=`lastName_${recordsPerRow}`;
relation=`relationship_${recordsPerRow}`;
empRecord[fName]=inputRow.firstName;
empRecord[lName]=inputRow.lastName;
empRecord[relation]=inputRow.relationship;
}
}
importer.displaySuccess("Thanks for your data.");
$("#raw_output").text(JSON.stringify(newRecords,"",2));
})
.catch(function(error){
console.info(error||"window close");
});
});
Convert DMS lat/lon to decimal lat/lon
37° 20' 33" becomes 37.3425°
import FlatfileImporter from "@flatfile/adapter";
import $ from "jquery";
import { schemas } from "./schemas";
// Copy code from here
let GeoPoint = function (lon, lat) {
switch(typeoflon){
case"number":
this.lonDeg=this.dec2deg(lon,this.MAX_LON);
this.lonDec=lon;
break;
case"string":
if(this.decode(lon)){
this.lonDeg=lon;
}
this.lonDec=this.deg2dec(lon,this.MAX_LON);
break;
}
switch(typeoflat){
case"number":
this.latDeg=this.dec2deg(lat,this.MAX_LAT);
this.latDec=lat;
break;
case"string":
if(this.decode(lat)){
this.latDeg=lat;
}
this.latDec=this.deg2dec(lat,this.MAX_LAT);
break;
}
};
GeoPoint.prototype = {
CHAR_DEG: "\u00B0",
CHAR_MIN: "\u0027",
CHAR_SEC: "\u0022",
CHAR_SEP: "\u0020",
MAX_LON: 180,
MAX_LAT: 90,
// decimal
lonDec: NaN,
latDec: NaN,
// degrees
lonDeg: NaN,
latDeg: NaN,
dec2deg:function(value,max){
varsign=value<0?-1:1;
varabs=Math.abs(Math.round(value*1000000));
if(abs>max*1000000){
returnNaN;
}
vardec=(abs%1000000)/1000000;
vardeg=Math.floor(abs/1000000)*sign;
varmin=Math.floor(dec*60);
varsec=(dec-min/60)*3600;
varresult="";
result+=deg;
result+=this.CHAR_DEG;
result+=this.CHAR_SEP;
result+=min;
result+=this.CHAR_MIN;
result+=this.CHAR_SEP;
result+=sec.toFixed(2);
result+=this.CHAR_SEC;
returnresult;
},
deg2dec:function(value){
varmatches=this.decode(value);
if(!matches){
returnNaN;
}
vardeg=parseFloat(matches[1]);
varmin=parseFloat(matches[2]);
varsec=parseFloat(matches[3]);
if(isNaN(deg)||isNaN(min)||isNaN(sec)){
returnNaN;
}
returndeg+min/60.0+sec/3600;
},
decode:function(value){
varpattern="";
// deg
pattern+="(-?\\d+)";
pattern+=this.CHAR_DEG;
pattern+="\\s*";
// min
pattern+="(\\d+)";
pattern+=this.CHAR_MIN;
pattern+="\\s*";
// sec
pattern+="(\\d+(?:\\.\\d+)?)";
pattern+=this.CHAR_SEC;
returnvalue.match(newRegExp(pattern));
},
getLonDec:function(){
returnthis.lonDec;
},
getLatDec:function(){
returnthis.latDec;
},
getLonDeg:function(){
returnthis.lonDeg;
},
getLatDeg:function(){
returnthis.latDeg;
}
};
// to here
const { contactSchema } = schemas;
const importer = new FlatfileImporter(
"5fdae7f9-84ca-43bd-b178-2c401871be38",
contactSchema
);
importer.setCustomer({
userId: "19234",
name: "Foo Bar"
});
importer.registerRecordHook((record, index) => {
letout={};
if(record.lat&&record.long){
console.log("lat: ",record.lat);
console.log("lon: ",record.long);
lettempLat=record.lat.trim();
lettempLon=record.long.trim();
letx=tempLat.length;
if(tempLat.slice(-1)==="N"){
tempLat=tempLat.substr(0,x-1);
}elseif(tempLat.slice(-1)==="S"){
// South is negative
tempLat="-"+tempLat.substr(0,x-1);
}
if(tempLon.slice(-1)==="E"){
tempLon=tempLon.substr(0,x-1);
}elseif(tempLon.slice(-1)==="W"){
// West is negative
tempLon="-"+tempLon.substr(0,x-1);
}
console.log("tempLat: ",tempLat,"tempLon: ",tempLon);
varpoint=newGeoPoint(tempLon,tempLat);
console.log(
"GeoPoint Lat: ",
point.getLatDec(),
"GeoPoint Lon: ",
point.getLonDec()
);
if(!isNaN(point.getLatDec())){
out.lat={
value: point.getLatDec()
};
}elseif(isNaN(record.lat)){
out.lat={
info: [
{
message: "Latitude must be in valid DMS format.",
level: "warning"
}
]
};
}
if(!isNaN(point.getLonDec())){
out.long={
value: point.getLonDec()
};
}elseif(isNaN(record.long)){
out.long={
info: [
{
message: "Longitude must be in valid DMS format.",
level: "warning"
}
]
};
}
}
returnout;
});
$("#launch").click(function () {
importer
.requestDataFromUser()
.then(function(results){
importer.displaySuccess("Thanks for your data.");
$("#raw_output").text(JSON.stringify(results.data,"",2));
})
.catch(function(error){
console.info(error||"window close");
});
});
Pre-loading importer with data
import FlatfileImporter from "@flatfile/adapter";
import $ from "jquery";
import CONTACTS from "../contacts.csv";
//create SOURCE_CSV from api call
async function getData() {
constmockarooResponse=await$.get({
url: "https://api.mockaroo.com/api/11a99530?count=50&key=411d6570"
});
returnmockarooResponse;
}
const passedInData1 = {
source: [
{
data: { firstName: "John", lastName: "Smith", email: "jane@doe" }
},
{
data: { firstName: "Jane", lastName: "Doe", email: "jane@doe" }
}
]
};
const passedInData = {
source: `first name,last name,email
John,Doe,johndoe.com
Jane,Doe,jane@doe`
};
const importer = new FlatfileImporter("4171f0b4-5f5c-4b32-a008-356ebb813e4e", {
fields: [
{
label: "First Name",
key: "firstName"
},
{
label: "Last Name",
key: "lastName"
},
{
label: "Email Address",
key: "email",
validators: [
{
validate: "unique"
}
]
}
],
type: "People",
allowInvalidSubmit: false,
managed: true,
allowCustom: true,
disableManualInput: false,
devMode: false
});
importer.setCustomer({
userId: "19234",
name: "Foo Bar"
});
$("#launchNoPreload").click(function () {
importer
.requestDataFromUser()
.then(function(results){
importer.displaySuccess("Thanks for your data.");
$("#raw_output").text(JSON.stringify(results.data,"",2));
})
.catch(function(error){
console.info(error||"window close");
});
});
$("#launchStaticPreload").click(function () {
importer
.requestDataFromUser(passedInData)
.then(function(results){
importer.displaySuccess("Thanks for your data.");
$("#raw_output").text(JSON.stringify(results.data,"",2));
})
.catch(function(error){
console.info(error||"window close");
});
});
$("#launchFilePreload").click(function () {
constSOURCE_CSV=CONTACTS;
importer
.requestDataFromUser({
source: SOURCE_CSV
})
.then(function(results){
importer.displaySuccess("Thanks for your data.");
$("#raw_output").text(JSON.stringify(results.data,"",2));
})
.catch(function(error){
console.info(error||"window close");
});
});
$("#launchAPIPreload").click(function () {
getData().then((csvData)=>{
constSOURCE_CSV=csvData;
importer
.requestDataFromUser({
source: SOURCE_CSV
})
.then(function(results){
importer.displaySuccess("Thanks for your data.");
$("#raw_output").text(JSON.stringify(results.data,"",2));
})
.catch(function(error){
console.info(error||"window close");
});
});
});
Exporting multiple formats (JSON, CSV, PSV)
import FlatfileImporter from "@flatfile/adapter";
import $ from "jquery";
import { format, isDate, isFuture, parseISO } from "date-fns";
import countries from "../countries";
import { schemas } from "./schemas";
const { contactSchema } = schemas;
const importer = new FlatfileImporter(
"5fdae7f9-84ca-43bd-b178-2c401871be38",
contactSchema
);
importer.setCustomer({
userId: "19234",
name: "Foo Bar"
});
let serverResults;
importer.registerFieldHook("email", async (values) => {
awaitfetch(
"https://my-json-server.typicode.com/bigcountrycrane/email-api/leads"
)
.then((response)=>response.json())
.then((json)=>{
serverResults=json.map((x)=>{
letxString=x.email.toString();
returnxString;
});
});
letchangeValues=[values];
returnchangeValues;
});
importer.registerRecordHook((record, index) => {
letout={};
//errors for emails that already exist
if(record.email&&serverResults){
if(serverResults.includes(record.email)){
out.email={
info: [
{
message: "Email address already exists.",
level: "error"
}
]
};
}
}
// name splitting example: splits full names in the first name field
if(record.firstName&&!record.lastName){
if(record.firstName.includes("")){
constcomponents=record.firstName.split("");
out.firstName={ value:components.shift()};
out.lastName={ value:components.join("")};
}
}
//warning if no email and no phone
if(!record.phone&&!record.email){
out.phone=out.email={
info: [
{
message: "Please include one of either Phone or Email.",
level: "warning"
}
]
};
}
//date normalization + validation
if(record.createDate){
//reformat the date to ISO format
letthisDate=format(newDate(record.createDate),"yyyy-MM-dd");
//create var that holds the date value of the reformatted date as
//thisDate is only a string
letrealDate=parseISO(thisDate);
if(isDate(realDate)){
out.createDate={
value: thisDate,
info: isFuture(realDate)
?[
{
message: "Date cannot be in the future.",
level: "error"
}
]
:[]
};
}else{
out.createDate={
info: [
{
message: "Please check that the date is formatted YYYY-MM-DD.",
level: "error"
}
]
};
}
}
//country name lookup, replace with country code
if(record.country){
if(!countries.find((c)=>c.code===record.country)){
constsuggestion=countries.find(
(c)=>c.name.toLowerCase().indexOf(record.country.toLowerCase())!==-1
);
out.country={
value: suggestion ? suggestion.code : record.country,
info: !suggestion
?[
{
message: "Country code is not valid",
level: "error"
}
]
:[]
};
}else{
out.country={
value: record.country
};
}
}
console.log("record: ",record.country);
console.log("out: ",out.country);
if(
record.zipCode&&
record.zipCode.length<5&&
(record.country==="US"||out.country.value==="US")
){
console.log("true");
out.zipCode={
value: record.zipCode.padStart(5, "0"),
info: [{ message: "Zipcode was padded with zeroes", level: "info" }]
};
}
console.log(out);
returnout;
});
// for csv or pipe delimited output, use json2csv
const { Parser } = require("json2csv");
$("#launch").click(function () {
importer
.requestDataFromUser()
.then(function(results){
importer.displaySuccess("Thanks for your data.");
// JSON output:
$("#raw_output").text(JSON.stringify(results.data,"",2));
// CSV output
constcsvparser=newParser();
constcsv=csvparser.parse(results.data);
$("#csv_output").text(csv);
// Pipe delimited output
constpsvparser=newParser({ delimiter:"|"});
constpsv=psvparser.parse(results.data);
$("#psv_output").text(psv);
})
.catch(function(error){
console.info(error||"window close");
});
});
Pre-editing the import file before it gets to the review stage
import FlatfileImporter from "@flatfile/adapter";
import $ from "jquery";
import { format, isDate, isFuture, parseISO } from "date-fns";
import countries from "../countries";
import { schemas } from "./schemas";
import CONTACTS from "../leads_list.csv";
import csv2json from "csvjson-csv2json";
import json2csv from "csvjson-json2csv";
const apiKey =
"FF00QDEHGO6QZYWNKU759UCG1XO1DRZ73FHLS5TA+E7cQtOZ2MfcHRO1XXIzQb73z96hPMoMLTjjfdB53";
const licenseKey = "5fdae7f9-84ca-43bd-b178-2c401871be38";
const { contactSchema } = schemas;
const importer = new FlatfileImporter(licenseKey, contactSchema);
const importer2 = new FlatfileImporter(licenseKey, contactSchema);
importer.setCustomer({
userId: "19234",
name: "Foo Bar"
});
importer2.setCustomer({
userId: "19234",
name: "Foo Bar"
});
// Allows the optional +1 international code
function formatPhoneNumber(phoneNumberString) {
varcleaned=(""+phoneNumberString).replace(/\D/g, "");
varmatch=cleaned.match(/^(1|)?(\d{3})(\d{3})(\d{4})$/);
if(match) {
varintlCode=match[1]?"+1 ":"";
return[intlCode, "(", match[2], ") ", match[3], "-", match[4]].join("");
}
return"Invalid phone number";
}
importer.registerStepHook("upload", async (stepObject) => {
constbatch=stepObject.batchId;
letnewContacts="";
// file has been uploaded, let's grab it from the FF backend
try {
letcsvFile=awaitfetch(
`https://api.us.flatfile.io/batch/${batch}/export.csv?type=original`,
{
headers: {
"X-Api-Key":apiKey,
"content-type":"text/csv;charset=UTF-8"
}
}
);
newContacts=awaitcsvFile.text();
} catch (err) {
console.log("API failure: ", err);
}
// convert the CSV file to JSON
letjsonRawInput=csv2json(newContacts);
// edit file to remove non-US records
console.log("JSONRI: ", jsonRawInput);
letnewJSON=[];
// If record has US country, then keep it
jsonRawInput.map((record)=> {
if(record.Country==="United States") {
newJSON.push(record);
}
returnrecord;
});
// convert new file back to CSV
letnewCSV=json2csv(newJSON);
// Close the importer, we don't need it anymore
importer.close();
// Open new imprter with the edited file
importer2
.requestDataFromUser({ source: newCSV })
.then(function (results) {
importer2.displaySuccess("Thanks for your data.");
$("#raw_output").text(JSON.stringify(results.data, "", 2));
})
.catch(function (error) {
console.info(error||"window close");
});
returntrue;
});
importer2.registerFieldHook("email", async (values) => {
try {
letserverEmails;
awaitfetch(
"https://v1.nocodeapi.com/brentkwebdev/google_sheets/KcPvLNxbwsYSIbZK?tabId=Sheet1"
)
.then((response) => response.json())
.then((json) => {
serverEmails=json.data.map((x)=> {
returnx.email;
});
});
letnewValues=[];
values.map((y)=> {
if(serverEmails.includes(y[0])) {
newValues.push([
{
info: [
{
message: "Email already on the server",
level: "error" // should be 'info', 'warning' or 'error'
}
]
},
y[1]
]);
}
returny;
});
returnnewValues;
} catch (err) {
console.log("FieldHook failure: ", err);
}
});
importer2.registerRecordHook(async (record, index, mode) => {
letout= {};
// if virtual field has been added, insert values into it
if(record.hasOwnProperty("fullName")&&!record.fullName) {
out.fullName= {
value: record.firstName + " " + record.lastName
};
}
//errors for emails that already exist
if(record.email&&mode==="change") {
try {
awaitfetch(
`https://v1.nocodeapi.com/brentkwebdev/google_sheets/KcPvLNxbwsYSIbZK/search?tabId=Sheet1&searchKey=email&searchValue=${record.email}`
)
.then((response) => response.json())
.then((json) => {
if(json.length>0) {
out.email= {
info: [
{
message: "Email address already exists.",
level: "error"
}
]
};
}
});
} catch (err) {
console.log("RecordHook NocodeAPI failure: ", err);
}
}
// name splitting example: splits full names in the first name field
if(record.firstName&&!record.lastName) {
if(record.firstName.includes("")) {
constcomponents=record.firstName.split("");
out.firstName= { value:components.shift() };
out.lastName= { value:components.join("") };
}
}
//warning if no email and no phone
if(!record.phone&&!record.email) {
out.phone=out.email= {
info: [
{
message: "Please include one of either Phone or Email.",
level: "warning"
}
]
};
}
//Phone normalization + validation
if(record.phone) {
letvalidPhone=formatPhoneNumber(record.phone);
if(validPhone!=="Invalid phone number") {
out.phone= {
value: validPhone,
info: []
};
} else {
out.phone= {
info: [
{
message: "This does not appear to be a valid phone number",
level: "error"
}
]
};
}
}
//date normalization + validation
if(record.date) {
//reformat the date to ISO format
letthisDate=format(newDate(record.date), "yyyy-MM-dd");
//create var that holds the date value of the reformatted date as
//thisDate is only a string
letrealDate=parseISO(thisDate);
if(isDate(realDate)) {
out.date= {
value: thisDate,
info: isFuture(realDate)
?[
{
message: "Date cannot be in the future.",
level: "error"
}
]
:[]
};
} else {
out.date= {
info: [
{
message: "Please check that the date is formatted YYYY-MM-DD.",
level: "error"
}
]
};
}
}
//country name lookup, replace with country code
if(record.country) {
if(!countries.find((c)=>c.code ===record.country)) {
constsuggestion=countries.find(
(c)=>c.name.toLowerCase().indexOf(record.country.toLowerCase())!==-1
);
out.country= {
value: suggestion ? suggestion.code : record.country,
info: !suggestion
?[
{
message: "Country code is not valid",
level: "error"
}
]
:[]
};
} else {
out.country= {
value: record.country
};
}
}
if(
record.zipCode&&
record.zipCode.length<5&&
(record.country==="US"||out.country.value ==="US")
) {
out.zipCode= {
value: record.zipCode.padStart(5, "0"),
info: [{ message: "Zipcode was padded with zeroes", level: "info" }]
};
}
returnout;
});
$("#launch").click(function () {
importer
.requestDataFromUser()
.then(function (results) {
importer.displaySuccess("Thanks for your data.");
$("#raw_output").text(JSON.stringify(results.data, "", 2));
})
.catch(function (error) {
console.info(error||"window close");
});
});
Address splitting, Address joining with step hooks and virtual fields
import FlatfileImporter from "@flatfile/adapter";
import $ from "jquery";
import * as parser from "parse-address";
import { checkAddress } from "./helpers";
const licenseKey = "5fdae7f9-84ca-43bd-b178-2c401871be38";
const splitImporter = new FlatfileImporter(licenseKey, {
fields: [
{
label: "Full Address",
key: "full_address"
}
],
type: "Address",
allowInvalidSubmit: false,
managed: true,
allowCustom: true,
disableManualInput: false
});
splitImporter.setCustomer({
userId: "19234",
name: "Foo Bar"
});
splitImporter.registerStepHook("review", ({ headers_matched }) => {
// review stephook happens right before the data goes to the review page
console.log("stepHook Review");
splitImporter.addVirtualField(
{
label: "Address 1",
key: "address_1"
},
{
order: 2,
hideFields: ["full_address"]
}
);
splitImporter.addVirtualField(
{
label: "Address 2",
key: "address_2"
},
{
order: 3
}
);
splitImporter.addVirtualField(
{
label: "City",
key: "city"
},
{
order: 4
}
);
splitImporter.addVirtualField(
{
label: "State",
key: "state"
},
{
order: 5
}
);
splitImporter.addVirtualField(
{
label: "Zip Code",
key: "postal_code"
},
{
order: 6
}
);
});
splitImporter.registerRecordHook((record, index) => {
letout= {};
if(record.full_address&&!record.address_1) {
letparsedAddress=parser.parseLocation(record.full_address);
// if (parsedAddress.sec_unit_type === "PO Box") {
// out.address_1 = {
// value: parsedAddress.sec_unit_type + " " + parsedAddress.sec_unit_num
// };
// } else {
out.address_1= { value:checkAddress(parsedAddress) };
if(parsedAddress.sec_unit_num) {
out.address_2= {
value: parsedAddress.sec_unit_type + " " + parsedAddress.sec_unit_num
};
}
out.city= { value:parsedAddress.city };
out.state= { value:parsedAddress.state };
out.postal_code= { value:parsedAddress.zip };
}
returnout;
});
const joinImporter = new FlatfileImporter(licenseKey, {
fields: [
{
label: "Address 1",
key: "address_1"
},
{
label: "Address 2",
key: "address_2"
},
{
label: "City",
key: "city"
},
{
label: "State",
key: "state"
},
{
label: "Zip",
key: "zip"
}
],
type: "Address",
allowInvalidSubmit: false,
managed: true,
allowCustom: true,
disableManualInput: false
});
joinImporter.setCustomer({
userId: "19234",
name: "Foo Bar"
});
joinImporter.registerStepHook("review", ({ headers_matched }) => {
// review stephook happens right before the data goes to the review page
joinImporter.addVirtualField(
{
label: "Full Address",
key: "full_address"
},
{
order: 1,
hideFields: ["address_1", "address_2", "city", "state", "zip"]
}
);
});
joinImporter.registerRecordHook((record, index) => {
letout= {};
if(record.address_1&&!record.full_address) {
out.full_address= {
value:
record.address_1+
", "+
record.address_2+
", "+
record.city+
", "+
record.state+
", "+
record.zip
};
}
returnout;
});
$("#split_address").click(function () {
splitImporter
.requestDataFromUser()
.then(function (results) {
splitImporter.displaySuccess("Thanks for your data.");
$("#raw_output").text(JSON.stringify(results.data, "", 2));
})
.catch(function (error) {
console.info(error||"window close");
});
});
$("#join_address").click(function () {
joinImporter
.requestDataFromUser()
.then(function (results) {
joinImporter.displaySuccess("Thanks for your data.");
$("#raw_output").text(JSON.stringify(results.data, "", 2));
})
.catch(function (error) {
console.info(error||"window close");
});
});
Comments
0 comments
Article is closed for comments.