Unable to use CSV parser and upload to Sheets due to blank fields and file size

How To
, ,
1

Hi everyone! I’m working on an automation that uses an HTTP request to pull a CSV file with the goal being to export that file or parts of it into sheets (redacted file example attached). Problem is, the file is huge and has some blank fields, so I can’t get the CSV parser file to work. I’ve spent a couple hours on this problem already, scoured the make.com forms, and tried the solutions to similar issues but with no success. Would love some help if someone here has solved something similar in the past!

What I’ve Tried So Far:

  1. Used regex to clean the raw CSV, replacing blanks with placeholders like "".
  2. Used 0codekit with code created from Chatgpt and Claude prompts but no luck.

It look like the blank columns are throwing off the CSV parser. Unfortunately, I’m unable to change the format/colums the file is delivered in, so ideally would just like to input the blank fields as blanks.

Was also thinking I might be able to API call another tool to clean up the data first but haven’t found any. Any help would be super appreciated!

Inventory Stock Check - Sheet12.csv (58.6 KB)

2

image
image992×566 32.2 KB

For more info, here’s the python code I’m using:
import pandas as pd
import io

Replace this with your actual CSV string

csv_data = “”“<{{2.data}}>”“”

Step 1: Clean and normalize line breaks

csv_data = csv_data.replace(‘\r\n’, ‘\n’).replace(‘\r’, ‘\n’)

Step 2: Parse the CSV string into a DataFrame

try:
# Ensure the first row is treated as the header
df = pd.read_csv(io.StringIO(csv_data), sep=“,”, skip_blank_lines=True, engine=“python”, on_bad_lines=‘skip’)
except Exception as e:
print(f"Error parsing CSV: {e}")
df = None

Step 3: Save the cleaned DataFrame back to a CSV string

if df is not None:
cleaned_csv = df.to_csv(index=False) # Removed the line_terminator argument
print(“Cleaned CSV:”)
print(cleaned_csv)
else:
print(“No valid CSV data found.”)

and the parse CSV module

image
image612×595 42.8 KB

Thank you for looking this over!

3

Hi @samliew! I saw you were able to help others with similar issues so thought I’d tag. If you have the availability, getting your take on this problem would be amazing! No pressure to respond though, know you’re busy and this is a pretty complex problem but any insights would mean a lot to me

5

Welcome to the Make Community!

Thanks for the direct ping, but as I’m travelling and on mobile at the moment, I can’t take a look at your CSV.

Python is probably the wrong way to tackle this problem though.

If you require further assistance, my profile has a couple of links to some resources that may help.

— @samliew

1 Like
6

Thanks Sam, I’ll take a deeper look. I was searching through quite a few forums and haven’t found anyone who’s had a similar problem that’s been solved but will keep at it. Maybe running it through an llm like Gemini with a large context window and asking it to keep specific columns and format it correctly could work? Will give that a try and report back.

Attached a screenshot. Issue is blank cells in D24, D25, entire Q row blank, breaking the csv format. Appreciate you taking the time to respond and hope you have a great weekend!

image
image1840×863 118 KB

7

A bit more info - the JSON data comes out in this format, so it may be an improper export from sellerboard?

“data”: ""ASIN","SKU","Title","ROI, %","FBA/FBM Stock","Stock value","Estimated Sales Velocity","Days of stock left","Recommended quantity for reordering","Running out of stock","Reserved","Sent to FBA","Ordered","Time to reorder","Margin","Profit forecast (30 days)","Comment","Marketplace","Target stock range after new order days","FBA buffer days","Manuf. time days","Use a Prep Center","Shipping to Prep Center days","Shipping to FBA days","Box param length","Box param width","Box param height","Box param Units In Box","Color","Size","Multipack size","Item number","FNSKU","Supplier SKU","FBA prep. stock Prep center 1 stock","FBA prep. stock Prep center 2 stock","FBA prep. stock Prep center 3 stock","FBA prep. stock Prep center 4 stock"\r\n"B08TQRC3F6","Single-M-Krikland-Glove","Kirkland Signature Nitrile Gloves, Box of 200, Medium for Health Care, Food Service, Home other uses…","137","196","962.80","25.28","10","2553","SOON","56","348","0","YES","28,79","5 234,04","","Amazon.com","100","30","7","YES","5","5","","","","","","","","","X0044WUHYP","","","","",""\r\n"B07R8J9TK5","6ft HDMI 2.1 Canada, 6ft_HDMI2.1","8K HDMI ARC/eARC Cable for Soundbar 6.6FT/2M HDMI 2.1 Cable 48Gbps Ultra High Speed Cable in-Wall Safe, 4K 120Hz, Supports Dynamic HDR, HDCP, Dolby Atmos for Vizio Samsung Bose Sound Bars","50","5","28.30","5.78","3","0","YES","11","858","0","NO","7,01","151,2","","Amazon.com","100","90","40","YES","5","5","","","","","","","","","B07R8J9TK5","","1 320","","",""\r\n"B085D7MN3X","8K white 10ft","8K HDMI 2.1 Cable (10ft) White, eARC, 8K 60Hz, 4K 120Hz, QFT, QMS, VRR, 48Gbps Bandwidth, Dynamic HDR, HDCP 2.2 & 2.3, DTS:X, Dolby Atmos, Dynamic HDR, CL3 in-Wall Safe","87","232","1244.00","5.49","57","0","NO","80","0","612","NO","24,58","1 217,7","","Amazon.com","100","90","40","YES","5","5","","","","","","","","","