Create SQL Table Schema based on data from flat files
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.