Bulk import data to collection from CSV or JSON

altered.coder

last month

13

• 

  janpeini

  last month

  We once build a CSV import for collections which looks like this:

  
  
  

  1. Custom Component with uploader and parser

  
  

  2. Implementation in the collection view

  
  

  
  

  Custom Component:

  
  

  import React from "react";
  import Styles from "./styles.module.scss";
  import csv from "csvtojson";
  
  type State = { type: "IDLE" } | { type: "ERROR"; error: string } | { type: "UPLOADING" };
  
  export const AgenciesUploader = () => {
      const [state, setState] = React.useState<State>({ type: "IDLE" });
  
      const onSubmit = async (e: React.FormEvent<HTMLFormElement>) => {
          e.preventDefault();
  
          const file = e.currentTarget.file.files[0];
  
          if (!file) {
              setState({ type: "ERROR", error: "Es wurde keine Datei ausgewählt." });
              return;
          }
  
          setState({ type: "UPLOADING" });
  
          const reader = new FileReader();
          console.log(reader);
  
          reader.onload = async function(e) {
              if(e.target) {
                  const data = csvJSON(e.target?.result as string);
                  console.log(data);
  
                  const formData = new FormData();
                  formData.set("data", JSON.stringify(data));
  
                  const response = await fetch("/api/[your-collection]/import", {
                      method: "POST",
                      body: formData,
                  });
  
                  if (!response.ok) {
                      console.log("error while uploading");
                  } else {
                      location.reload();
                  }
              }
          };
  
          reader.readAsText(file);
  
      };
  
      const csvJSON = (csv: string) => {
          const lines = csv.split('\n')
          const result = []
          const headers = lines[0].split(',')
      
          for (let i = 1; i < lines.length; i++) {        
              if (!lines[i])
                  continue
              const obj: any = {}
              const currentline = lines[i].split(',')
      
              for (let j = 0; j < headers.length; j++) {
                  obj[headers[j]] = currentline[j]
              }
              result.push(obj)
          }
          return result
      }
  
      return (
          <div className="agencyPagesUploader">
              <h4>Import Data:</h4>
              <p>Select your CSVs to be imported</p>
              <form onSubmit={onSubmit}>
                  {state.type === "ERROR" && <p className={Styles.error}>{state.error}</p>}
  
                  <input name="file" type="file" accept=".csv,.txt" />
                  <button disabled={state.type === "UPLOADING"}>Upload</button>
              </form>
          </div>
      );
  };

  
  

  
  

  Collection config:

  
  

  ...
    admin: {
        ...
        components: {
            BeforeListTable: [AgenciesUploader],
        },
    },
  ...
  endpoints: [
        {
            path: "/import",
            method: "post",
            handler: importAgencies,
        },
    ],
  ...

  
  

  
  

  Import Handler:

  
  

  import csv from "csvtojson";
  import payload from "payload";
  import { PayloadHandler } from "payload/config";
  import { agencyPages } from "../collections/agencyPages";
  import { AgencyPage } from "../types/payload-types";
  import { forEachAsync } from "../utils/forEachAsync";
  
  export const importAgencies: PayloadHandler = async (req, res) => {
      if (!req.body?.data) {
          return res.status(400).send({ error: "csv is missing" });
      }
  
      const data = JSON.parse(req.body.data);
      let pageCounter = 0;
  
      for (const row of data){
          const oldPage = await getPageById(row.slug);
  
          const newPage: AgencyPage = {
              ...row,
              status: "draft",
          };
  
          if (oldPage) {
              await payload.update({
                  collection: agencyPages.slug,
                  id: oldPage.id,
                  data: newPage,
              });
          } else {
              await payload.create({
                  collection: agencyPages.slug,
                  data: newPage,
              });
          }
          pageCounter++;
      }
  
      // todo: flag pages, which aren't available anymore - maybe set them to draft? ask admins to delete them? ...
      res.status(200).send({ success: "true", count: pageCounter });
  };
  
  const getPageById = async (slug: string) => {
      const pages = await payload.find({
          collection: agencyPages.slug,
          where: { slug: { equals: slug } },
          limit: 1,
      });
      if(pages.docs.length) return pages.docs[0] as AgencyPage | undefined;
      return undefined;
  };

  
  

  
  

  It will have to be refined, we used this only for a very controlled csv upload. But it does all the basics.

• 

  altered.coder

  last month

  @janpeini Thanks! I will check it out! This one looking more promising than other solutions available.

• 

  janpeini

  last month

  In general it's simple. But in our case it relies on a fixed format of the csv. Of course you could build a custom interface to let the user select which csv column to match to which field in the collection.

• 

  seanzubrickas

  Payload Team

  last month

  Thanks for jumping in here to help @janpeini — much appreciated!

• 

  janpeini

  last month

  And yeah... I guess this case screams "plugin" 😅

• 

  altered.coder

  last month

  @janpeini Exactly! This should be a plugin.

  
  

  
  

  @janpeini I modified the code according to my needs and thanks to you, now its working like a charm.

  
  

  But there is one more help I need, please explain me this function

  
  
  

  const getPageById = async (slug: string) => {

  
  

  const pages = await payload.find({

  
  

  collection: agencyPages.slug,

  
  

  where: { slug: { equals: slug } },

  
  

  limit: 1,

  
  

  });

  
  

  if(pages.docs.length) return pages.docs[0] as AgencyPage | undefined;

  
  

  return undefined;

  
  

  };

  
  
  
  

  Because I can't update predefined data without it, currently I am able to insert only.

  
  

  When I am using my collection slug in

  collection

  property, it throws this error

  
  

  - error QueryError: The following path cannot be queried: slug

  
  

  
  

  Note:-
  And for anyone looking for this solution, use csvtojsonpackage for csv parsing, the function given in this solution won't work with sub objects or sub arrays

• 

  janpeini

  last month

  This part checks, if there already is an item with the slug from the csv to prevent duplicates. So it then updates rather than creates a new item.

  
  

  
  

  we did this check based on the slug... but you can do as you please. Could be a custom identifier or whatever

• 

  altered.coder

  last month

  Okayy! Thanks!

  
  

  
  

  @janpeini Thanks for the help, I finally able to make myself a perfectly working code, attaching images of my vesion of bulk file uploader in Payload

  

  
• 

  janpeini

  last month

  Very nice!

• 

  seanzubrickas

  Payload Team

  last month

  nice work @altered.coder !!

  
  

  
  

  thanks again for working through this with them @janpeini - you are a champ!

• 

  janpeini

  last month

  no prob... was just posting some of our code 😅

• 

  seanzubrickas

  Payload Team

  last month

  we appreciate that very much! We are working on official import/export support - so that will come..eventually!

• 

  altered.coder

  last month

  @janpeini @seanzubrickas Did you guys know any way to reload the collections properly, so I don't need to reload the whole page after import is done....?