jeudi 29 août 2013

Import from excel: the mixed colums issue

From the sas knowledge base I get some usefull information : TypeGuessRows

Basically TypeGuessRows is the number of rows explored by the driver to determine the type of the columns.By default it is set to 8.

I suggest to set it to 0, that is to explore all rows. This may lead to performance issue, but prevents data loss.
According to the cited article the keys are:
  1. HKEY_LOCAL_MACHINE/Software/Microsoft/Office/12.0/Access Connectivity Engine/Engines/Excel/TypeGuessRows
  2. HKEY_LOCAL_MACHINE/Software/Wow6432Node/Microsoft/Office/12.0/Access Connectivity Engine/Engines/Excel/TypeGuessRows
But I also find them :
  1. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel
  2. HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel
The best is to scan the register searching : TypeGuessRows.

lundi 25 mars 2013

Excecute SQL batch from C#

Two ways, extending two objects. For a SSMS like use, batchSeparator should be equal to go.

    public static class SomeExtensionsClass {
        public static void ExecuteBatchFromFile(this DataContext dc, String fileName, String batchSeparator) {
            StringBuilder sb = new StringBuilder();
            foreach (String sqlLine in File.ReadAllLines(fileName)) {
                if (sqlLine == batchSeparator) {
                    if (sb.Length != 0) {
                        dc.ExecuteCommand(sb.ToString());
                        sb.Remove(0, sb.Length); //On attend de passer en .Net 4 pour le .Clear
                    }
                } else {
                    sb.AppendLine(sqlLine);
                }
            }
            if (sb.Length != 0)
                dc.ExecuteCommand(sb.ToString());
        }

        public static void ExecuteBatchFromFile(this ObjectContext oc, String fileName, String batchSeparator) {
            StringBuilder sb = new StringBuilder();
            foreach (String sqlLine in File.ReadAllLines(fileName)) {
                if (sqlLine == batchSeparator) {
                    if (sb.Length != 0) {
                        oc.ExecuteStoreCommand(sb.ToString());
                        sb.Remove(0, sb.Length); //On attend de passer en .Net 4 pour le .Clear
                    }
                } else {
                    sb.AppendLine(sqlLine);
                }
            }
            if (sb.Length != 0)
                oc.ExecuteStoreCommand(sb.ToString());
        }
    }