Importing a table with a few empty cells


Hi guys,  

I'm making a class for importing a table from a text file with tab delimited data.  

It works perfectly as long as there are no empty cells in the text file.



(see attached example)



Any suggestions how to detect an empty cell and what to fill the ArrayList with if the cell is empty?



```
public class ImportFileTableModel extends AbstractTableModel {

    protected ArrayList data;
    protected ArrayList columnNames ;
    protected String datafile;
    
    public ImportFileTableModel(String f){
        datafile = f;
        initArrayLists();
    }
    
    public void initArrayLists() {
        
        String aLine ;
        data = new ArrayList();
        columnNames = new ArrayList();
        
        try {
            FileInputStream fin =  new FileInputStream(datafile);
            BufferedReader br = new BufferedReader(new InputStreamReader(fin));
            // extract column names
            StringTokenizer st1 = new StringTokenizer(br.readLine(), "\t");
            while(st1.hasMoreTokens()) columnNames.add(st1.nextToken());
            // extract data
            while ((aLine = br.readLine()) != null) {
                StringTokenizer st2 = new StringTokenizer(aLine, "\t");
                while(st2.hasMoreTokens()) data.add(st2.nextToken());
            }
            br.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
 public int getRowCount() {
        return data.size() / getColumnCount();
    }
    
    public int getColumnCount(){
        return columnNames.size();
    }
```
> StringTokenizer is a legacy class that is retained for compatibility reasons although its use is discouraged in new code. It is recommended that anyone seeking this functionality use the split method of String or the java.util.regex package instead.
> 
> 


(API doc for StringTokeniser)



You should do a lot better by reading one whole line at a time then splitting it (String split method) on the tab delimiters to get a String array. I haven't tested this but you should get array elements of "" where there are consecutive tabs, although it may be better to trim() the elements to get rid of any spurious blanks.
Check if any of the values in the cells are null.  

If they are you need to modify your code to check for null conditions before using `ToString()`. If I am not wrong, you are trying to read or loop through the rows from datatable. This error states that the datasource(DataTable) is not having any rows. To avoid this exception use this before you read or loop through datatable:  


```
if(dt.Rows.Count  > 0 ){
     //Do your stuff here.
}
```
  

  

  

--Amit for this, just use an if function. see https://macmost.com/learn-how-to-use-the-if-function-in-numbers.html so something like this in a2: if(isblank(b2),c2,b2) the condition is to check if b2 is blank. if it is, get the value of c2. if it isn’t blank, then get the value of b2. Resolved:  

  

FORM 1.  

  

Copy Code

```
private void btnModificaContractul\_Click(object sender, EventArgs e)
        {


            string[] IDProdusContractPrelugit = new string[dataProduseContract.Rows.Count];
            string[] IDProdusContractRetras = new string[dataProduseContract.Rows.Count];

            for (int i = 0; i < dataProduseContract.Rows.Count; i++)
            {

                if (Convert.ToBoolean(dataProduseContract.Rows[i].Cells[0].Value) == false)
                {
                    IDProdusContractPrelugit[i] = dataProduseContract.Rows[i].Cells[5].Value.ToString();
                }
                else
                {
                    IDProdusContractRetras[i] = dataProduseContract.Rows[i].Cells[5].Value.ToString();
                }
            }

           

            try
            {
                frmModificaContract ModificareContract = new frmModificaContract(IDProdusContractPrelugit, IDProdusContractRetras);
                ModificareContract.ShowDialog();
            }
            catch { MessageBox.Show("Trebuie să selectaţi un contract din listă pentru a edita.", "Datele furnizate sunt incomplete."); }
           
        }
```
  

  

FORM 2.  

  

Copy Code

```
string[] IDProdusContractPrelugit;
        string[] IDProdusContractRetras;

        public frmModificaContract(string[] IDProdusContractPrelugit, string[] IDProdusContractRetras)
        {
            InitializeComponent();
            this.IDProdusContractPrelugit = IDProdusContractPrelugit;
            this.IDProdusContractRetras = IDProdusContractRetras;
            AfisareProduseContract();
        }


        void AfisareProduseContract()
        {

            foreach (string ContractPrelungit in IDProdusContractPrelugit)
            {
                if (ContractPrelungit != null)
                {
                    Program.Connection.CommandText = "select * from ContractItems WHERE ContractItemId=@ContractItemId";
                    Program.Connection.AddParameter("@ContractItemId", ContractPrelungit);

                    DataTable Table = new DataTable();
                    Program.Connection.FillDataTable(Table, true);


                    for (int i = 0; i < Table.Rows.Count; i++)
                    {
                        if (String.IsNullOrWhiteSpace(Table.Rows[i]["DeletedId"].ToString()))
                        {
                            bool validare;
                            validare = true;

                            dataProduseContract.Rows.Add(validare, Table.Rows[i]["Name"].ToString(), Table.Rows[i]["Quantity"].ToString() + " " + Table.Rows[i]["QuantityUnit"].ToString(), Table.Rows[i]["Payment"].ToString(), Table.Rows[i]["Description"].ToString(), Table.Rows[i]["ContractItemId"].ToString());
                        }
                        else
                        {
                            bool validarescos;
                            validarescos = false;
                            dataProduseContract.Rows.Add(validarescos, Table.Rows[i]["Name"].ToString(), Table.Rows[i]["Quantity"].ToString() + " " + Table.Rows[i]["QuantityUnit"].ToString(), Table.Rows[i]["Payment"].ToString(), Table.Rows[i]["Description"].ToString(), Table.Rows[i]["ContractItemId"].ToString());
                            dataProduseContract.Enabled = false;
                        }

                    }
                }
            }

            
        }
```
  

  

