Question importing Text delimited file to access DB file

[aya.akram]

Hi
I've got a problem in Importing Text delimited File to access Db , I always get an error "No value given for one or more required parameters'
while execution [Select Into] Statement

Dim svdialog As New SaveFileDialog
svdialog.Filter = "Access Database Files (*.mdb)/*.mdb"
If svdialog.ShowDialog = Windows.Forms.DialogResult.Cancel Then Exit Function

svdialog.Title = "Save Track DB"
CreateAccessDatabase(svdialog.FileName)

Dim ds As New DataSet
Dim da As New OleDbDataAdapter
'Define the connectors
Dim oConn As OleDbConnection
Dim oComm As OleDbCommand
Dim oConnect, oQuery As String

'Define connection string
oConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & svdialog.FileName

'Define the query string the creates the table
oQuery = "CREATE TABLE TABLE1 (ID Autoincrement, " & _
"[Date] DATETIME," & _
"[Time] DATETIME," & _
"[Valid_position] TEXT(100) NOT NULL," & _
"[Lat] NUMBER NOT NULL," & _
"[Long] NUMBER NOT NULL," & _
"[Speed] NUMBER NOT NULL," & _
"CONSTRAINT ID PRIMARY KEY(ID) )"
'' Instantiate the connectors
oConn = New OleDbConnection(oConnect)
oComm = New OleDbCommand(oQuery, oConn)

'Open the connection
oConn.Open()
' 'Perform the Non-Query
oComm.ExecuteNonQuery()
' 'Close the connection
oConn.Close()

Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & svdialog.FileName) '& "Extended Properties='text;FMT=TabDelimited;HDR=YES'")

Dim myFile() As String = System.IO.File.ReadAllText("D:\Tracks\Text.txt").Split(","c)
'For Each line As String In myFile

Dim cmd As New OleDbCommand("Insert INTO TABLE1 ([Date],[Time],[Valid_position],[Lat],[Long],[Speed])" & _
" SELECT F1 as [Date],F2 as [Time],F3 as [Valid_position],F4 as [Lat],F5 as [Long],F6 as [Speed]" & _
" FROM [Text;Database=c:\;FMT=Delimited].[Text.txt];", conn)

conn.Open()
cmd.ExecuteNonQuery()
conn.Close()​

here's the text file is attatched

 

[MattP]

Your file has a header record so Ole will see the field names rather than F1, F2 etc.

HDR=Yes is the default so you can omit that.

Comma separated values are the default so you can omit the delimited extended property as well.

I would recommend StringBuilder & String.Format as alternatives to concatenating strings together. (Examples of both below)

Imports System.Data.OleDb
Imports System.IO

Public Class Form1

	Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
		Const dbPath As String = "C:\Temp\Database1.mdb"

		Using cn As New OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", dbPath))
			cn.Open()
			Dim createQuery As String = GetCreateTableString("Table1")
			Dim cmd As New OleDbCommand(createQuery, cn)
			cmd.ExecuteNonQuery()
			Dim insertQuery As String = GetInsertTableString(dbPath, "Table1", "C:\Temp\Text.txt")
			cmd.CommandText = insertQuery
			cmd.ExecuteNonQuery()
		End Using
	End Sub

	Private Function GetCreateTableString(ByVal tableName As String) As String
		Dim builder As New System.Text.StringBuilder
		With builder
			.Append(String.Format("CREATE TABLE {0} (", tableName))
			.Append("ID Autoincrement, ")
			.Append("[Date] DATETIME, ")
			.Append("[Time] DATETIME, ")
			.Append("[Valid_position] TEXT(100), ")
			.Append("[Lat] NUMBER NOT NULL, ")
			.Append("[Long] NUMBER NOT NULL, ")
			.Append("[Speed] NUMBER NOT NULL, ")
			.Append("CONSTRAINT ID PRIMARY KEY(ID))")
			Return builder.ToString
		End With
	End Function

	Private Function GetInsertTableString(ByVal databasePath As String, ByVal tableName As String, ByVal inputFile As String) As String
		Dim fi As New FileInfo(inputFile)
		Dim builder As New System.Text.StringBuilder
		With builder
			.Append(String.Format("INSERT INTO {0} ([Date], [Time], [Valid_position], [Lat], [Long], [Speed]) ", tableName))
			.Append(String.Format("SELECT * FROM [Text;DATABASE={0};].[{1}]", fi.Directory, fi.Name))
			Return builder.ToString
		End With
	End Function

End Class