Understanding Parsing/Mapping Data From A .CSV File

[Eric_D_Miller]

Hello,

I am attempting to pull information from a .csv file and would like to map that data to specific fields on a project. The csv is originating from a spreadsheet. There are a few cells in the spreadsheet that I would like to map data to some project fields.

The CSV file that is being uploaded parses with no data in the bundles, only the header:

Any insight on what I need to do in order to access the rest of the data in the csv? Could it be how the excel sheet is formatted?

Why are my bundles showing up blank?

Here is my Parse CSV Module for context:

[Rafal_Bainie]

2 thoughts:

1. why not select comma from delimiter dropdown? maybe that is impacting results...

2. We use aggregator into custom data structure post parse CSV module to organize everything :

I hope this helps

Rafal

[Sven-iX]

Hi @Eric_D_Miller 
Can you check what the {3.Data} value is? (I assume it's from a document download?)

Also - when you download the CSV and open in Excel - does it look as expected? 

[Eric_D_Miller]

Hello @Sven-iX ,

The  {3.Data} value is indeed from a document download. The downloaded file is a .csv format.

The .csv file is getting exported from an Excel spreadsheet that includes plenty of formatting, but when I open it via .csv all of the data is still in there.

[Sven-iX]

Right - is it possible to see a screenshot of what that file looks like when you open it in a text editor? 

Parse CSV works fine usually. SOMEthing is off with the downloaded doc. 
Did you save it as CSV UTF-8? 
No modifications after save-as CSV ? 

[Eric_D_Miller]

I exported it from Excel using the Download as CSV, not Download as CSV UTF-8

Here is how it looks in Excel and in Notepad++

Excel:

Notepad++

REVISION DATE,x/xx/xx,,DESCRIPTION,,PRICE PER $,TOTAL SIGNS/Hours/stores/Quantity,Sub Total,Tax (%7),Total,,SIGNAGE PRE-TAX,," $XXXX.XX ",,,,,
KIT / OOK,KIT,,KIT PACKING COST,, $- ,315, $- , $- , $- ,,HARDWARE PRE-TAX,, $- ,,,,,
IN-STORE DATE,x/xx/xx,,Shipping Estimate Per Store,, $- ,315, $- , $- , $- ,,KITTING/VCBs/MISC PRE-TAX,," $XXXX.XX ",,,,,
FUNDED BY,xxx,,Special Shipping COST (Sign Count),, $- ,1, $- , $- , $- ,,SUBTOTAL,," $XXXX.XX ",,,,,
,,,STANDARD VENDOR VCBs (Sign Count) ,, $X.XX ,"X,XX"," $XXXX.XX ", $XXX.XX ," $XXXX.XX ",,TAX (7%),," $XXX.XX ",,,,,
WORKFRONT PROJECT #,xxx,,STANDARD VCBs (Sign Count) ,, $- ,"X,XXX", $- , $- , $- ,,GRAND TOTAL,," $XXXX.XX ",,,,,
Project Name,xxx,,PROFESSIONAL SERVICES,,,1, $- ,, $- ,,,,,,,,,
CATEGORY,APPAREL,,CREATIVE HOURS,,,1, $- ,, $- ,,,,,,,,,
SUB-CATEGORY,ATHLETIC,,33%,, $- ,1, $- , $- , $- ,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
Master Job Code,XXXXXXXXX,,,,Estimate for:,ALL,,,,,,,,,,,,
WORKFRONT PROJECT NAME,XXXXXXXX,,,,,,,,,,,,,,,,,
DSG PROJECT MANAGER,XXXXXX,,,,,,,,,,,,,,,,,
OUTFORM APM,XXXXX,,,,,,,,,,,,,,,,,
Line Items Required,10,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
SPLITS,JOB CODE,SIGN TYPE,SIZE,PRINT,MATERIAL,FINISHING,HARDWARE,SIGN PRICE,TOTAL SIGNS,TOTAL SIGN COST,HARDWARE PRICE PER SIGN,HARDWARE TOTAL,SUB TOTAL,TAX (7%),TOTAL,,,
,XXXX- XXXXA1,EASEL BACK GALLERY 34 X 28,34 X 28,SS (4/0),"3/16"" FOAMCORE W/B/B","TRIM TO SIZE, APPLY (1) 24"" BLACK EASEL BACK PER SIGN",, $XX.XX , 229 ," $XXXX.XX ", $- , $- ," $XXXX.XX ", $XXX.XX ," $XXXX.XX ",,,
,XXXX- XXXXA2,EASEL BACK GALLERY 34 X 28,34 X 28,SS (4/0),"3/16"" FOAMCORE W/B/B","TRIM TO SIZE, APPLY (1) 24"" BLACK EASEL BACK PER SIGN",, $XX.XX , 82 ," $XXXX.XX ", $- , $- ," $XXXX.XX ", $XXX.XX ," $XXXX.XX ",,,
,XXXX- XXXXB1,FACEOUT SIGN,4 X 4,SS (4/0),80# GLOSS COVER,TRIM TO SIZE,, $X.XX , 335 , $XXX.XX , $- , $- , $XXX.XX , $XX.XX , $XXX.XX ,,,
,XXXX- XXXXB2,FACEOUT SIGN,4 X 4,SS (4/0),80# GLOSS COVER,TRIM TO SIZE,, $X.XX , 335 , $XXX.XX , $- , $- , $XXX.XX , $XX.XX , $XXX.XX ,,,
,XXXX- XXXXC1,H-FRAME SIDE PANEL (PERF),22.25 X 53.5,SS (4/0),18PT C1S,"TRIM TO SIZE, WITH PERF PER DIE LINE",, $XX.XX , 335 ," $XXXX.XX ", $- , $- ," $XXXX.XX ", $XXX.XX ," $XXXX.XX ",,,
,XXXX- XXXXC2,H-FRAME SIDE PANEL (PERF),22.25 X 53.5,SS (4/0),18PT C1S,"TRIM TO SIZE, WITH PERF PER DIE LINE",, $XX.XX , 335 ," $XXXX.XX ", $- , $- ," $XXXX.XX ", $XXX.XX ," $XXXX.XX ",,,
,XXXX- XXXXD1,HOS LARGE FRAME MAGNET,47.25 X 23.625,SS (4/0),".020 MIL MAGNETIC VINYL WHITE ",TRIM TO SIZE,, $XX.XX , 43 , $XXX.XX , $- , $- , $XXX.XX , $XX.XX , $XXX.XX ,,,
,XXXX- XXXXD2,HOS LARGE FRAME MAGNET,47.25 X 23.625,SS (4/0),".020 MIL MAGNETIC VINYL WHITE ",TRIM TO SIZE,, $XX.XX , 43 , $XXX.XX , $- , $- , $XXX.XX , $XX.XX , $XXX.XX ,,,
,XXXX- XXXXD3,HOS LARGE FRAME MAGNET,47.25 X 23.625,SS (4/0),".020 MIL MAGNETIC VINYL WHITE ",TRIM TO SIZE,, $XX.XX , 43 , $XXX.XX , $- , $- , $XXX.XX , $XX.XX , $XXX.XX ,,,
,XXXX - 9098126 E1,HEAT BEND SIGN 11 X 14 X 7,11 X 21 (FLAT) / 11 X 14 X 7 (FINISHED),SS (WHITE/0),3MIL SINTRA BLACK,"TRIM TO SIZE, APPLY HEAT BEND AT 92 °",, $XX.XX , 43 , $XXX.XX , $- , $- , $XXX.XX , $XX.XX , $XXX.XX ,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,
,,,,,,,,

[Sven-iX]

Hi @Eric_D_Miller 

Couple comments: 

• Your sheet has a bunch of name-value fields , and a real CSV table starts in line 18. When you parse this - what data do you expect to read out of it? 
• Excel is weird - for example, your columns are "filled" up until col P (16) - but an example row in the CSV shows 19 cols (counting the commas)
  

  ,XXXX - 9098126 E1,HEAT BEND SIGN 11 X 14 X 7,11 X 21 (FLAT) / 11 X 14 X 7 (FINISHED),SS (WHITE/0),3MIL SINTRA BLACK,"TRIM TO SIZE, APPLY HEAT BEND AT 92 °",, $XX.XX , 43 , $XXX.XX , $- , $- , $XXX.XX , $XX.XX , $XXX.XX ,,,​

  In my experience I have to literally "delete rows/cols" outside the area I am exporting because otherwise Excel tries to be too smart (I assume those cells are "touched" and even though they're blank, Excel exports them... )

Help me understand what your next step is after parsing this CSV. Then we'll get it working. 

[Eric_D_Miller]

I am trying to get the $ amount from the:

1. STANDARD VENDOR VCBs (Sign Count)
2. SUBTOTAL
3. TAX(7%)
4. 33%

Does this help?

[Sven-iX]

Oof - I don't know of a way other than brute force: Here's my attempt at reading out the particular cell based on row/col numbers. 

This "CSV" is not a single table where you could parse and fetch the value from Column X and Row Y. We're I'm doing it manually.

After setting the CSV, I create that mapping, including row#, col# and a name for the variable. Seemed easiest. Then some CSV gymnastics because I can't simply split a row on a comma, since values can include commas (ex X,XXX). The purist in me shudders a bit, but it works. - Of course that assumes noone adds columns or rows  

Results:

JSON map

resulting array of desired values

[Eric_D_Miller]

Sven,

Thanks for the details and the Blueprint!

I am lost on how to set the initial 'set variable' module to pull the date from the .csv being uploaded rather than the text I sent as the example.

Do I still download the document in Fusion? If so how do I push the data from the downloaded .csv to the variable?

Thank you in advance.

[Sven-iX · Accepted solution]

Hi @Eric_D_Miller 
you don't need that variable technically (I needed to because I didn't download the CSV)
Just use the 3.data field from the download

[Eric_D_Miller]

Sven,

Amazing! This got it! Very helpful to see how you brute forced certain rows/columns.

As always, I appreciate your expertise.

-Eric

[Sven-iX]

Glad I could help Eric. 

If you have Office 365 you could also look into the Excel modules 

One thought: 

If you create a separate sheet where you reference the cells you need with a formula, and export THAT sheet you could save yourself the brute-force trouble: 

E.g. 

Then your CSV module would import these 4 "columns" as you expect.