, delimited text file to access db

Put text file data into a vb created access db

Page 1 of 1

9 Replies - 4148 Views - Last Post: 05 June 2009 - 11:24 PM Rate Topic: -----

#1 petersoj  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 17-May 09

, delimited text file to access db

Post icon  Posted 31 May 2009 - 07:03 PM

I need to take a text file with data separated by commas and convert the files into a access 2000 database. I have not been able to find anything close enough to what I need.

Data looks like : 19990101,111,AZ,e

Thanks,
Is This A Good Question/Topic? 0
  • +

Replies To: , delimited text file to access db

#2 vb5prgrmr  Icon User is offline

  • D.I.C Lover

Reputation: 109
  • View blog
  • Posts: 1,016
  • Joined: 21-March 09

Re: , delimited text file to access db

Posted 31 May 2009 - 09:15 PM

In VB's help, on the index tab, look up the following...

Open Statement
FreeFile Function
Line Input
Split Function

Then search this site or look up at the top of this forum if I remember correctly for the tutorual on ADO.

Good Luck
Was This Post Helpful? 0
  • +
  • -

#3 Ghostwolf  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 23
  • Joined: 01-May 09

Re: , delimited text file to access db

Posted 01 June 2009 - 07:46 AM

If you're using VBA inside Access, another option might be to use a schema.ini file to define your incoming data.

The ini file layout is pretty easy:
	 [TMPFILE.TXT]
	 ColNameHeader=FALSE
	 CharacterSet=OEM
	 Format=CSVDelimited
	 Col1=Program Char
	 Col2=RunDate Char
	 Col3=County Char
	 Col4=CountyName Char
	 Col5=ForDate Char



This file says that when TMPFILE.TXT is encountered, there will be no header row in the incoming data, which is CSVDelimited, and it identifies the five columns of incoming data.
Was This Post Helpful? 0
  • +
  • -

#4 petersoj  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 17-May 09

Re: , delimited text file to access db

Posted 03 June 2009 - 06:36 AM

View Postvb5prgrmr, on 31 May, 2009 - 08:15 PM, said:

In VB's help, on the index tab, look up the following...

Open Statement
FreeFile Function
Line Input
Split Function

Then search this site or look up at the top of this forum if I remember correctly for the tutorual on ADO.

Good Luck


I tried the following code, but ended up gettign this error "Multiple-step operation genated errors. Check each status value"

Private Sub Command1_Click()
  Dim conConnection As New ADODB.Connection
  Dim cmdCommand As New ADODB.Command
  Dim rstRecordSet As New ADODB.Recordset
  Dim AccPath As String
  
  AccPath = App.Path & "\AccessFiles\"
  
   conConnection.ConnectionString "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _App.Path & "\" & "database.mdb;Mode=Read|Write"
conConnection.CursorLocation = adUseClient

   conConnection.Open

  With cmdCommand
	.ActiveConnection = conConnection
	.CommandText = "SELECT * FROM tabTestTable;"
	.CommandType = adCmdText
  End With

	With rstRecordSet
	.CursorType = adOpenStatic
	.CursorLocation = adUseClient
	.LockType = adLockOptimistic
	.Open cmdCommand
  End With

  Dim Acc As String
  Dim AccDate
  Dim AccNum
  Dim AccSt
  Dim AccLot
  Open AccPath & "Access.txt" For Input As #1
	Do While Not EOF(1)
		Line Input #1, Acc
		AccDate = Mid$(Acc, 1, 8)
		AccNum = Mid$(Acc, 10, 3)
		AccSt = Mid$(Acc, 14, 2)
		AccLot = Mid$(Acc, 17, 1)
		
		With rstRecordSet
		  .AddNew
		  .Fields(0) = AccDate
		  .Fields(1) = AccNum
		  .Fields(2) = AccSt
		  .Fields(3) = AccLot
		End With
	Loop
	Close #1
  conConnection.Close

  Set conConnection = Nothing
  Set cmdCommand = Nothing
  Set rstRecordSet = Nothing
End Sub


I am stuck and not sure if I am going i the right direction here.

MOD EDIT: Code Tags Added.
Was This Post Helpful? 0
  • +
  • -

#5 vb5prgrmr  Icon User is offline

  • D.I.C Lover

Reputation: 109
  • View blog
  • Posts: 1,016
  • Joined: 21-March 09

Re: , delimited text file to access db

Posted 03 June 2009 - 10:29 PM

Instead of .addnew/.update method. I would suggest a command object with an insert string...
strSQL = "INSERT INTO tableName(field1name,field2name) VALUES ('" & textvalue & "'," & numericvalue & ")"
conn.execute strSQL



However, I just noticed that you do not have an .Update statement in your loop. So add it after you set the field=value part and test.