The problem is allwais i have Null value in array. Your test is redundant, since you are already querying only for matching records. In this case, you will get no rows if there is no match, therefore you will never hit the else clause in the loop.  

  

Instead, it is enough to check if there are any matching records at all. The example below shows how (note that you can put a condition straight into Any() instead of specifying Where()).  

  

C#Copy Code

```
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
 
namespace CustomerMaster
{
    public partial class CustomerMaster : System.Web.UI.Page
    {
 
        private void AddData()
        {
            
            OperationsDataContext AddDb = new OperationsDataContext();
            CustomerMast cm = new CustomerMast();
            
                        
            cm.CustomerCode = txtCustCde.Text;
            cm.CustomerName = txtCustName.Text;
            cm.CustomerAddress = txtCustAdd.Text;
            cm.BrkrCode = txtBrkCode.Text;
            cm.BrokerName = txtBrkName.Text.
            cm.CrrCode = txtCrrCode.CarrierCode;
            cm.CarrierName =  txtCrrName.Text;
            cm.StnCode=sm.StationCode;
            cm.StationName = txtStnName.Text;
            cm.VatNo=txtVatNo.Text;
            cm.TinNo=txtTinNo.Text;
            cm.TMCO=txtTMCONo.Text;
            cm.PanNo = txtPAN.Text;
			
	    if(AddDb.CustomerMasts.Any(c=>c.CustomerCode == txtCustCde.Text))
            {
	        Response.Write("Error! CustomerCode Exits");
	    }
	    else
	    {
                AddDb.BrokerMasts.InsertOnSubmit(bm);
                AddDb.CarrierMasts.InsertOnSubmit(crm);
                AddDb.StationMasts.InsertOnSubmit(sm);
                AddDb.CustomerMasts.InsertOnSubmit(cm);

	        AddDb.SubmitChanges();
            }         
        }
 
        protected void btnAdd\_Click(object sender, EventArgs e)
        {
            AddData();
        }
    }
}
``` Turns out If I tweked each method 



```
foreach (var dataColumn in readFields.Select(column => new DataColumn(columntypeof(string)) { AllowDBNull = true, DefaultValue = string.Empty }))
{
    csvData.Columns.Add(dataColumn);
}

```

and in the second Method I changed it to use an index rather than column name



```
for (var count = 0; count < table.Columns.Count; count++)
{
    sqlBulkCopy.ColumnMappings.Add(count, count);
}

``` here is an example notebook

<https://gist.github.com/volkerh/465925d24accc940f7a0a0b1f5f40f7a#file-map_array_example-ipynb>

map\_array\_example.ipynb

{

"cells": [

{

"cell\_type": "code",

"execution\_count": 1,

"id": "satellite-momentum",

"metadata": {},

"outputs": [],

"source… you can use lazy evaluation so if the first expression returns true it will not check the second expression. just change the ‘or’ in your statement to ‘orelse’ so it would be:

“string.isnullorempty(message) orelse message.contains(”!")" You could try using the in line view like this:


Select table1.col1, table1.col2, table1…., table2.col1, table2.col2, table2….  

 from  

(SELECT *  

FROM dbo.Shaft\_Scales  

WHERE dbo.Shaft\_Scales.ID = (SELECT max(ID) from dbo.Shaft\_Scales ID) as table1,  

(SELECT *  

FROM OtherTable  

WHERE dbo.OtherTable.ID = (SELECT max(ID) from dbo.OtherTable ID) as table2


—————- another approach from meandyou 


This works on [at least some] SQL processors


SELECT T1.* , T2.*  

FROM table1 T1, table2 T2  

WHERE …


Do we have to discuss why not to use “SELECT * ” in your SQL?<pre>


Try this


SELECT * FROM T1 as a, T2 as b WHERE a.ID = (SELECT max(ID) from T1)  

and b.ID = (SELECT max(ID) from T2);</pre> How you do this will depend on a number of factors: since you haven't tried to explain in any detail, I'll assume you know nothing, and just give you the basics.  

  

The easiest way to do this is to read the text file into a single string, and then use the .NET framework to find the match:  


```
Dim text As String = File.ReadAllText("D:\Temp\MyFile.txt")
Dim index As Integer = text.IndexOf("hello")
If index >= 0 Then
   ' String is in file, starting at character "index"
End If
```
  

There are other ways, but that is pretty much the simplest.