Issue while reading large CSV file

Hi,
I am having problem in reading large CSV file.
The file size is 260 MB. There are approximately 300,00 records.
Some time bot is able to read the file and some time it is throwing System Exception.
I tried Read CSV activity in try catch with Retry scope but bot is still failing.
Can anyone suggest me any other way of reading CSV file?

Capture 1

1 Like

Hi,
It might be caused by insufficient of system resource. However, can you try the following?

  • Update the latest version of Excel Activities Package.
  • Use Read Text File Activity and Generate DataTable Activity instead of Read CSV.

Regards,

Thank you @Yoichi
I have latest Excel activities package in my project.
I will try your suggestion “Read Text file” activity.
Can you please tell me how should I generate data table from “Read text file” activity?
If you have sample workflow, it will be of great help.

Hi,

Hope the following helps you.

Sample20200115-1.zip (10.0 KB)

Regards,

Thank you @Yoichi

2 Likes

Getting same problem.
Unable to read 260 mb file.
Is there any other way?

Hi,

This file might be too large to read. I’ll suggest the following 2 ways as workaround.

  1. Use “System.IO.File.ReadAllText” method as the following.
    in Assign Activity.
    strData = System.IO.File.ReadAllText(yourCSVFile)
    Then use Generate DataTable Activity

  2. Use “System.IO.File.ReadLines” method. This method read data not at once.
    See the following sample in details.

Sample20200115-1v2.zip (10.9 KB)

I am getting error in generate data table when I am writing my file name.

image

When I am doing auto detect columns. Following error is coming:

image

Hi,

The former error seems format issue.
Can you share a few lines of your csv file? It’s OK if dummy data. I’d like to know whether there is text delimiter etc in your CSV file.

Regards,

I have added .xlsx file since .csv I am unable to upload.

Sample.xlsx (12.1 KB)

Hi,

Thank you for sharing. But can you share raw csv file? Because I’d like to know if there is text delimiter only in CSV format.

Regards,

MEDICARE_AETNA_011320_FULLDAY_FINAL.zip (1.4 KB)

I have attached Csv file

Hi,

I just updated a sample as the following.
This sample works your above csv and if error occur, output its row number to log file.
Can you try?
Then, please check output panel after run this workflow. If there are some error row number, we need to investigate data of the row.

Sample20200115-1v3.zip (12.6 KB)

Regards,

Getting this in the Output line: “row No.509649:Input array is longer than the number of columns in this table.”

Hi,

There is something wrong at row No. 509649. Can you share this row data?
or
Can you add Write Line Activity to output item variable in the Catch like the following, and re-run? Data of the row will output to log.

BTW, do you have any other error? All data except no.509649 was stored to datatable successfully?

Regards,

Yes sure will do.
Actually data is almost same in all the rows.
But let me share with you

I am reading CSV file not Excel file :expressionless:

@Deepak_M

Can you please check your csv data first?

If the data something like this

A,B,C
1,2,3
4,5,6
7,8,9,9,
2,3,4

In the above data the read csv activity will fail because of the extra data in the 4 th line.

Please check if something related to this

For almost every row it is giving an error.
Input array is longer than the number of columns in this table.

Hi,

Can you share a row data which error occured? I suppose there is something difference between the row and above sample data.

Regards,