importing text file error

[nlh3011]

I am currently import a text file into a access table using a macro which is
working fine apart for 1 little problem. I need the text file to keep its
sequence, row 1 first, row second next etc. Sometimes the sequence is
changed when I run the macro, and sometimes its correct. The file is too big
to open in excel, so I cannot add a numbered key on the file.

Can anyone help me please?

 

[Jeff Boyce]

"row 1, row 2, ..." implies there's something about the data that implies
order.

If there isn't a "numbered key", is the text file organized by date/time, or
by ...? Access won't impose an order on a "bucket o' data", unless you tell
it how to determine the order.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[nlh3011]

There is nothing on the text file that defines the order. Its an annoying
system generated report that is apparantly "too difficult to change", but it
looks like this.

Supplier name Invoice No. Amount
Supplier A 123456 £10
125345 £11
235687 £20
Supplier B 456789 £30
123896 £40

So I have blank fields in my table that need populating. I have written a
routine that will look at the row above to get the supplier name but it only
works if the rows are in the correct order. Once the order is lost there is
no way of working out the correct supplier.

Is there anyway of adding a numbered column on the text file that access can
use?

 

[Jeff Boyce]

I suppose one option would be to open the text file using something like
Excel, insert a column, and add in "row numbers", then import THAT file into
Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Another thought ... if the data you are seeing is a report, pre-formatted,
then maybe you can persuade the provide to provide the underlying raw data
rather than the report itself...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jimbo]

I am still new to access, however, I had a similiar problem in which I needed
to read and hash a large text file for certain data.
I read in the file, manipulated the data, saved the results all on the fly.
Here is a slight example of how I did it in access and ran as a macro -
hopefully you get the idea.:

Sub Read_Data()
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fs, a, retstring, Rownumber

'Open Source File
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.OpenTextFile("place source path and filename here.txt",
ForReading, False)

'Create Destination File
Set b = fs.CreateTextFile("place destination path and filename here.txt",
True, False)

Do While a.AtEndOfStream <> True
Rownumber = Rownumber + 1
retstring = a.ReadLine

b.writeline (RowNumber + a)

Loop

a.Close
b.Close

 

[gllincoln]

Hi!

Another approach, one that has worked for me in the past, would be to
create a temporary import table and populate that with the file, parsing the
file on the fly. Since I cannot be sure that the file presents fixed length
records although it kind of looks like it might be.... I assume, based on
your statements that the format of this report is locked, that the format
and content will not change.

The example requires the table be in place with the appropriate data types :
tblTempImport, has columns
[Supplier Name] text 50
[Invoice No] long
[Amount] Currency (assuming English locale)

This code assumes that Microsoft DAO 3.51 or similar library is loaded
(Alt-F11, Tools, References)

The first question would be, is the file name always the same? If this is
true, then you can use a constant. Otherwise you need to acquire the path
and file name one way or another. I will assume a constant in the example.
The parsing logic won't work if you have leading spaces in front of the
Supplier Name, we are assuming that either there is a supplier name and it
is the first thing in the row of information or else we have a bunch of
spaces then the Invoice No. appears.

Hope this alternate view of how to get at the information gives you some
ideas.

The code: (aacchhhhoooooo)
========================================================

Sub ImportMyFile()

'declare constant value file path and name
Const myFile = "C:\import_folder\import_file_name.txt"

'set reference to target table
Dim rs As DAO.Recordset
Set rs = DAO.OpenRecordset("tblTempImport")

'declare working variables
Dim g As String
Dim i As Integer
Dim count As Long

Dim vData(3) As Variant
Dim gSupplier As String

Open myFile For Input As #1
'get past the header row
Line Input #1, g

Do While Not EOF(1)

'get the next row of data
Line Input #1, g

'add to the count of rows
count = count + 1

'parse out the data
vData(1) = Trim(Left(g, InStr(g, " ")))
vData(2) = Trim(Mid(g, InStr(g, " "), InStr(g, "£") - 1))
vData(3) = Trim(Mid(g, InStr(g, "£") - 1))

'if we have a new supplier change the name stored in gSupplier
If Len(vData(1)) > 0 Then gSupplier = vData(1)

'insert the current supplier name
vData(1) = gSupplier

rs.AddNew
For i = 1 To 3
rs.Fields(i - 1) = vData(i)
Next
rs.Update
Loop

Close #1
Set rs = Nothing
MsgBox "We have imported " & count & " records.", vbOKOnly, "Import
Completed"
End Sub