
Ask HN: How do you handle Excel import/exports in your apps - NOtherDev
Hi folks!<p>I&#x27;m implementing Excel-related functionality for at least 4th project in my career. As usual, an ability to save the data set into an XLSX file in a custom format and the way to ingest the data back, using the same data format, or ideally letting the customer map their own formats into our format.<p>What worries me is that I need to take care about all the hassle around - handling large uploads, managing timeouts, reporting the progress, caring about basics of data validation, file encodings, ensuring the import process doesn&#x27;t take up all the server resources etc.<p>The list of impediments for such a &quot;side&quot; feature is unproportionally large, IMO. There are few libraries for almost every stack, but they still require me to handle at least half of these impediments by my own (resources, server config etc.).<p>I&#x27;m wondering, how do you usually handle these stuff? Do we all go through these problems over and over again or are there solutions that I&#x27;m not aware of that makes things easier, like a standalone tool or service I can &quot;just use&quot;?<p>If not, my maker soul asks: maybe there should be one? Maybe I should build one? Would you consider using this kind of service in your applications if one exists? Why not?
======
borplk
Been there done that.

Tips (apply common sense based on your specific company):

1\. As an engineer, learn to say no. Want to import some random excel sheet
into our system? Sorry no you can not.

2\. Try to provide an API instead and push the problem to the customer. If
they want it badly enough they can pay someone to write something that takes
their stuff and imports it via your API.

3\. If you have to do the file thing avoid XLSX like the plague. Stick to CSV.
It's their problem to convert their XLSX or anything else to CSV.

4\. CHOOSE, VALIDATE AND ENFORCE the format (or a few possible formats). Do
not negotiate or collaborate with customers about "custom format" and so on.
That is a never ending battle you will never win. You give 1 inch they take
10. Eventually they will give an animated gif compressed in a zip file,
wrapped in a tarball and ask you to convert that to CSV and import it in.

5\. If you are running a typical SaaS don't turn your product into free in-
house IT consulting for the random customers. Make the capability possible
within the system by exposing an API and leave the rest for them to figure
out. If they care about enough they will have to pay someone to create the
integrations. 95% don't care and will give up and adapt.

If you have enterprise customers paying 6-7 figure sums it's a different
story...

~~~
marktangotango
Expose the api and offer consulting to work with them, min 40 hours st $200
hour for example.

------
marktangotango
Sounds like you’re talking about two separate things, the upload part and the
import part. The upload part is solved for most languages and frameworks. The
import step; excel parsing solved, getting data into the correct format is
highly app specific. Maybe you could clarify what you have in mind?

------
lucasverra
SheetJs [0]

[0]: [http://sheetjs.com/](http://sheetjs.com/)

free as beer, voila !

------
acesubido
> If not, my maker soul asks: maybe there should be one? Maybe I should build
> one? Would you consider using this kind of service in your applications if
> one exists? Why not?

To answer your "maybe there should be one?" question. What you're describing
sounds like this: [https://flatfile.io/](https://flatfile.io/). It was shared
here 2 months ago.

Though to answer your original question:

> How do you handle Excel import/exports in your apps

One way we go about that is: whenever someone uploads a file, create some sort
of UploadRequest record which contains the filepath in S3/Azure/GCS/etc. Then
the actual parsing and validation is spun off in a worker process. That way
the web process isn't locked and we can pub progress
((processed_rows/row_count).percentage) on authorized subs. If that worker
process ends, update the UploadRequest record status and dump the array of
good_rows/error_rows in some column for that record. Users see that the status
for that UploadRequest is updated, then they can review the UploadRequest
errors. If there are errors, they download the uploaded file, fix that and re-
upload again. At the start you can also provide a template to "gate" your
upload, if it doesn't match your template you blow up UploadRequest with the
proper error. If all is good, they click "Process" or something, which
actually inserts the rows or does whatever.

Again, that’s just one way.

If bizdev asks to make a feature that allows fixing those rows in your app
without leaving the browser, we find that it slowly feature creeps into Google
Sheets territory, which is a BIG undertaking and a separate feature on it’s
own. It's a nice experience to do in-app editing but we try to see if most of
our Excel uploaders would prefer to go back to Excel for editing. Sometimes we
find that they’ll prefer going back to Excel because they’ll use the same
excel sheet for other purposes in their process (send that Excel as an email
to another department, use that and aggregate it as a separate report, etc.).

But really, the hardest part in Excel uploads isn't server resources,
progress, file encoding, or timeouts, it's the validation. There are different
levels of validation: from presence checks, formatting, type-checking to
running a row or a cell against business logic, 3rd-party API calls or records
in your database. This is where most User errors arise, because you'd have to
educate them on what values to put if you're referencing something in your
business logic (special abbreviated codes, special combination of codes, date
formats, etc.).

~~~
FearNotDaniel
Small question, since the developer docs are not available on mobile devices:

When the flatfile.io homepage keeps referring to "XLS" docs, is it actually
trying to say they will import modern Excel files ("XLSX") or does it
literally mean you would have to ask users to re-save their spreadsheets into
the ancient format?

