Hello,

I was wondering if somebody could help guide me.

I have produced a module in Access 2010 that imports a specific .txt file from a specific directory.

This file is just full of text, no commas to indicate columns or column names.

I have done it so that I am splitting the text up in to different fields, by stating the database table field name, and the values from the text file that will be in them columns.

I have so far got to the point, where the text file is being opened and so is the table, I can see the data that is going in to the table.

The module finishes, I go in to the table, and there are 6 empty rows (the .txt file contains 6 rows of text).

My code is as follows:

Sub ImportTextFile()
Dim LineData As String

Dim Field1 As String
Dim Field2 As String
Dim Field3 As String
Dim Field4 As String
Dim Field5 As String
Dim Field6 As String
Dim Field7 As String
Dim Field8 As String
Dim Field9 As String
Dim Field10 As String
Dim Field11 As String
Dim Field12 As String
Dim Field13 As String
Dim Field14 As String
Dim Field15 As String
Dim Field16 As String
Dim Field17 As String
Dim Field18 As String
Set cncurrent = CurrentProject.Connection
Set rsDiag = New ADODB.Recordset
' Open the text file
Open "T:\File.txt" For Input As #1
MsgBox "File is open..."
' Open the table to insert the text file into
strsql = "Select * from tbltestTable"

MsgBox "Fields: " + strsql

rsDiag.Open strsql, cncurrent, adOpenDynamic, adLockOptimistic
MsgBox "Database table is open..."
Do While Not EOF(1)
' Read a line of data.
Line Input #1, LineData
rsDiag.AddNew
Field1 = Left(LineData, 4)
Field2 = Mid(LineData, 5, 12)
Field3 = Mid(LineData, 17, 9)
Field4 = Mid(LineData, 26, 7)
Field5 = Mid(LineData, 33, 4)
Field6 = Mid(LineData, 37, 20)
Field7 = Mid(LineData, 57, 2)
Field8 = Mid(LineData, 59, 7)
Field9 = Mid(LineData, 66, 11)
Field10 = Mid(LineData, 77, 2)
Field11 = Mid(LineData, 79, 12)
Field12 = Mid(LineData, 91, 4)
Field13 = Mid(LineData, 95, 4)
Field14 = Mid(LineData, 99, 30)
Field15 = Mid(LineData, 129, 2)
Field16 = Mid(LineData, 131, 3)
Field17 = Mid(LineData, 134, 2)
Field18 = Mid(LineData, 136, 2)

MsgBox "Data: " + LineData

rsDiag.MoveNext
'rsDiag!ICDraw = ICDraw
'rsDiag!Description = ICDDesc
rsDiag.Update

MsgBox "Import Complete..."

Loop
' Close the data file.
Close #1
rsDiag.Close
End Sub

Could anyone advise me on what I might be doing wrong or what is actually happening and why I am getting blank rows?

Many thanks,

Dan

if its an Access question then its best asked in the Access forum

bear in mind VB is not VBA is not VBS

at what point do you think you are assining values to the columns in the recordset?
I can see you are assiging the value from the input to working varaibles, but not where you then assign those values to the recordset rsdiag

assuming that field1..field18 are actually the names of your columns (and shame on you if they are, column names should ideally describe the data they hold) you need to prefix them with the name of the recordset object and a fullstop (or for those speaking English as a foreign language across t'pond:- a period)

eg
generally speaking its a good idea to validate the data before inserting, its also a good idea to put in some form of error trapping.. look up VBA error trapping

Hi,

Thanks for your reply.

No, my columns are called different, but for obvious reasons, I kept them as Field1, Field2 etc.

I got it working, but needed to do rsDiag.Fields("Field1") = mid(LineData, 4, 19) etc.

Thank you for your help.

Dan