Unable to read CSV file having > 2 GB Data

Hello guys,

I have an excel File which is having data more than (>=2 Gb )., i tried using ODBC Connection, i tried few other ways like “Stream Reader” (Could be incorrect Code) to read the data, but still im not able to read the file, kindly for any support or any suggestions for reading the .CSV file…
@Anil_G @Gangadhar_Athili @Yoichi

Hi @Nissy_Ruth_Prabhu ,
Read CSV or read workbook can not read it?

Had you tried:

File.ReadAllText(YourFullFilePath) - return: String
File.ReadAllLines(YourFullFilePath) - return: String Array

what was done in detail?

No its not , because the data is more than 2 GB so those activities are getting failed like im getting job stopped unexpected error.

I have used a stream reader code in invoke code activity but looks like the code is not accurate, so not able to get as per the expected.

therefore we asked for the details e.g. the implemented code lines

Hi @Nissy_Ruth_Prabhu ,
You can try

  • Assign Activity: File.ReadAllLines(CSVFilePath).ToList()
  • Generate Datatable Activity
    Can you share your file, I will try read it
    Regards,
    LNV

i have tried the above but im still getting the same job stopped unexpected error

so lets focus on correcting the text reading with stream reader as this is a preferred option when handling large files. Just share with us the failing implementation and we will have a look at this

we didn’t ask for this. We asked for the implementation details.

It is up to you if you want share or not share the details. But we can better help when we can refer to your implementation details

Have a look also here:

I tried the above approach , and im getting the count 0.

can you please correct me with this code, if it works.

Blockquote
using System;
using System.IO;

class Program
{
static void Main()
{
// Replace “your-csv-file.csv” with the actual file path
string csvFilePath = “your-csv-file.csv”;

   try
   {
       using (StreamReader reader = new StreamReader(csvFilePath))
       {
           while (!reader.EndOfStream)
           {
               string line = reader.ReadLine();
               if (!string.IsNullOrEmpty(line))
               {
                   string[] fields = line.Split(',');
                   // Process the fields as needed, for example:
                   foreach (string field in fields)
                   {
                       Console.Write(field + " ");
                   }
                   Console.WriteLine();
               }
           }
       }
   }
   catch (Exception ex)
   {
       Console.WriteLine($"Error reading the CSV file: {ex.Message}");
   }

}
}

Blockquote

one of the simplest start could look like this:

Invoke Code:
grafik

Using r As StreamReader = New StreamReader(strFilePath)
  strText = r.ReadToEnd()
End Using

With Arguments:

myText Variable | String: the text
myFilePath | String: the full file path to the text file

This approach can be advanced to:

  • buffered reading / line reading
  • enhanced error handling
1 Like

@ppr Tq so much its working , im able to get read the data , but later im trying to pass that string to generate data table so that i can get a datatable, but im getting the below error

So it is better to share with us all details and maybe samples at the begin:

In general we can parse the large text data / csv data

  • reading / chunking / parsing / mersing the chunk
    • with essential activities or LINQ

Hi @Nissy_Ruth_Prabhu ,

Maybe could you also check with the below modified code if it works :

try
   {
       using (StreamReader reader = new StreamReader(csvFilePath)) 
       {
		  int count = 0;
		   
           while (!reader.EndOfStream)
           {
               string line = reader.ReadLine();
               if (!string.IsNullOrEmpty(line))
               {
                   string[] fields = line.Split(';');
				   if(count==0)
				   {
				   		foreach(string field in fields)
						{
							out_dt.Columns.Add(field);
						}
				   }else
				   {
                   // Process the fields as needed, for example:
	                       out_dt.Rows.Add(fields);
               		}
         	 	}
			   count++;
       		}
   		}
   }
   catch (Exception ex)
   {
       Console.WriteLine($"Error reading the CSV file: {ex.Message}");
   }

Arguments :

DT is a Datatable created using Build Datatable activity with no columns and no rows.
image

As Already mentioned, we would need a sample data to understand the structure delimiter used. Current code works with Semicolon (; ) as separator but could easily be changed to the required.

However a check is needed to understand whether it is efficient enough.

There are lot of syntax errors in the above code. ,

@Nissy_Ruth_Prabhu ,

We believe your issue is resolved now.

But if you still wish to check on the code, you could let us know what were the Errors encountered. A Screenshot would help to analyse better, also if you have Selected VB as the Language, change it to C# and check.

Dear @ppr @supermanPunch

I tried using the Code the bot is able to read for lesser data but the bot is not able to read the file for huge data, please find the below screenshot

lets check the part step (reading, not parsing). Once this is working, we will advance it, similar to @supermanPunch approach

grafik

Lines  = New List(Of String)()
Using r As StreamReader = New StreamReader(strFilePath)
  Do While ( Not r.EndOfStream)
	  Lines.Add(r.ReadLine())	  
  Loop
End Using

In case the reading will also fail, we will check for buffered Reading approaches