Issue in Reading csv file aspnet cstring FilePathServerMapPath34ExcelLists34 PathGetFileNameFileUploadexcelFileNameToString string csvData FileReadAllTextFilePathforeach string row in csvDataSplit39n39 Execute a loop over the columns foreach string cell in rowSplit39t39 dt1Rowsdt1RowsCount 1i cell its not splitting rows cells correctlyif i click on csv file cells and save it then it works correctly

Read data from custom CSV (Text) file using C# and VB.Net in ASP.Net

mukesh1

SOLVED CLOSED ACTIVE ANSWERED
User: mukesh1
Posted: on May 27, 2020 05:00 AM
Forum: Data Controls
Answer: 1
Views: 6511

Issue in Reading csv file asp.net c#

string FilePath=Server.MapPath("~/Excel_Lists/" + Path.GetFileName(FileUploadexcel.FileName)).ToString(); 
string csvData = File.ReadAllText(FilePath);
foreach (string row in csvData.Split('\n'))
{
    //Execute a loop over the columns.
    foreach (string cell in row.Split('\t'))
    {
        dt1.Rows[dt1.Rows.Count - 1][i] = cell;
    }
}

its not splitting rows cells correctly,,,if i click on csv file cells and save it, then it works correctly, othwerwise it shows error

My Csv file data

date/time	settlement id	type	order id	sku	description	quantity	marketplace	fulfillment	order city	order state	order postal	tax collection model	product sales	product sales tax	shipping credits	shipping credits tax	gift wrap credits	giftwrap credits tax	promotional rebates	promotional rebates tax	marketplace withheld tax	selling fees	fba fees	other transaction fees	other	total
Jan 1, 2020 12:38:06 AM PST	12551492991	Adjustment		19Q4CoR518090-KSArtichokeHearts33oz2Pk	FBA Inventory Reimbursement - Damaged:Warehouse	2							0	0	0	0	0	0	0	0	0	0	0	0	23.84	23.84
Jan 1, 2020 2:13:38 AM PST	12551492991	Adjustment	114-6752853-3545812	19Q4CoBStk-100LEDMiniatureLights	FBA Inventory Reimbursement - Customer Return	1							0	0	0	0	0	0	0	0	0	0	0	0	3.85	3.85
Jan 1, 2020 2:16:54 AM PST	12551492991	Adjustment		1910CoR1218967-PanasonicBluetooth3HSPh	FBA Inventory Reimbursement - Lost:Warehouse	1							0	0	0	0	0	0	0	0	0	0	0	0	41.39	41.39
Jan 1, 2020 5:55:46 AM PST	12551492991	Adjustment	114-6752853-3545812	19Q4CoBStk-100LEDMiniatureLights	FBA Inventory Reimbursement - Customer Return	1							0	0	0	0	0	0	0	0	0	0	0	0	3.85	3.85
Jan 1, 2020 6:29:55 AM PST	12551492991	Adjustment	113-8937203-7736242	19Q4CoBStk-100LEDMiniatureLights	FBA Inventory Reimbursement - Customer Return	1							0	0	0	0	0	0	0	0	0	0	0	0	3.91	3.91
Jan 1, 2020 7:30:02 AM PST	12551492991	Adjustment	113-9661337-0251441	19Q4CoBStk-100LEDMiniatureLights	FBA Inventory Reimbursement - Customer Return	1							0	0	0	0	0	0	0	0	0	0	0	0	3.91	3.91
Jan 1, 2020 4:07:32 PM PST	12556440361	Order	111-4537955-0941844	19Q5CoS617550-NMOrgFlxSeed1400mg300ct2pk	Nature Made Organic Flaxseed Oil 1,400 mg - Omega-3-6-9 for Heart Health - 300 Softgels(Pack of 2)	1	amazon.com	Amazon	Flowood	MS	39232-8105		40.63	0	0	0	0	0	0	0	0	-6.09	-5.64	0	0	28.9

check file here https://drive.google.com/file/d/1pVxK_GPKidqgvwFtXlQ_ly_PjHV4FLo3/view?usp=sharing

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 4

Reply
ANSWER
User: dharmendr
Replied: on May 27, 2020 07:05 AM
Modified: on May 28, 2020 06:05 AM
Report

Himukesh1,

Since your first column contains comma so the issue arises.

So you need to apply costom logic to split the row assign to the cell.

Check below code.

protected void OnUpload(object sender, EventArgs e)
{
    string FilePath = Server.MapPath("~/2020JanMonthlyTransaction.csv").ToString();
    string csvData = File.ReadAllText(FilePath);
    DataTable dt = new DataTable();
    int j = 0;
    foreach (string row in csvData.Split('\n'))
    {
        if (j == 7)
        {
            foreach (string cell in row.Split(','))
            {
                dt.Columns.Add(cell.Replace("\"", ""));
            }
        }
        else if (j > 7)
        {
            int i = 0;
            DataRow dr = dt.NewRow();
            foreach (string cells in row.Split(new[] { "\"," }, StringSplitOptions.None))
            {
                dr[i] = cells.Replace("\"", ""); ;
                i++;
            }
            dt.Rows.Add(dr);
        }
        j++;
    }
}

VB.Net

Protected Sub OnUpload(ByVal sender As Object, ByVal e As EventArgs)
    Dim FilePath As String = Server.MapPath("~/2020JanMonthlyTransaction.csv").ToString()
    Dim csvData As String = File.ReadAllText(FilePath)
    Dim dt As DataTable = New DataTable()
    Dim j As Integer = 0

    For Each row As String In csvData.Split(vbLf)
        If j = 7 Then
            For Each cell As String In row.Split(","c)
                dt.Columns.Add(cell.Replace("""", ""))
            Next
        ElseIf j > 7 Then
            Dim i As Integer = 0
            Dim dr As DataRow = dt.NewRow()
            For Each cells As String In row.Split({""","}, StringSplitOptions.None)
                dr(i) = cells.Replace("""", "")
                i += 1
            Next

            dt.Rows.Add(dr)
        End If

        j += 1
    Next
End Sub

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Read data from custom CSV (Text) file using C# and VB.Net in ASP.Net

Company

Explore

Follow us

Contact Us