12-09-2020 10:03 AM
Title: Import CSV File
Description: This flow allows you to import csv files into a destination table.
Actually I have uploaded 2 flows. One is a compact flow that just removes the JSON accessible dynamic columns.
You can easily access the columns in the compact version by using the column indexes . . . ,,, etc...
This step is where the main difference is at. We don't recreate the JSON, we simply begin to write our data columns by accessing them via column indexes (apply to each actions). This will cut down in the run time by even another 30% to 40%.
** If you need to add or remove columns you can, follow the formatting and pattern in the "append json string items" and anywhere else columns are accessed by column indexes with 0 being first column -- for example "variables('variable')" or "split(. .. . )", etc.. .
Import the package attached below into your own environment.
You only need to change the action where I get the file content from, such as sharepoint get file content, onedrive get file content.
My file in the example is located on premise.
When you change the get file content action it will remove any references to it, this is where it belongs though.
Please read the comments within the flow steps, they should explain the using this flow.
Be sure to watch where your money columns or other columns with commas fall as they have commas replaced by asterisk in this flow, when you write the data you need to find the money columns to remove the commas since it will most likely go into a currency column.
Also check the JSON step carefully since it will let you access your columns dynamically in the following steps.
You will need to modify the JSON schema to match your column names and types, you should be able to see where the column names and types are within the properties brackets.
In this step of you JSON notice my values have quotes because mine are all string type, even the cost.
If you have number types remove the quotes (around the variables) at this step where the items are appended and you probably need to replace the comma out of the money value (replace with nothing).
This step will control how many records you are going to process.
The flow is set up to process 200 rows per loop and should not be changed or due to the nesting of loops it may go over the limit.
It will detect how many loops it needs to perform. So if you have 5,000 rows it will loop 25 times.
You should change the count though. Make sure the count is over the number of loops, the count just prevents it from looping indefinitely.
Note: This flow can take a long time to run, the more rows with commas in the columns the longer.
9-25-20 - A new version of the flow is available, it is optimized and should run 40% to 50% faster.
Questions: If you have any issues running it, most likely I can figure it out for you.
Anything else we should know: You can easily change the trigger type to be scheduled, manual, or when a certain event occurs.
The csv file must be text based as in saved as plain text with a csv extension or txt in some cases.
Note: any file with csv extension will probably show with an excel icon.
The best way to find out if it will work is to right click it on your computer and choose to open it in word pad or note pad. If you see the data it will work. If you see different characters it is an excel based file and won't work.
An excel based file is of type object and can't be read as a string in this flow.
You can easily convert it by saving excel file as CSV UTF-8 comma delimited in save as options.
It should also work on an excel file without table format as long as you convert to csv the same way and same extension requirement.
** If the file is on sharepoint go ahead and save as csv utf-8 and then change the extension to .txt or sharepoint will force it to open as an actual excel spreadsheet file.
you may also need to use .txt extension from other "get file contents" besides sharepoint. I know for a fact on premise can stay as .csv extension.
My sample run of 12000 rows, this sample has 10 columns, you can have any number of columns.
Thanks very much for sharing! I've been able to parse a csv file with about 40,000 records but what I then want to do is compare that file with an existing excel table in sharepoint with about the same amount of records. The comparison process seems to take forever so I tried to use concurrent processes but I couldn't do this because of the way you reset the json string in the retrieve each file loop. Am I missing something here as to how I take the output and do parallel processing of the result?
You can go ahead and write the 40k records to a temporary sharepoint since they are being processed.
Then you can use concurrency to compare the temporary sharepoint records to the excel table in sharepoint.
The comparison can be added at the end of the flow or maybe a new flow.
This looks really good but I am looking for something slightly different. I would like to Import csv or excel data each time a file is modified so the new data can be imported into a SharePoint list. Currently I have an export of the data arriving on email, so can capture the attachment and save it to OneDrive for Business.
I am using your flow, works amazingly except one thing. I find it cannot get past a line break in between the extra quotes, the flow just hangs and will not finish processing. How would you go about dealing with this? I don't know how .csv files code in line breaks.
Late response here. My notifications are no longer sent to my email.
Post a pic of your fail action details and or error message to see what it is doing in this particular line.
Normally the error message will display which character causes the issue or explain why it can't process it.
I believe the create CSV action does have some type of limits. I'm don't know the limitations but it should be listed in the documentation.
Thank you for sharing this!
I am going to download it to try it out, but I do have a couple of questions...
I would like to create a flow to build a table of delivery addresses. I have 3 sources of data for this, corporate orders, online orders and internal orders.
Each source produces a date/timestamped .CSV file. But for simplicity let's call them corporate.CSV, online.CSV and internal.CSV.
To complicate things further, each .CSV file has a different schema. While all the relevant data is present in each file the relevant columns have different headings and layouts.
Downloading the .CSV files themselves probably cannot be automated, but it will save me tons of time if I can use a flow to pick and combine the data I need.
What I need my fliw to do...
Button flow (manual trigger)
Create a master table to store and export the extracted data as a spreadsheet.
Pick files (use a file picker to select relevant. CSV files).
I'm thinking here it will need to separate into 3 branches as the 3 .CSV files have different schema.
Parse .CSV file and convert to a table.
Extract relevant data and write to master table.
Save master table as spreadsheet that can be exported into route optimization software.
You should be able to do all the things you listed without many additions to the flow except for the 3 schemas.
You can use the trigger flow manually and have it prompt to select files. The prompt can have multiple files.
For the parsing step, there is no need to convert to a table. The json output is already available in the flow if you download the one with the json included.
This lets you access the columns from the drop down (dynamic).
You will need a new JSON schema which is your "master table" where you collect the records from the three files.
This can simply be a string variable where you append the data of each record using a JSON schema. Each branch will append records to this string variable.
Then you can parse it with JSON so you can access the "master table" columns to then write to a database destination or even excel table.
I already have an example of this also in the end of the flow in retrieve each file loop.
The only limitation I see is creating it as a spreadsheet. There is no way to create a spreadsheet from flow.
You can get around this by having an empty spreadsheet file present that you make a copy of each time and then create a table in that copy and then write each record from the "master table" parsed JSON string.
mmm your main approach was to import a csv or something else ? because I need only a simple Filter array to split my csv in rows and create columns with a select. Inside my select I can format data based on my requirements. After the select I can write my data to excel, sharepoint or somewhere else.