locked
Read CSV to SQL table using LINQ RRS feed

  • Question

  • User-1877975950 posted

    Hi all,

    The code below is shown as the solution for reading CSV to SQL table using LINQ. I find this approach too inflexible.

    Is there a more flexible way to read column names from the CSV file first line and data from all subsequent lines AND then a foreach loop inserts to a SQL table without having to use an intermediate datatable AND without using a 3rd part library?

    Many thanks

    Stew

    Here is the code I don't like:

    string[] csvlines = File.ReadAllLines(filename);

    var query = from csvline in csvlines

    let data = csvline.Split(',')

                           select new{

                                ID = data[0],

                                FirstNumber = data[1],

    SecondNumber = data[2],

                                ThirdNumber = data[3]

                            };

     

    Wednesday, August 19, 2015 6:03 AM

Answers

All replies

  • User-821857111 posted

    You can use the TextFieldParser class: http://www.mikesdotnetting.com/article/279/reading-text-based-files-in-asp-net

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 19, 2015 6:44 AM
  • User-1877975950 posted

    Hi Mike,

    In between posting and seeing your answer, I tested the VBReader approach but couldn't immediately get to the CSV file headers.

    Could you show this please?

    You deserve the answer points. Thank you.

    I have settled on the method below, if for no other reason than to give back to the community. 

    I like this approach because of its simplicity. I use a CSV with headers and do not anticipate any data gaps.

    I would be very pleased to receive your further comments/restrictions/improvements. It would benefit the community also. :-)

    var s = File.ReadAllLines(filename) .Select((i) =>

    new { Fields = i.Split(new char[] { ',' }) });

    var header = s.First();

    var rows = s.Skip(1).ToList();

    Wednesday, August 19, 2015 10:39 AM
  • User-821857111 posted

    My article (the one that I linked to) demonstrates how to handle the header row.

    The File.ReadAllLines and string.Split functions are fine if you have control over the format and content of your file and it's simple. However,  the code you posted will fall over if you have text in one of the fields that contains commas, for example

    Wednesday, August 19, 2015 11:18 AM
  • User-1877975950 posted

    Hi Mike,

    I implemented the Header example and it ignores the header row and takes the first data row as headers. I would like to get this method working so if you have a suggestion then please advised.

    The headers are definitely in the data as the first row in the CSV. It's a bit weird that they are being skipped.

    Also, I have control of the CSV and so it is v. unlikely that we would see commas in the data.

    The reason I am going down this route is to replace 'JET'. It is coming up with strange results when whole numbers are read before decimals. Also I read jet is unsupported so we need to move on a bit.

    many thanks

    Stew

    Thursday, August 20, 2015 3:37 AM
  • User-821857111 posted

    I implemented the Header example and it ignores the header row and takes the first data row as headers

    Can you show the code you have implemented?

    Thursday, August 20, 2015 3:48 AM