CODEWITHSUNDEEP
c#winformsms-accessodbc
Gold
Gold

Reputation: 62484

INSERT INTO Access table from CSV file via C# code

i have this table:

Items
====
Barcode (text)
Des (text)
Price (double)

i made csv file on d:\Items.csv for fast insert (12345,computer,120.44).....

i try to insert like this (C# WinForm program):

Cmd = Conn.CreateCommand();
SQL = @"INSERT INTO Items SELECT * FROM [Text;DATABASE=" + @"d:" + @"\].[Items.txt];";
Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();

but i got 2 error:

1. Data type mismatch in criteria expression.

2. The field 'Items.Barcode ' cannot contain a Null value because the Required property for this field is set to True.  Enter a value in this field.

how to fix this ?

Upvotes: 1

Views: 4040

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123689

The following C# code works for me. It imports C:\Users\Public\Items.csv into a table named [Items] in my Access 2010 database C:\Users\Public\Database1.accdb. The contents of the CSV file is simply

12345,computer,120.44

The code is

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Odbc;

namespace myDbTest
{
    class Program
    {
        static void Main(string[] args)
        {
            string myConnectionString;
            myConnectionString =
                    @"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" +
                    @"Dbq=C:\Users\Public\Database1.accdb;";

            using (var con = new OdbcConnection())
            {
                con.ConnectionString = myConnectionString;
                con.Open();

                using (var cmd = new OdbcCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.CommandText =
                            @"INSERT INTO Items " +
                            @"SELECT * FROM [Text;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=437;ACCDB=YES;Database=C:\Users\Public].[Items#csv];";
                    cmd.ExecuteNonQuery();
                }
                con.Close();
            }
        }
    }
}

Upvotes: 3

Related Questions