Importing data from flat files
Tips and tricks around importing data from flat text files from disk or ftp, using Flow as the extraction engine
In a situation where the only source to the data you need exists in flat text files (or XML-files) and you need to import it to e.g. a database, it can be a challenge to figure out what tool to use and how to use it. There are many options out there, some whose sole purpose is more or less just that (Microsoft SSIS / Data factory etc.), that are more powerful for just this purpose, albeit there could be situations where Flow is not such a bad option. For example, if you are using Flow as the extraction tool from many data sources you can still use the same tool for your text/xml files. The simplicity with Flow to modify the data and/or logic during the import combined with that you and your colleagues do not need to learn and monitor another tool could be some of the reasons to use Flow. If you end up going with Flow, this page give useful tips.
There are some prerequisites for the file itself for using Flow as your extraction engine:
Flow can only handle simple flat files, meaning not .xls/.xlsx or other complex files, they need to be plain text files. Different delimiter characters (the character that splits the columns, e.g., Tab, Comma, Semicolon. Etc.) and different code pages are though supported in Flow.
For text files the number of columns need to always be the same. As we will look at below you can use your own headers, but the number of columns needs to always be the same.
If any of the above steps are not fulfilled, consider using another tool to import your data.
You also need to consider how the file will be uploaded to Flow. If it is from a manual user step in a user Flow no extra license will be needed. However if the file is on an ftp/sftp-server you need a separate license for that connector, or if its on file you will need the File Connector license.
Create a user step with the File Gallery control, asking for a file to upload. It is always good to have a simple user flow for troubleshooting, so creating this Flow next to your automated more complex Flow is a good idea. To follow along in the script step below, if you please, create a plain text file with three columns separated by semi colon.

Example of a simple user Flow to upload a file, delimiter it with Flow script, and presenting it in a user step with the grid view
The next step will be our CSVFillTable Flow Script step that we will take a closer look at.
If you are not familiar with these functions, please search for them in our help section where the latest functionality will be explained.
We will be using CSVFill (which is a Flow script command) to delimit the data.
A nifty feature of flow script to use in conjunction with CSVFill is how to handle files without column headers, or if they consist of illegal characters (including spaces). For that, create your own string that you concatenate with your data using the &-sign like this (for simplicity we are pretending our file consists of three columns, KeyCol, ExprCol1 and ExprCol2):
Let StringWHeaders = "KeyCol;ExprCol1; ExprCol2" & chr(13) & csv;
We are creating our own three headers here, adding a Carriage Return (chr(13)) and then adding our file data that is in our csv-variable (or what we have called it in our connector that fetches the file). Even if the file has simple column header names it could be a good idea to just use your own anyway (if you will copy the flow and we are not as lucky with the next file, or something changes in the import file)
As described in the help section, CSVFill also needs a prototype table::
Let Proto = table(KeyCol,ExprCol1,ExprCol2);
and then assigning the Result variable to the result of the CSV-Fill function:
Let Result = CSVFill(Proto, StringWHeaders, ";");
If the file has headers in it you do not want to import them (if it doesn’t this step can be ignored, but again could be good to just leave 0 at the end so it is there if you are making a copy of this Flow as a template for another file import, which has headers):
return skip(Result,1);
So our entire flow script step will look something like this, returning our data in a table variable:
Let StringWHeaders = "KeyCol;ExprCol1; ExprCol2" & chr(13) & csv;
Let Proto = table(KeyCol,ExprCol1,ExprCol2);
Let Result = CSVFill(Proto, StringWHeaders, ";");
return skip(Result,1);
In a real world scenario there could be quite a few columns to import so for readability you may row break the StringWithHeaders and Prototype. This way it is much easier to see if a column is missing between the two or if an extra delimiter character snuck it's way in. Like this example:
Let StringWHeaders = "DATUM;KOD_AM;BOLAG;AVD;KOSTNADSST;BETKUNDNR;BETKUNDKOD;BETKUNDKAT;KUNDNR;KUNDKOD;KUNDKAT;KUNDTYP_ORDER;KUNDKAT_ORDER;DATLEV;DATFAKT"
&";DATANKOM;DATKLAR;DATBEST;FAKTNR;FORSALJARE;FORS_NAMN;VAGNNR;REGNR;LANDKOD;CHASSINR;TYP;MARKE;FSLAG;ARSMODELL;MODELLGRP"
&";MODELL;BEGGARTYP;KEDJENR;KEDJENIVA;KREDITKORT;STAMKNR;AFFOKOD;VM;LAGERDAGAR;KUNDBESTNR;FINANSKUNDNR;FINANS_BOL;INBAVD;INBKST"
&";INBVAGNNR;INBREGNR;INBLANDKOD;INBCHASSI;INBTYP;INBMARKE;INBFSLAG;INBAR;INBMODGRP;INBMOD;INBAFFOKOD;INBVM;ANTAL;GATANPRIS"
&";FORS_PRIS;RABATT;TAXIRABATT;FORS_UTR_INT;FORS_UTR_EXT;FORSAKRING;SKATT;EXP_AVG;INKOPSPRIS;PAKOSTNAD;UTR_KOST;GARANTIKOST"
&";NEDSKRIVNING;FORH_INKPRIS;FORH_UTR_INT;FORH_UTR_EXT;INB_INKPRIS;INB_NEDSKR;INB_AVSK;FINANSBELOPP;LAGERDAGAR2;FORH_PAK_INT;FORH_PAK_EXT"
& chr(13) & csv;
Let Proto = table(DATUM,KOD_AM,BOLAG,AVD,KOSTNADSST,BETKUNDNR,BETKUNDKOD,BETKUNDKAT,KUNDNR,KUNDKOD,KUNDKAT,KUNDTYP_ORDER,KUNDKAT_ORDER,DATLEV,DATFAKT
,DATANKOM,DATKLAR,DATBEST,FAKTNR,FORSALJARE,FORS_NAMN,VAGNNR,REGNR,LANDKOD,CHASSINR,TYP,MARKE,FSLAG,ARSMODELL,MODELLGRP
,MODELL,BEGGARTYP,KEDJENR,KEDJENIVA,KREDITKORT,STAMKNR,AFFOKOD,VM,LAGERDAGAR,KUNDBESTNR,FINANSKUNDNR,FINANS_BOL,INBAVD,INBKST
,INBVAGNNR,INBREGNR,INBLANDKOD,INBCHASSI,INBTYP,INBMARKE,INBFSLAG,INBAR,INBMODGRP,INBMOD,INBAFFOKOD,INBVM,ANTAL,GATANPRIS
,FORS_PRIS,RABATT,TAXIRABATT,FORS_UTR_INT,FORS_UTR_EXT,FORSAKRING,SKATT,EXP_AVG,INKOPSPRIS,PAKOSTNAD,UTR_KOST,GARANTIKOST
,NEDSKRIVNING,FORH_INKPRIS,FORH_UTR_INT,FORH_UTR_EXT,INB_INKPRIS,INB_NEDSKR,INB_AVSK,FINANSBELOPP,LAGERDAGAR2,FORH_PAK_INT,FORH_PAK_EXT );
Let Result = CSVFill(Proto, StringWHeaders, ";");
return Result;
What if the columns in the text file are separated by a tab as delimiter? Trying to just enter a tab between the columns will not work. Instead try using another character and then replacing it with chr(9) (which is Tab):
Let StringWHeaders = " KeyCol;ExprCol1; ExprCol2 ".Replace(";", chr(9))