Can anyone advice on a simple way to create SQL tables on the fly, using a CSV file upload in a .NET Web App?

Question

Hi, I have checked a few answers but was unable to find specific details on how we can create a SQL table using custom metadata (or Columns from a CSV file) which is uploaded by a user through a CSV file. A feature available with www.dabbledb.com, like creating online database for custom information. It will be a web based .NET application using SQL Server 2005 or 2008 as the backend db.

Look forward to hearing from you.

Thank you SA

Answer 1

If you are using an entity-attribute-value database then you can just add the new columns in the master attribute table, linked to the entity, then just assign values in the attribute-value table for the entity.

But, if that doesn't make sense then you will want to use the ALTER TABLE command and add new columns, but if you are going to be dynamically changing the table structure then you may want to consider an EAV database structure.

Answer 2

I recently worked on something quite close to your requirements. In my case, the data from each row in a CSV file that is uploaded in to the server is stored as a 'blob'. I have used the Lumen Works CSV reader to parse the uploaded CSV file. It has the ability to read the column headers, which you can strip of whitespaces and may to SQL database table columns.

Hope this helps indyfromoz

Answer 3

We just wrote a perl program that reads the csv, analyzes the data in each column, tries at first to believe that the first line is column names, then cobbles up a create table statement and a bunch 0f inserts. We can run it from a command environment or a system call inside PHP.

Answer 4

This is just an example how I would do this (which means it possibly is bug ridden and wrong...)

CSV File:

Column:ColumnType:size:n, Column:Type:size:n, Column:Type:size:y
C1Data, C2Data, C3Data

Pseudo Code:

StringBuilder sql = new StringBuilder();
sb.append("CREATE TABLE " + someTableName+ "(" + Environment.NewLine);
for(string s in csvlines) {
 if(first_line) {

    // pull apart our columns
    string[] cols = s.Split(new char[] { ',' });

    // iterate over the list of columns
    for(string col in cols) {

      // pull apart the column name, type, size and nullable flag
      string[] ci = col.Split(new char[] { ':' });

      // add our column name and add the datatype
      sb.Append("[" + ci[0] + "]" + " [" + ci[1] + "] ");

      // only put a size if value greater than 0
      if(ci[2] != "0") {
        sb.Append("(" + ci[2] + ") ");
      }

      // put our NULLABLE flag on NULL columns only
      if(ci[3] == "y") {
        sb.Append("NULL," + Environment.NewLine);
      } else {
        sb.Append("NOT NULL," + Environment.NewLine);
      }
    }

    // remember the above has a bug where the last column has a rouge ,
    sb.Append(")");
 } else  {
     // create our insert statement and insert as appropriate
 }
}

May be disgusting, maybe dirty, may just do what you require!