Email CSV attachment Data to Data Store error

[Zii]

Hi, I am trying to import a CSV file from an email to store it’s data to my Data Store.

I have setup the scenario as follow:

I have a slight issue with the CSV file as the system that generates it has the first row and first column empty. I’m not sure if it’s an issue but I can’t select the option that my CSV has headers in the Parse CSV module.

I am able retrieve the email with the CSV attachment and the data structure looks like this:

Although the data says Long String, I wasn’t sure I have to use the “toString()” function, but I did anyways.
This is how I setup the Parse CSV module:

Here is how I set up the Data Store Module:

When I run the scenario I then get this error: anything after line 10003 is empty, basically it’s the end of the csv file.

I selected Ignore error of type: Error as the automatic error handler, which catches the error but then nothing is executed to add the data to my selected Data Store.

Here is the path from CSV to Data Store

I am assuming the Ignore function is catching the error and ending the automation.

I’m not sure where I am going wrong with this, any superheros out there that can help me with this? :)))

[Runcorn]

Can you check Line No 10003 and check what is the content in it? Seems like either the name field has a comma in it or the data is broken for that particular line.

[Zii]

Hi @Runcorn,

It’s the end of the data, but it looks like all of the cells in that row is merged.

Unfortunately I have no way of editing how this file is generated, I need to find a way to ignore and bypass this.

[Runcorn]

So, it is basically an empty line, right?

If not can you open the csv and then paste the content here?

[Zii]

That’s correct, just an empty line.

The entire content? Or just that row?

The below is Rows 10002 & 10003

	30010		MISC WEIGHT	0	0	0	0

[Runcorn]

Not sure, if this works you will need to try this on the CSV file that you are getting, we need to remove the empty line anywhere in the file.

So, To do that, You can use replace function or Replace Module under Text Parser Module, with regular expression,

^(?:[\t ]*(?:\r?\n|\r))+

GIve it a try and see if it resolves the issue, you will then now use the output of this module in CSV module.

[Zii]

Hi @Runcorn, I think this is the way to go. I am now getting the error below.

When I copy that pattern and paste into notepad it seems there is a line break or return:

I tried to add another text replace module:

There error seems to be similar even without the ,,,,,,, at the beginning:

I tried to put this before the other suggested text replacement module, hoping that it would catch the return also, which I think it has but still giving an error.

[Zii]

I managed to solve the return and additional comma by using using the pattern ,,,,,,,\s,

This is what I get now.

PLU,RANDOM CODE,NAME,(£) Price1L1,(£) Price2L1,(£) Price3L1,(£) Price4L1 ,1,4251032001404,Meray DLX Mixed Nuts,3.99,0.00,0.00,0.00 ,2,5053827169981,Kelloggs Rice Krispies,2.99,0.00,0.00,0.00 ,3,5053827171472,Kellogs corn flakes,2.99,0.00,0.00,0.00 etc

I have set the CSV parse to 7 columns and now has header selected. However still getting the “Number of columns on line 1 does not match header” error.