CODEWITHSUNDEEP
c#sqlexcel
Marc Howard
Marc Howard

Reputation: 425

create sql tables from excel columns

h_header        data type                           cp_retirementPlanningAnalysis        data type
versionId       int                                 signifShortFall                      varchar
serviceId       varchar(10)                         isProjIncAdeq                        boolean
creationTime    date                                howCloseRetire                       varchar
brandCode       varchar                             clientsView                          varchar

My excel file above has a header which i want to be the table name, then the column names below with the data type next to it. Is there any easy way i can generate the tables from each column, plus the properties with datatypes? I have around 70 tables that need to be created.

Upvotes: 0

Views: 163

Answers (1)

grovesNL
grovesNL

Reputation: 6075

Rather than reading directly from Excel, you should be able to easily reformat your Excel tables to two columns instead of four. Then you could save this as a CSV file and manually build the query in some way similar to the following:

using System;
using System.IO;

namespace sample
{
    class Program
    {
        static void Main(string[] args)
        {
            // Read all lines from csv file describing single table
            string query = "CREATE TABLE ";
            using (StreamReader sr = new StreamReader(File.OpenRead(@"C:\test\test.csv"))){
                // Skip the first line
                sr.ReadLine();
                // Get the header from the second line
                query += sr.ReadLine().Split(',')[0] + '(';
                while (!sr.EndOfStream)
                {
                    // Add parameters to query
                    string[] line = sr.ReadLine().Split(',');
                    query += line[0] + ' ' + line[1] + ',';
                }
                query = query.TrimEnd(',');
                query += ");";
                Console.WriteLine(query);
            }
        }
    }
}

This is just an example of an easy way to do it without creating a connection to Excel. However, you could also connect this directly to your Excel file to loop through each sheet, or use Linq to loop through this, etc.

With a CSV file of the following text:

h_header,data type
cp_retirementPlanningAnalysis,data type
versionId,int
signifShortFall,varchar
serviceId,varchar(10)
isProjIncAdeq,boolean
creationTime,date
howCloseRetire,varchar
brandCode,varchar
clientsView,varchar

The query created is:

CREATE TABLE cp_retirementPlanningAnalysis(versionId int,signifShortFall varchar,serviceId varchar(10),isProjIncAdeq boolean,creationTime date,howCloseRetire varchar,brandCode varchar,clientsView varchar);

Upvotes: 1

Related Questions