Import large Excel file into SqlServer

I have to import the list of NGO from Romania to SqlServer.  Before seeing my solution , please do it yourself: the file could be found at http://www.just.ro/registrul-national-ong/ and the name is Asociatii

I have tried with SSMS – no luck ( text will be truncated, even when I put nvarchar(max))

The I tried with flat file from CSV  – same

Finally, I have solved with a little bit of VBA : first I have tried to write the insert into file and execute with sqlcmd. The file was too large, so sqlcmd does not report any errors( and not insert anything)

So the solution was to generate a batch file that have this

sqlcmd  -S . -E -d InfoRo -Q “INSERT INTO [dbo].[Asociatii$]  ([Denumire]  ,[Numar inreg Reg National],[pozitie inchisa (da/nu)],[Starea actuala],[Judet],[Localitate],[Adresa],[Asociati/Fondatori],[Scop],[Consiliu director],[Apartenenta federatie],[HG utilitate publica],[Data HG utilitate publica],[F14])values( ////)”

generated by this VBA script

Option Explicit

Sub x()
Dim startInsert As String

startInsert = "sqlcmd  -S . -E -d InfoRo -Q ""INSERT INTO [dbo].[Asociatii$]"
         startInsert = startInsert & "  ([Denumire]"
         startInsert = startInsert & "  ,[Numar inreg Reg National]"
           startInsert = startInsert & ",[pozitie inchisa (da/nu)]"
           startInsert = startInsert & ",[Starea actuala]"
           startInsert = startInsert & ",[Judet]"
           startInsert = startInsert & ",[Localitate]"
           startInsert = startInsert & ",[Adresa]"
           startInsert = startInsert & ",[Asociati/Fondatori]"
           startInsert = startInsert & ",[Scop]"
           startInsert = startInsert & ",[Consiliu director]"
           startInsert = startInsert & ",[Apartenenta federatie]"
           startInsert = startInsert & ",[HG utilitate publica]"
           startInsert = startInsert & ",[Data HG utilitate publica]"
           startInsert = startInsert & ",[F14])values("
           
           
           Dim i As Long, j As Integer, text As String
           Dim insert As String
           For i = 2 To 86813
            insert = insert & startInsert
            For j = Asc("A") To Asc("M")
                text = Sheet1.Range(Chr(j) & i).text
                text = Replace(text, vbCr, " ")
                text = Replace(text, vbLf, " ")
                text = Trim(text)
                 text = Replace(text, "'", "''")
                text = Replace(text, "  ", " ")
                
                insert = insert & "'" & text & "',"
            Next j
            insert = insert & "null)" & """"
            insert = insert & vbCrLf
            If (i Mod 150 = 0) Then
                Open "C:\Users\Surface1\Desktop\2Percent\a.bat" For Append As #1
                Print #1, insert
                Close #1
                insert = ""
            End If
           Next i
           If (Len(insert) > 0) Then
                Open "C:\Users\Surface1\Desktop\2Percent\a.bat" For Append As #1
                Print #1, insert
                Close #1
                insert = ""
            End If
          
           
          
           
     
End Sub


Obtain data from ANAF(local IRS)

In ROmania ANAF is providing a WebAPI that allows access to some of the information that any enterprise should provide. The API is described at https://webservicesp.anaf.ro/PlatitorTvaRest/api/v3/ ,

I have made a C# console and a node,js script.

The differences:

1.it is easiear in node to make an http request.

2. For C# – I think in components. For node – I think in application.

Without further ado, here is the code

using RestSharp;
using System;

namespace InfoAnafWS
{
    class Program
    {
        static void Main(string[] args)
        {
            if(args.Length == 0)
            {
                Console.WriteLine("lipseste CUI ");
                return;
            }
            var client = new RestClient("https://webservicesp.anaf.ro");
            var request = new RestRequest("PlatitorTvaRest/api/v3/ws/tva", Method.POST);
            request.RequestFormat = DataFormat.Json;
            string date = DateTime.Now.AddDays(-5).ToString("yyyy-MM-dd");
            string req = "";
            foreach (var item in args)
            {
                req += string.Format(@"{{'cui': {1}, 'data':'{0}'}}", date, item);
            }
            req = req.Replace("'", "\"");
            req = "[" + req + "]";

            request.AddParameter("application/json", req, ParameterType.RequestBody);
            var data = client.Execute(request);
            Console.WriteLine(data.Content);
        }
    }
}


const start = async () => {
	
let request = require('request');

const d = require('delay');
await d(1000);
var fs = require('fs');
var array = fs.readFileSync('CUI.txt').toString().split("\r\n");
var start=0;

while(start<array.length){
	var nr = Math.floor(Math.random() * Math.floor(495))+1;
	var jsonData=[];
	console.log('start at ' + start+  ' for nr ' + nr);
	var i=0;
	while(i<nr && i+start<array.length){
		jsonData.push({"cui": array[i+start], "data":"2018-03-26"});
		i++;		
	}
	
	var finish=false;
	console.log('array :'+ jsonData[0].cui + '-'  + jsonData[nr-1].cui);
	request.post(
    'https://webservicesp.anaf.ro/PlatitorTvaRest/api/v3/ws/tva',
    { json: jsonData },
    function (error, response, body) {
		finish=true;
        if (!error && response.statusCode == 200) {
            console.log(body);
			fs.writeFile(`text${start}_${nr}.json`, JSON.stringify( body ), function (err) {
            if (err) {
                return console.log(err);
            }

            console.log("The file was saved!" + `text${start}_${nr}.json`);
        });

        }
    });
	start = start+nr;
	while(!finish){
		console.log('waiting');
		await d(13000);
	}
	

}
console.log('finish')
return;

}
start();

Tiny Types–documentation–part 4

This is a series

  1. http://msprogrammer.serviciipeweb.ro/2018/03/12/tiny-types-in-cpart-1/
  2. http://msprogrammer.serviciipeweb.ro/2018/03/19/tiny-types-part-2adding-iequatable/
  3. http://msprogrammer.serviciipeweb.ro/2018/03/26/tinytypesadding-equality-operatorpart-3/

  4. http://msprogrammer.serviciipeweb.ro/2018/04/02/tiny-typesdocumentationpart-4/

tiny types in C#

Build Status

Tiny types is a NuGET dll , https://www.nuget.org/packages/TinyTypesObjects/

Also , you can find the construction here: http://msprogrammer.serviciipeweb.ro/category/tinytypes/

The documentation is copied shameless from https://github.com/jan-molak/tiny-types

Installation

To install the module from nuget : … Install-Package TinyTypesObjects …

Defining Tiny Types

An int on its own is just a scalar with no meaning. With an object, even a small one, you are giving both the compiler and the programmer additional information about what the value is and why it is being used.

Jeff Bay, Object Calisthenics

Single-value types

To define a single-value TinyType – extend from TinyType<T>() :

If you want operator ==, please use TinyTypeOf or TinyTypeOfString

using TinyTypesObjects;

public class Age : TinyTypeOf<int>
    {
        public Age(int nr) : base(nr)
        {

        }
    }
public class FirstName : TinyTypeOfString
    {
        public FirstName(string str) : base(str)
        {

        }
    }

Every tiny type defined this way has a get property value of type T, which you can use to access the wrapped primitive value. For example:

var firstName = new FirstName("Jan");

Assert.AreEqual(firstName.value , "Jan");
        
Converting from / to original values

There are defined conversions between type T and the class

public void TestConvert()
        {
            string s = "http://msprogrammer.serviciipeweb.ro";
            TinyTypeOfString tt = s;
            Assert.AreEqual(s, (string)tt);

            int nr = 7;
            TinyTypeOf<int> tt1 = nr;

            Assert.AreEqual(nr, (int)tt1);
        }
        

so the following code should work for the class with constructor string

class TestConstructor
    {
        public TestConstructor(string firstName)
        {
            FirstName = firstName;
        }

        public string FirstName { get; }
    }
[TestMethod]
        public void TestConstructor()
        {
            var firstName = new FirstName("Jan");
            TestConstructor tc = new TestConstructor(firstName);
            Assert.AreEqual(tc.FirstName, "Jan");
        }
Equals or ==

Each tiny type object has an equals method, which you can use to compare it by value:

int nr = 7;
            TinyTypeOf<int> tt1 = nr;
            TinyTypeOf<int> tt2 = nr;

            Assert.AreEqual(tt1, tt2);
            Assert.IsTrue(tt1 == tt2);
Links:

GitHub: https://github.com/ignatandrei/tinyTypes

Blog About: http://msprogrammer.serviciipeweb.ro/category/tinytypes/

NuGet: https://www.nuget.org/packages/TinyTypesObjects/

TinyTypes–adding equality operator–part 3

This is a series

  1. http://msprogrammer.serviciipeweb.ro/2018/03/12/tiny-types-in-cpart-1/
  2. http://msprogrammer.serviciipeweb.ro/2018/03/19/tiny-types-part-2adding-iequatable/
  3. http://msprogrammer.serviciipeweb.ro/2018/03/26/tinytypesadding-equality-operatorpart-3/

  4. http://msprogrammer.serviciipeweb.ro/2018/04/02/tiny-typesdocumentationpart-4/

Now we have come to the interesting part – the equality operator.

We have already operator equals, but not ==

 [TestMethod]
        public void TestSimpleIntOperatorEqual()
        {
            int nr = 7;
            TinyType<int> tt1 = nr;
            TinyType<int> tt2 = nr;

            Assert.AreEqual(tt1, tt2);
            Assert.IsFalse(tt1 == tt2);
        }
        [TestMethod]
        public void TestSimpleStringOperatorEqual()
        {
            string s = "http://msprogrammer.serviciipeweb.ro";
            TinyType<string> tt1 = s;
            TinyType<string> tt2 = s;

            Assert.AreEqual(tt1, tt2);
            Assert.IsFalse(tt1 == tt2);
        }

Because we primary want this TinyTypes for structs( int, double, bool, and so on) we can define a new class

For this we could implement operator  ==  by using the Equals operator ( because we KNOW that a struct cannot be null)

public class TinyTypeOf<T> : TinyType<T>
        where T:struct
    {
        public TinyTypeOf(T tDef) : base(tDef)
        {
        }

       
        public static bool operator ==(TinyTypeOf<T> lhs, TinyTypeOf<T> rhs)
        {
            if(lhs is null)
            {
                return rhs is null;
            }
            return lhs.t.Equals(rhs.t);
        }

        public static bool operator !=(TinyTypeOf<T> lhs, TinyTypeOf<T> rhs)
        {
            return !(lhs==rhs);
        }

CODE FOR NEW CLASS

Also, because the string is not a struct, but a class, we need for String also:

public class TinyTypeOfString : TinyType<string>

And the tests are

using Microsoft.VisualStudio.TestTools.UnitTesting;
using TinyTypesObjects;

namespace TinyTypesTest
{
    [TestClass]
    public class TestTinyTypeOperatorEqual
    {
        [TestMethod]
        public void TestSimpleIntOperatorEqual()
        {
            int nr = 7;
            TinyType<int> tt1 = nr;
            TinyType<int> tt2 = nr;

            Assert.AreEqual(tt1, tt2);
            Assert.IsFalse(tt1 == tt2);
        }
        [TestMethod]
        public void TestSimpleStringOperatorEqual()
        {
            string s = "http://msprogrammer.serviciipeweb.ro";
            TinyType<string> tt1 = s;
            TinyType<string> tt2 = s;

            Assert.AreEqual(tt1, tt2);
            Assert.IsFalse(tt1 == tt2);
        }
        [TestMethod]
        public void TestSimpleStringOperatorEqualWorks()
        {
            string s = "http://msprogrammer.serviciipeweb.ro";

            TinyTypeOfString tt1 = s;
            TinyTypeOfString tt2 = s;

            Assert.AreEqual(tt1, tt2);
            Assert.IsTrue(tt1 == tt2);
        }

        [TestMethod]
        public void TestSimpleIntOperatorEqualWorks()
        {
            int nr = 7;
            TinyTypeOf<int> tt1 = nr;
            TinyTypeOf<int> tt2 = nr;

            Assert.AreEqual(tt1, tt2);
            Assert.IsTrue(tt1 == tt2);
        }
        [TestMethod]
        public void TestSimpleIntNrOperatorEqualWorks()
        {
            int nr = 7;
            TinyType<int> tt1 = nr;
            
            Assert.AreEqual(tt1, nr);
            Assert.IsTrue(tt1 == nr);
        }
    }
}

Tiny Types part 2–adding IEquatable

This is a series

  1. http://msprogrammer.serviciipeweb.ro/2018/03/12/tiny-types-in-cpart-1/
  2. http://msprogrammer.serviciipeweb.ro/2018/03/19/tiny-types-part-2adding-iequatable/
  3. http://msprogrammer.serviciipeweb.ro/2018/03/26/tinytypesadding-equality-operatorpart-3/

  4. http://msprogrammer.serviciipeweb.ro/2018/04/02/tiny-typesdocumentationpart-4/

As always, the bigger problem is adding equality. The Tiny Type should be equal with the inner value – and with the other type with the same value. And, in C#, when you implement equality , there is a whole theory – see https://msdn.microsoft.com/en-us/library/336aedhh(v=vs.100).aspx .

So the code to define equality is 60 lines long just for defining equality for

public class TinyType<T>:IEquatable<T>, IEquatable<TinyType<T>>

   

But this is not all. This is the code for testing equality

using Microsoft.VisualStudio.TestTools.UnitTesting;
using TinyTypesObjects;

namespace TinyTypesTest
{
    [TestClass]
    public class TestTinyTypeEquals
    {
        [TestMethod]
        public void TestSimpleStringEquals()
        {
            #region arrange
            string s = "http://msprogrammer.serviciipeweb.ro";
            #endregion
            #region act
            TinyType<string> tt1 = s;
            TinyType<string> tt2 = s;
            #endregion
            #region assert
            Assert.IsTrue(tt1.Equals(tt2));
            Assert.AreEqual(tt1, tt2);
            Assert.AreEqual<string>(tt1, tt2);
            Assert.AreEqual<TinyType<string>>(tt1, tt2);
            Assert.AreEqual<string>(s, tt2);
            #endregion
        }
        [TestMethod]
        public void TestSimpleStringWithNull()
        {
            #region arrange
            string s = null;
            #endregion
            #region act
            TinyType<string> tt1 = s;
            TinyType<string> tt2 = null;
            #endregion
            #region assert
            Assert.IsFalse(tt1.Equals(tt2));
            Assert.AreNotEqual(tt1, tt2);
            Assert.AreEqual<string>(tt1, tt2);
            Assert.AreNotEqual<TinyType<string>>(tt1, tt2);
            Assert.AreEqual<string>(s, tt2);
            #endregion
        }
        [TestMethod]
        public void TestSimpleStringNull()
        {
            #region arrange
            string s = null;
            #endregion
            #region act
            TinyType<string> tt1 = s;
            TinyType<string> tt2 = s;
            #endregion
            #region assert
            Assert.IsTrue(tt1.Equals(tt2));
            Assert.AreEqual(tt1, tt2);
            Assert.AreEqual<string>(tt1, tt2);
            Assert.AreEqual<TinyType<string>>(tt1, tt2);
            Assert.AreEqual<string>(s, tt2);
            #endregion
        }
        [TestMethod]
        public void TestSimpleIntEquals()
        {
            #region arrange
            int s = 1;
            #endregion
            #region act
            TinyType<int> tt1 = s;
            TinyType<int> tt2 = s;
            #endregion
            #region assert
            Assert.IsTrue(tt1.Equals(tt2));
            Assert.AreEqual(tt1, tt2);
            Assert.AreEqual<int>(tt1, tt2);
            Assert.AreEqual<TinyType<int>>(tt1, tt2);
            Assert.AreEqual<int>(s, tt2);
            
            #endregion
        }

    }
}