How to import a text file into a database

How to import a text file into a database

• Tryin2Bgood

  Post what you have and let us have a look
  
  

• ks2006

  hope following code helps you! I am reading from a CSV file (this may be a text file too) & pushing the same data into a Ms Access Table

  Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
  Dim connstringa As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DataBase\yourdata.mdb;Persist Security Info=True"
  Dim cnAccess As OleDbConnection = New System.Data.OleDb.OleDbConnection(connstringa)
  Dim strString As String = "SELECT name, description FROM status"
  Dim selectCMD As OleDbCommand = New System.Data.OleDb.OleDbCommand(strString, cnAccess)
  Dim adp As New OleDbDataAdapter(strString, connstringa)
  Dim cmdBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(adp)

  Dim dtRecords1 As DataTable
  Dim oRow2 As DataRow

  
  Try
  dtRecords1 = GetDataTable2("SELECT name, description FROM members22.csv")
  DataGridView1.DataSource = dtRecords1

  For Each oRow2 In ds1.Tables(0).Rows
  oRow2.SetAdded()
  Next

  Dim dtItemsTable As Test.statusDataTable
  Dim oRowItems As Test.statusRow
  Dim ItemsDataSet As Test
  Dim oRow1 As DataRow

  ItemsDataSet = New Test
  dtItemsTable = ItemsDataSet.Tables(ItemsDataSet.status.TableName)

  For Each oRow1 In ds1.Tables(0).Rows
  oRowItems = dtItemsTable.NewRow
  With oRowItems
  '.id = oRow1.Item(0)
  .name = oRow1.Item(0)
  .description = oRow1.Item(1)
  End With
  dtItemsTable.Rows.Add(oRowItems)
  Next

  DataGridView2.DataSource = ItemsDataSet.Tables(0)

  
  'adp.Fill(StausDataSet)
  adp.Update(ItemsDataSet.Tables(0))
  Catch ex As Exception
  MsgBox(ex.Message)
  End Try
  End Sub

  Private Function GetDataTable2(ByVal sql As String)
  Dim rt As DataTable
  Dim da1 As OdbcDataAdapter
  Dim con As OdbcConnection
  Dim cmd As OdbcCommand
  Try
  rt = New DataTable()
  ds1 = New DataSet()
  da1 = New OdbcDataAdapter()
  con = New OdbcConnection(GetConnection())
  cmd = New OdbcCommand(sql, con)
  da1.SelectCommand = cmd
  da1.Fill(ds1)
  rt = ds1.Tables(0)
  Label1.Text = "Records in the CSV:" + CStr(ds1.Tables(0).Rows.Count)
  Catch ex As Exception
  rt = Nothing
  MsgBox(ex.Message)
  End Try
  Return rt
  End Function

  this what i am doing one of the project: the code is in C#

  using System;
  using System.Collections.Generic;
  using System.ComponentModel;
  using System.Data;
  using System.Data.Odbc;
  using System.Drawing;
  using System.Text;
  using System.Windows.Forms;

  namespace WindowsApplication5
  {
  public partial class Form1 : Form
  {
  public Form1()
  {
  InitializeComponent();
  }

  private void button1_Click(object sender, EventArgs e)
  {

  DataTable dtRecords = GetDataTable("SELECT FirstName, LastName FROM members2.csv ");
  dataGridView1.DataSource = dtRecords;
  foreach(DataRow dr in dtRecords.Rows) {
  MessageBox.Show(dr["FirstName"].ToString() + " " + dr["LastName"].ToString() );
  }

  
  }

  private static string GetConnection() {
  return @"Driver={Microsoft Text Driver (*.txt; *.csv)};HDR=YES;Extensions=asc,csv,tab,txt;Dbq=C:\Temp";
  }

  public static DataTable GetDataTable(string sql) {
  DataTable rt = new DataTable();
  DataSet ds = new DataSet();
  OdbcDataAdapter da = new OdbcDataAdapter();
  OdbcConnection con = new OdbcConnection(GetConnection());
  OdbcCommand cmd = new OdbcCommand(sql, con);
  da.SelectCommand = cmd;
  da.Fill(ds);
  try {
  rt = ds.Tables[0];
  }
  catch {
  rt = null;
  }
  return rt;
  }

  public static void DoCommand(string sql) {
  OdbcConnection con = new OdbcConnection(GetConnection());
  OdbcCommand cmd = new OdbcCommand();
  cmd.CommandText = sql;
  cmd.Connection = con;
  con.Open();
  try {
  cmd.ExecuteNonQuery();
  }
  catch (Exception e){
  //HandleError(e,sql);
  }
  finally {
  con.Close();
  }
  }
  }
  }

  best regards,

  Ammar.

  
  

• Ganeshm

  This is what I have thus far. Do I need to use an array

  Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

  Dim SQLString As String = "Select * from rdstype27"

  'Connect to Firebird DB

  Dim cn As New FirebirdSql.Data.FirebirdClient.FbConnection("User=DBADMIN;Password=admin;Database=E:\Firebird\RDS.FDB;DataSource=fs000xveuc90;Port=3050;Dialect=3;Charset=NONE;Role=user;Connection lifetime=0;Connection timeout=15;Pooling=True;Packet Size=8192;Server Type=0")

  Dim dt As New DataTable

  cn.Open()

  Dim da As New FirebirdSql.Data.FirebirdClient.FbDataAdapter(SQLString, cn)

  'Dim s As String

  'Dim tr As IO.TextReader = New IO.StreamReader("e:\firebird\actblrds027rl.txt")

  'For i As Integer = 0 To tr.ReadToEnd - 1

  ' s = tr.ReadLine

  ' Dim rw As DataRow = dt.NewRow()

  ' rw("division") = s.Substring(0, 2)

  'Next i

  Using parser As New FileIO.TextFieldParser("e:\firebird\actblrds027rl.txt")

  parser.SetDelimiters(",")

  While Not parser.EndOfData

  Me.DataGridView1.Rows.Add(parser.ReadLine.Substring(0, 2))

  End While

  
  
  

• GTH

  Great. Thank you.
  
  
• How to import a text file into a database