ExcelReader Factory Fails on last write line

Question

I have an Excel file WKDEL.xls. I want to read all records and import them into an SQL Table I already created.

Example Excel file:

ID      | FNAME        | T_W_TON  | Y_D_TON  | WKEND
--------+--------------+----------+----------+--------------
8118.00 | TOM, COLLIIN |  0.00000 |  0.00000 | 12/8/2018
   3.00 | JOHN, DOE    | 60.22500 | 60.22500 | 12/8/2018
   8.00 | KIM, JUNG    |  0.00000 |  0.0000  | 12/8/2018

I need all records that have a T_W_TON and are greater than 0 basically and import them to my SQL table:

IDCODE: real
FNAME : nvarchar(50)
T_W_TON: real
Y_D_TON: real
WEKEND: date
CROP_SEASON: int(3 as default)

So far I have been able to load an excel file but not sure how to iterate through the values. The code errors on the for loop. I would also want to skip the header values.

 private void GetWeeklyDeliveries()
    {    
        string FilePath = textBox1.Text;
        using (var fileStream = new FileStream(FilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
        using (var rdr = ExcelReaderFactory.CreateReader(fileStream))
        {
            using (Stream fos = File.Open(@"C:\Fuel\ZINVOW.dbf", FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                //Create New File or Open New File.
                var writer = new DBFWriter();
                var bsi_code = new DBFField("HIS_ID", NativeDbType.Char, 25, 0);
                var f_name = new DBFField("FNAME", NativeDbType.Char, 50, 0);
                var t_w_ton = new DBFField("T_W_TON", NativeDbType.Char, 25, 0);
                var y_d_ton = new DBFField("Y_D_TON", NativeDbType.Char, 25, 0);
                var w_k_end = new DBFField("WKEND", NativeDbType.Char, 25, 0);
                var loc = new DBFField("LOC", NativeDbType.Char, 20, 0);
                var b_name = new DBFField("B_NAME", NativeDbType.Char, 50, 0);
                var a_name = new DBFField("A_NAME", NativeDbType.Char, 50, 0);
                var fuel_rate = new DBFField("FUELRATE", NativeDbType.Numeric, 25, 5);
                var payment = new DBFField("PAYMENT", NativeDbType.Numeric, 25, 5);
                var hun_fig = new DBFField("HUNFIG", NativeDbType.Char, 12, 0);
                var ten_fig = new DBFField("TENFIG", NativeDbType.Char, 12, 0);
                var one_fig = new DBFField("ONEFIG", NativeDbType.Char, 12, 0);
                var hundred = new DBFField("HUNDRED", NativeDbType.Char, 12, 0);
                var teen = new DBFField("TEEN", NativeDbType.Char, 12, 0);
                var ten = new DBFField("TEN", NativeDbType.Char, 12, 0);
                var one = new DBFField("ONE", NativeDbType.Char, 12, 0);
                var words = new DBFField("WORDS", NativeDbType.Char, 50, 0);
                var authority = new DBFField("AUTHORITY", NativeDbType.Char, 12, 0);
                var thoufig = new DBFField("THOUFIG", NativeDbType.Char, 12, 0);
                var thousand = new DBFField("THOUSAND", NativeDbType.Char, 12, 0);
                var newrate = new DBFField("NEWRATE", NativeDbType.Numeric, 25, 5);
                var sn = new DBFField("SN", NativeDbType.Numeric, 25, 0);
                var lic = new DBFField("LIC", NativeDbType.Char, 12, 0);
                var dealloc = new DBFField("DELALOC", NativeDbType.Char, 12, 0);
                var fee = new DBFField("FEE", NativeDbType.Numeric, 12, 5);

                writer.Fields = new[] { bsi_code, f_name, t_w_ton, y_d_ton, w_k_end, loc, b_name, a_name, fuel_rate, payment, hun_fig, ten_fig, one_fig, hundred, teen, ten, one, words, authority, thoufig, thousand, newrate, sn, lic, dealloc, fee };

                while (rdr.Read())
               {//Begin Reading Delivery File (XLS) 
                var bsicode = rdr[0];
                var fname = rdr[1];
                var twton = rdr[2];
                var ydton = rdr[3];
                var wkend = rdr[4];


                if (bsicode.ToString() == "HIS_ID")
                {
                    //Skip First Record
                }
                else if(Convert.ToDouble(twton.ToString()) > 0)
                {

                        var bsicodex = bsicode.ToString();
                        var fnamex = fname.ToString();
                        var t_w_tonx = twton.ToString();
                        var y_d_tonx = ydton.ToString();
                        var w_k_endx = wkend.ToString();
                        var locx = "";
                        var b_namex = "";  
                        var a_namex = "";
                        var fuel_ratex = ""; 




                        var paymentx = "70.00";//need to Calculate
                        var hun_figx = "";
                        var ten_figx = "";
                        var one_figx = "";
                        var hundredx = "";
                        var teenx = "";
                        var tenx = "";
                        var onex = "";
                        var wordsx = "";
                        var authorithyx = "";
                        var thoufigx = "";
                        var thousandx = "";
                        var newratex = 1.19 * 6; //Need to Calculate
                        var snx = 1234;    //Need to autogenerate can Be Primary Key
                        var licx = "";
                        var deallocx = "";
                        var feex = 1.3*34;   //Need to Calculate
                        writer.AddRecord(bsicodex, fnamex, t_w_tonx, y_d_tonx, w_k_endx, locx, b_namex, a_namex, fuel_ratex.ToString(), paymentx, hun_figx, ten_figx, one_figx, hundredx, teenx, tenx, onex, wordsx, authorithyx, thoufigx, thousandx, newratex.ToString(),snx,licx,deallocx,feex.ToString());


                    }

                }


                writer.Write(fos);//CRASHES HERE 



            }

        }

    }

Is this something you have to do one time (to get the data set up), or multiple times (as part of a process)? If it's one time, you may be able to use a tool and avoid writing code altogether. — Ann L., Dec 28 '18 at 01:25
first to skip the header (first row) call rdr.Read() once before the while loop — Joe B, Dec 31 '18 at 04:10

Joe B · Answer 1 · 2018-12-27T20:16:18.653

2

The way I did it I used a easy to use and good tool ExcelDataReaderfrom nuget

  using (var fileStream = new FileStream(_fileLocation, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
        using (var rdr = ExcelReaderFactory.CreateReader(fileStream))
        { 

            while(rdr.Read())
            {

                //add it to a data table 
            }

        }

Then use SqlBulkCopy to write the DataTable to the server

edited Dec 27 '18 at 20:16

answered Dec 27 '18 at 19:49

Joe B

738
7
28

var val = rdr[columnindex] – Joe B Dec 27 '18 at 23:02
This code below crashes within the while loop when i try to write to the dbf file. an issue with double declaration : var t_w_ton = new DBFField("T_W_TON", NativeDbType.Double,50, 0); var y_d_ton = new DBFField("Y_D_TON", NativeDbType.Double, 50,0); writer.Fields = new[] { bsi_code, f_name, t_w_ton, y_d_ton}; double t_w_tonx = Convert.ToDouble(59.34560); double y_d_tonx = Convert.ToDouble(73.34560); writer.AddRecord(bsicodex, fnamex, t_w_tonx, y_d_tonx); writer.Write(fos); – ivias Dec 29 '18 at 04:14
What is the writer object? Can you share your code? – Joe B Dec 30 '18 at 00:16
Hi Joey B. I added an updated code using the ExcelDataReader on my original post. however it only writes one record. I want it to add all the records for twton that are greater than 0 – ivias Dec 31 '18 at 04:04
I think you should post a new question with your issue with writing to a dbf file. what is the error that it throws? – Joe B Dec 31 '18 at 04:13
I updated the code again this time it does write to the dbf file but it doesnt write all items. Not sure how to debug and get specific error. Can you see code? Also when the dbf file gets created the cells say " the number in this cell is formatted as text or preceded by an apostrophe" It only wrote 301 out of 317 records. – ivias Dec 31 '18 at 04:50
I updated the question since now the only issue i have seems to be with the last write line. Not sure why it crashes there. – ivias Dec 31 '18 at 22:24

score 0 · Answer 2 · answered Dec 28 '18 at 01:23

I would skip the datatable and use a DbDataReader. Datatables add a lot of overheader, and in this case deconstructing them will be every bit as much work as just using a data reader and going after each column. If the columns line up, you can take them column-for-column during the insert. If not, it's minor adjustments to make it work.

Something like this should do the trick:

using (SqlConnection icon = new SqlConnection(sqlConnectionString))
{
    icon.Open();

    using (SqlCommand icmd = new SqlCommand(@"insert into table " +
        "values (@ID, @FNAME, @TW, @YD, @WKEND)", icon))
    {
        icmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Real));
        icmd.Parameters.Add(new SqlParameter("@FNAME", SqlDbType.NVarChar));
        icmd.Parameters.Add(new SqlParameter("@TW", SqlDbType.Real));
        icmd.Parameters.Add(new SqlParameter("@YD", SqlDbType.Real));
        icmd.Parameters.Add(new SqlParameter("@WKEND", SqlDbType.Date));

        using (OleDbConnection con = new OleDbConnection(connstring))
        {
            con.Open();

            using (OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]", con))
            {
                using (OleDbDataReader reader = cmd.ExecuteReader())
                {
                    reader.Read();  // Skip Header Row
                    while (reader.Read())
                    {
                        for (int i = 0; i < 5; i++)
                            icmd.Parameters[i].Value = reader.GetValue(i);

                        icmd.ExecuteNonQuery();
                    }

                    reader.Close();
                }
            }
        }
    }
}

The .GetValue(x) objects will be read as strings, so it's possible you will need to do some casts/conversions to turn them into reals... not sure. I think it depends on the ADO adapter, and I'm not sure how SQL Server's behaves. Some are more forgiving than others.

ExcelReader Factory Fails on last write line

2 Answers2