Create SQL Table Schema based on data from flat files

Article
10/31/2013

Hello All,

Today we ll see how to create a SQL table schema based on flat files(excel, csv, txt formats).

Steps:

1. Create a datatable and load data into datatable from a flat file.

2. Read each coloumn from datatable and set its data type, length and other configurable properties.

3. Convert C# data types to SQL DB data types.

4. Create a table in a Database (This part is out of scope) of this post.

We will do this step by step.

Step 1: Create a datatable and load data into datatable from a flat file

DataSet ds = new DataSet();
string strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)}; Dbq="+filePath+"; Extensions=asc,csv,tab,txt;Persist Security Info=False";
string sql_select;
OdbcConnection conn;
conn = new OdbcConnection(strConnString.Trim());
conn.Open();

//Creates the select command text

sql_select = "select * from ["+filename+"]";

//Creates the data adapter
OdbcDataAdapter obj_oledb_da = new OdbcDataAdapter(sql_select, conn);

//Fills dataset with the records from CSV file
obj_oledb_da.Fill(ds, "csv");

return ds.Tables[0];

Step 2 & 3: Create SQL table Schema for the above datatable

public static string CreateSqlTableFromDataTable(string tableName, DataTable table)
{
string sql = "CREATE TABLE [" + tableName + "] (\n";
// columns
foreach (DataColumn column in table.Columns)
{
sql += "[" + column.ColumnName + "] " + SQLGetType(column) + ",\n";
}
sql = sql.TrimEnd(new char[] { ',', '\n' }) + "\n";
// primary keys
if (table.PrimaryKey.Length > 0)
{
sql += "CONSTRAINT [PK_" + tableName + "] PRIMARY KEY CLUSTERED (";
foreach (DataColumn column in table.PrimaryKey)
{
sql += "[" + column.ColumnName + "],";
}
sql = sql.TrimEnd(new char[] { ',' }) + "))\n";
}
else
sql += ")";
return sql;
}

-------------------------------------------------------------------------------

public static string SQLGetType(DataColumn column)
{
return GetSqlType(column.DataType, column.MaxLength, 10, 2);
}

------------------------------------------------------------------------------

public static string GetSqlType(object type, int columnSize, int numericPrecision, int numericScale)
        {
            switch (type.ToString())
            {
                case "System.Byte[]":
                    return "VARBINARY(MAX)";

case "System.Boolean":
return "BIT";

case "System.DateTime":
return "DATETIME";

case "System.DateTimeOffset":
return "DATETIMEOFFSET";

                case "System.Decimal":
                    if (numericPrecision != -1 && numericScale != -1)
                        return "DECIMAL(" + numericPrecision + "," + numericScale + ")";
                    else
                        return "DECIMAL";

case "System.Double":
return "FLOAT";

case "System.Single":
return "REAL";

case "System.Int64":
return "BIGINT";

case "System.Int32":
return "INT";

case "System.Int16":
return "SMALLINT";

case "System.String":
return "NVARCHAR(" + ((columnSize == -1 || columnSize > 8000) ? "MAX" : columnSize.ToString()) + ")";

case "System.Byte":
return "TINYINT";

case "System.Guid":
return "UNIQUEIDENTIFIER";

                default:
                    throw new Exception(type.ToString() + " not implemented.");
            }
        }

---------------------------------------------------------------------------------------------------------------

string SQL from step2 will return the table schema.

Create SQL Table Schema based on data from flat files

Additional resources