Good Luck
Was This Post Helpful? 0
  • +
  • -

#6 born2c0de  Icon User is offline

  • printf("I'm a %XR",195936478);
  • member icon

Reputation: 180
  • View blog
  • Posts: 4,667
  • Joined: 26-November 04

Re: , delimited text file to access db

Posted 04 June 2009 - 12:35 AM

Read this tutorial to learn how to deal with file handling in VB6 and once you read it, you'll be able to handle and split text from files as you require.
Was This Post Helpful? 0
  • +
  • -

#7 petersoj  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 17-May 09

Re: , delimited text file to access db

Posted 04 June 2009 - 04:44 AM

View Postvb5prgrmr, on 3 Jun, 2009 - 09:29 PM, said:

Instead of .addnew/.update method. I would suggest a command object with an insert string...
strSQL = "INSERT INTO tableName(field1name,field2name) VALUES ('" & textvalue & "'," & numericvalue & ")"
conn.execute strSQL



However, I just noticed that you do not have an .Update statement in your loop. So add it after you set the field=value part and test.

Good Luck


I assume you wanted me to try the .Update first? ( did not work). I am confused as to where to put the insert string in the code. Do I replace the .AddNew/.Update section? the values part of the Insert string is not working for me. I keep getting a compile errror (Expected expression). So I am clear on this, where you have textvalue and numericvalue that would be my variables:
Dim Acc As String
Dim AccDate As String
Dim AccNum As String
Dim AccSt As String
Dim AccLot As String
Incidently, I forgot to delcare these properly when I first started. See code at the beginning.

Thanks for continuing to help.
Was This Post Helpful? 0
  • +
  • -

#8 vb5prgrmr  Icon User is offline

  • D.I.C Lover

Reputation: 109
  • View blog
  • Posts: 1,016
  • Joined: 21-March 09

Re: , delimited text file to access db

Posted 05 June 2009 - 12:45 AM

>Do I replace the .AddNew/.Update section?

Yes.

strSQL = "INSERT INTO YourTableName(YourFieldName) VALUES ('" & variable & "')"

Where YourFileName is you will need to list the fields of the database seperated by a comma (Field1, Field2) and where variable is "('" & var1 & "," & var2 & "')"

Good Luck
Was This Post Helpful? 0
  • +
  • -

#9 petersoj  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 17-May 09

Re: , delimited text file to access db

Posted 05 June 2009 - 06:29 AM

View Postvb5prgrmr, on 4 Jun, 2009 - 11:45 PM, said:

>Do I replace the .AddNew/.Update section?

Yes.

strSQL = "INSERT INTO YourTableName(YourFieldName) VALUES ('" & variable & "')"

Where YourFileName is you will need to list the fields of the database seperated by a comma (Field1, Field2) and where variable is "('" & var1 & "," & var2 & "')"

Good Luck


Here is what I have at this point:
Private Sub Command1_Click()

Dim conConnection As New ADODB.Connection
Dim cmdCommand As New ADODB.Command

Dim AccPath As String

AccPath = App.Path & "\AccessFiles\"

conConnection.ConnectionString "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\" & "database.mdb;Mode=Read|Write"
conConnection.CursorLocation = adUseClient

conConnection.Open

With cmdCommand
.ActiveConnection = conConnection
.CommandText = "SELECT * FROM tabTestTable;"
.CommandType = adCmdText
End With

Dim Acc As String
Dim AccDate
Dim AccNum
Dim AccSt
Dim AccLot
Open AccPath & "Access.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, Acc
AccDate = Mid$(Acc, 1, 8)
AccNum = Mid$(Acc, 10, 3)
AccSt = Mid$(Acc, 14, 2)
AccLot = Mid$(Acc, 17, 1)
strSQL = "INSERT INTO tabTestTable(Date,LotNum,State,LotType) VALUES ('" & AccDate & "," & AccNum & "," & AccSt & "," & AccLot & "')"
con.Execute strSQL
Loop
Close #1
conConnection.Close

Set conConnection = Nothing
Set cmdCommand = Nothing
End Sub

After I run it I get this error "Invalid use of property" and the code highlights the conConnection.ConnectionString. I did not find anything out on the internet to give me a hint. Anything you see or know could be causing the error?

Thanks,
Was This Post Helpful? 0
  • +
  • -

#10 vb5prgrmr  Icon User is offline

  • D.I.C Lover

Reputation: 109
  • View blog
  • Posts: 1,016
  • Joined: 21-March 09

Re: , delimited text file to access db

Posted 05 June 2009 - 11:24 PM

remove...

Mode=Read|Write

and see...

http://www.connectio...s.com/access#85

Good Luck
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1