locked
Add new column in existing csv file in c# RRS feed

  • Question

  • User1036972001 posted

    Hi all

    I need insert new columns into one existing CSV file updated each day and as pipeline delimited

    ||||||||||||||||||||||||||||||||||||||||||||||||||
    |Table1|||||||||||||||||||||||||||||||||||||||||||||||||            
    ||||||||||||||||||||||||||||||||||||||||||||||||||          
    N|IDI  |TEST|START DATE HOUR    |CAUSE|KIND|NUMB|NAMES|         
    1|10704|    |21/07/2020 15:05:54|L    |MT  |2786|NAV  |         
    2|10660|    |21/07/2020 09:27:31|L    |MT  |4088|PIS  |     
    ||||||||||||||||||||||||||||||||||||||||||||||||||          
    |Table2|||||||||||||||||||||||||||||||||||||||||||||||||            
    ||||||||||||||||||||||||||||||||||||||||||||||||||          
    N|IDI  |TEST|START DATE HOUR    |END DATE HOUR      |LENGHT  |RETURNS         |CAUSE|KIND|NUMB|NAMES|           
    1|10710|    |21/07/2020 19:34:00|21/07/2020 20:19:09|00:45:09| -              |L    |MT  |7806|ACC  |
    2|10708|    |21/07/2020 18:28:12|21/07/2020 18:28:13|00:00:01| -              |T    |MT  |2600|LIT  |       
    3|10700|    |21/07/2020 14:16:37|21/07/2020 15:19:13|01:02:36|21/07/2020 17:00|L    |MT  |4435|UHI  |       
    4|10698|    |21/07/2020 14:06:45|21/07/2020 14:07:22|00:00:37|-               |B    |MT  |5789|TYK  |
    5|10674|    |21/07/2020 10:21:04|21/07/2020 10:44:41|00:23:37|21/07/2020 12:30|T    |MT  |6699|FGR  |
    ||||||||||||||||||||||||||||||||||||||||||||||||||

    Note that the number of columns between table 1 and table 2 are different on CSV file

    For table 1 the number of column is 8

    For table 2 the number of column is 10

    I need adding missing columns into table 1 vs table 2

    1. END DATE HOUR
    2. LENGHT

    I have tried this code below, but I have error

    Index was out of range. Must be non-negative and less than the size of the collection parameter name:index

    on this line 

    if (line[1] == "Table2")

    when in csv file I have these rows

    ||||||||||||||||||||||||||||||||||||||||||||||||||
    |Table1|||||||||||||||||||||||||||||||||||||||||||||||||            
    ||||||||||||||||||||||||||||||||||||||||||||||||||          
    N|IDI  |TEST|START DATE HOUR    |CAUSE|KIND|NUMB|NAMES|         
    1|10704|    |21/07/2020 15:05:54|L    |MT  |2786|NAV  |         
    2|10660|    |21/07/2020 09:27:31|L    |MT  |4088|PIS  |     
    values of names 
    values of names .|0|0|1|1|0|0||||
    ||||||||||||||||||||||||||||||||||||||||||||||||||          
    |Table2|||||||||||||||||||||||||||||||||||||||||||||||||            
    ||||||||||||||||||||||||||||||||||||||||||||||||||          
    N|IDI  |TEST|START DATE HOUR    |END DATE HOUR      |LENGHT  |RETURNS         |CAUSE|KIND|NUMB|NAMES|           
    1|10710|    |21/07/2020 19:34:00|21/07/2020 20:19:09|00:45:09| -              |L    |MT  |7806|ACC  |
    2|10708|    |21/07/2020 18:28:12|21/07/2020 18:28:13|00:00:01| -              |T    |MT  |2600|LIT  |       
    3|10700|    |21/07/2020 14:16:37|21/07/2020 15:19:13|01:02:36|21/07/2020 17:00|L    |MT  |4435|UHI  |       
    4|10698|    |21/07/2020 14:06:45|21/07/2020 14:07:22|00:00:37|-               |B    |MT  |5789|TYK  |
    5|10674|    |21/07/2020 10:21:04|21/07/2020 10:44:41|00:23:37|21/07/2020 12:30|T    |MT  |6699|FGR  |
    ||||||||||||||||||||||||||||||||||||||||||||||||||

    I need delete these rows on csv file

    values of names 
    values of names .|0|0|1|1|0|0||||

    code behind

    int posNewColumn = 4;
    
    string input = @"C:\Temp\SO\import.csv";
    string output = @"C:\Temp\SO\out.csv";
    
    string[] CSVDump = File.ReadAllLines(input);
    List<List<string>> CSV = CSVDump.Select(x => x.Split('|').ToList()).ToList();
    foreach (List<string> line in CSV)
    {
        if (line[1] == "Table2")
        {
            break;
        }
        line.Insert(posNewColumn, line[0] == "N" ? "LENGHT" : string.Empty);
        line.Insert(posNewColumn, line[0] == "N" ? "END DATE HOUR" : string.Empty);
    }
    
    File.WriteAllLines(output, CSV.Select(x => string.Join("|", x)));

    This the csv file

    How to do resolve this?

    Thanks in advance for any help

    Tuesday, August 25, 2020 8:33 AM

