Import Excel data to DataBase dynamically

Question

how you can probably see by the title, I want to know a way to import data from excel to a database, even though I read a lot of questions and answers about this, I coundn't find one that would solve my problem. So my excel sheet is every minutes refreshing its numbers (by getting new values from the internet and overwriting the same cell), so the excel needs to be always opened. I want to read these values from visual studio, get these values and write it on my database. I already got that working using OleDb and writing it on PostgreSql, but it only works when my excel is closed (i think that is like that because OleDb opens excel to read it, and sice it is already opened, it doesnt work). I really appreciate anyone that could help me... Thanks!

Answer 1

...I want to read these values from visual studio...

Why not VBA to read these values and write to Postgres? You can run VBA macro in Excel Workbook. For example:

Sub Cell2Postgres()
Dim Connection As New ADODB.Connection
Dim Command As New ADODB.Command

Connection.ConnectionString = "Driver=PostgreSQL Unicode;Server=localhost;Port=5432;Database=postgres;Uid=postgres;Pwd=postgres"
Connection.Open

Command.ActiveConnection = Connection

Command.CommandText = "INSERT INTO public.mytable (myfield) VALUES (?)"
Command.Parameters.Append Command.CreateParameter("", adVarChar, adParamInput, 255, Range("A1").Value)
Command.Execute


Connection.Close

End Sub

Answer 2

This is some code I wrote for an add-in that does exactly that -- it takes a highlighted range in Excel and uploads it to a table (using C#, VSTO in this case).

This code went through many, MANY iterations and is finally something we are quite happy with. It is extremely fast (faster than any version we tried previously, and faster than PgAdmin's import) and is very forgiving with datatypes -- you don't even need to know the datatype of the target table, provided you format it in a way that PostgresSQL's copy command can load it.

In a nutshell, it takes the range, does a copy-paste special values to a new sheet, saves the sheet as a CSV (Fast, uses native Excel functionality), compresses the CSV file, FTPs the file to the PostgreSQL server and then runs the copy command.

CAVEAT: Because this does copy, the user that actually runs the command has to be a superuser.

var addIn = Globals.ThisAddIn;
Excel.Range range = addIn.Application.Selection;

Excel.Workbook wb = addIn.Application.Workbooks.Add();
Excel.Worksheet ws = wb.Worksheets[1];

range.Copy();
ws.get_Range("A1").PasteSpecial(Excel.XlPasteType.xlPasteValuesAndNumberFormats);
addIn.Application.DisplayAlerts = false;
wb.SaveAs(Path.Combine(_Outputdir, string.Format("{0}.csv", TableName)),
    Excel.XlFileFormat.xlCSV);
wb.Close();
addIn.Application.DisplayAlerts = true;

string newFile = Commons.Compress(_Outputdir, string.Format("{0}.csv", TableName));

This is a custom FTP routine we wrote. I could not get the .NET Class Library to work. You can do whatever you want to get it to the server:

Commons.FtpPut(newFile, _Outputdir);

Now, load the data:

NpgsqlTransaction trans = conn.BeginTransaction(IsolationLevel.RepeatableRead);

if (TruncateTable)
{
    cmd = new NpgsqlCommand(string.Format("truncate table {0}", TableName), conn, trans);
    cmd.ExecuteNonQuery();
}

try
{
    Stopwatch st = new Stopwatch();
    st.Start();

    string format = HasHeader ? "csv header" : "csv";

    cmd.CommandText = string.Format(
        "copy {0} from program 'gzip -dc /apps/external_data/inbound/{0}.csv.gz' " +
        "with null as '' {1} encoding 'WIN1250'", TableName, format);

    cmd.ExecuteNonQuery();

    trans.Commit();

    st.Stop();

    Results = string.Format("Upload Completed in {0}", st.Elapsed);
}
catch (Exception ex)
{
    trans.Rollback();
    Results = ex.ToString();
    success = false;
}

Again, hand-roll your own FTP cleanup process:

Commons.FtpDelete(newFile, _Outputdir);

Upstream of this, we do checks to be sure the user has permissions to truncate and/or load the table.

One final note -- this code is not notional. It runs in production, and users do dozens of table uploads each day.