Best way to get CSV data loaded without losing trilling zeros

[MohammadNaveed]

Dear All,

i am looking for the solution to load CSV data into collection without losing trilling zeros.

currently, i written a Excel VBA code in Excel to import CSV data in Excel, and running the Macro from BP using Excel VBO -> Run Macro , then after reading the data using Excel VBO -> Get Collection.

This approach is taking lot of time when we are running very frequently as VBA Code takes memory and its slowing down the performance.

i want to write a code in Code Stage to get data from CSV file and CSV files will have more than 10K rows, i written a OLEDB code to fetch data from CSV and its ready fast however its losing the trilling zeros.

can anyone please suggest or guide me with right path to get CSV Data loaded in collection in optimized way without losing the trilling zeros.

[MohammadNaveed · Accepted solution]

finally i written this code to read data from CSV File using Loop and its taking 2 secs of time to read 14000 records.

sharing the code here, for others who might need this.

Dim SR As StreamReader = New StreamReader("yourpath\csvfilename.CSV")
Dim line As String = SR.ReadLine()
Dim strArray As String() = line.Split(","c)
Dim dt As DataTable = New DataTable()
Dim row As DataRow
Dim iCounter As Long = 0


For Each s As String In strArray
    dt.Columns.Add(New DataColumn(strArray(iCounter).ToString()))
    iCounter = iCounter + 1
Next

Do
   line = SR.ReadLine
   If Not line = String.Empty Then
        row = dt.NewRow()
        row.ItemArray = line.Split(","c)
        dt.Rows.Add(row)
   Else
        Exit Do
   End If
Loop

Dim rcnt As Long
rcnt = dt.Rows.Count​