Answers

  • User314352500 posted

    I understand that you want to keep anything starting with a number, a pipeline or a letter N, right?

    If right, try this

    List<List<string>> CSV = CSVDump
      .Where(x => x.Length > 0 && "0123456789N|".Contains(x[0]))
      .Select(x => x.Split('|').ToList()).ToArray();

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 26, 2020 7:53 AM
  • User1535942433 posted

    Hi Koopers,

    Accroding to your description and codes,I have create a test. Could you tell us where do you use these rows in csv file?

    values of names 
    values of names .|0|0|1|1|0|0||||

    How do you add these rows into csv file?

    I have tried this code below, but I have error

    Index was out of range. Must be non-negative and less than the size of the collection parameter name:index

    Your problem is when you excute the "values of names " row, line count is 1.So line[1] was out of range.

    Best regards,

    Yijing Sun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 26, 2020 8:18 AM
  • User303363814 posted

    Before you examine the second entry of the List, check that there are at least two entries in the list

    if (line.Count() >= 2 && line[1] == "Table2")

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 27, 2020 3:31 AM

All replies

  • User475983607 posted

    First, CSV stands for comma separated values.  You have a pipe delimited text file not a CSV file.  Secondly, this question has nothing to do with ASP.NET. 

    You are simply building a text file.   This type of problem is generally solved use standard ETL tools found in the database tool-chain.  SQL server SSIS can handle this.  TSQL can handle this if you fell like writing code.  This can also be handled point-and-click using SQL Management studio;https://stackoverflow.com/questions/18022570/sql-server-export-from-file-sql-to-a-flat-file.

    There are also NuGet packages that you can take advantage of to read/write delimited files; https://nugetmusthaves.com/Tag/delimited

    I do not recommend building this yourself since this problem has been solved and code tested.  

    Tuesday, August 25, 2020 1:27 PM
  • User314352500 posted

    I understand that you want to keep anything starting with a number, a pipeline or a letter N, right?

    If right, try this

    List<List<string>> CSV = CSVDump
      .Where(x => x.Length > 0 && "0123456789N|".Contains(x[0]))
      .Select(x => x.Split('|').ToList()).ToArray();

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 26, 2020 7:53 AM
  • User1535942433 posted

    Hi Koopers,

    Accroding to your description and codes,I have create a test. Could you tell us where do you use these rows in csv file?

    values of names 
    values of names .|0|0|1|1|0|0||||

    How do you add these rows into csv file?

    I have tried this code below, but I have error

    Index was out of range. Must be non-negative and less than the size of the collection parameter name:index

    Your problem is when you excute the "values of names " row, line count is 1.So line[1] was out of range.

    Best regards,

    Yijing Sun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 26, 2020 8:18 AM
  • User303363814 posted

    Before you examine the second entry of the List, check that there are at least two entries in the list

    if (line.Count() >= 2 && line[1] == "Table2")

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 27, 2020 3:31 AM
  • User1036972001 posted

    thanks all for this help

    Thursday, August 27, 2020 11:40 AM