Reading a Text File that is not comma deliminated

[Atliens911]

I want to create a module that reads in a txt file that doens't always have the same dilimentor. the file looks like this

4
AInc
886946 208008 150416 961946 1112362
74.80 23 0 ,CC , 5
BInc
938985 297416 236148 943085 1179233
100.88 100 0 ,LCCC, 5
CInc
881946 188164 124572 967946 1092518
68.89 0 0 ,CC , 5
DInc
964946 214824 154232 964946 1119179
76.78 31 0 ,CC , 5

I need AInc 20808 150416 961946

lets say its company name sales assets and total and these are columns in a table called cash.

I'd like to set each field up in parallel arrays and update my table cash.

Can someone give me some direction?

thanks

 

[KenReay]

Hi

If the file is comma delimited or fixed width field (sometimes called SDF), you can import it via standard code in Access (DoCmd.TransferText), you may need to set up an import specification (if you do not know how to do this, post back, it is not exactly intuitive).

If the file is not in one of the 'standard' formats or is as you imply a mixture, you will have to write code to read the file in and parse the contents

You may be able to do this using teh FileSystem object library, but I have not done this before so cannot give guidance, alternatively you can use the 'old' and rather basic OPEN , INPUT LINE etc commands, if you need to resort to this, I would suggest a quick read of the appropriate help and post back with any queries

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Atliens911]

Thanks ken. I will need to parse the file. I'm not so worried about that but I'm having difficulty with updating my table. I'm using

Insert into Cash
(Comp_Name, Sales, Assets, Total)
Values
("XXXX", 123, 456,657)

and I'm getting a error that the types don't match but I checked them and they do. Any suggestions?

 

[KenReay]

Hi

Apart from the obvious one that the types do in fact not match, which you say you have checked - sorry nothing else comes to mind

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PaulBricker]

You might want to look at the Split Function if you are running A2000 or newer. Essentially it takes a string and and splits it based on a delimiter (in your case you only seem to want the values delimited by a space) and you can specify the number of values returned (again in your case it seems you want a certain number of values out of your string.
HTH

Paul

 

[MichaelRed]

from the original post, the file "looks like" it consists of:

A single record which is hte count of the (actual) number of records to follow.

A single element line which is the "Company"

Two lines with data elements related to the company.

If this is correct, the parse routine needs to account for the "who cares" (number of records) then skip every third line.

In terms of the insert stmt, I only see the three values taken from the record, not the fourth (Total).


If the process is concerned with large numbers of records being imported, I would suggest that you do a search for basGrabFile in these fora, as it is a function for to access (import) large text files into memory in a more rapid manner than 'ye olde' line input.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[WayneRyan]

Atliens911,

Not tested, but something like this should do it.

' Declare table variables
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String

' Declare file variables
Dim buffer As String
Dim ptr As Integer

' Declare working variables
Dim intCompanies As Integer
Dim intLoop As Integer

' Declare table Columns
Dim Company As String
Dim Sales As Long
Dim Assets As Long
Dim Total As Long

Set dbs = CurrentDb

Open "C:\Textfile.txt" For Input As #1

Line Input #1, buffer
intCompanies = Val(Trim(buffer)) ' How many companies?

For intLoop = 1 to intCompanies
Line Input #1, buffer
Company = RTrim(buffer)
Line Input #1, buffer
buffer = LTrim(buffer)
buffer = RTrim(buffer)
'
Sales = CLng(Trim(Mid(buffer, 1, InStr(1, buffer, " &quot - 1)))
buffer = Mid(buffer, InStr(1, buffer, " &quot)
buffer = LTrim(buffer)
'
Assets = CLng(Trim(Mid(buffer, 1, InStr(1, buffer, " &quot - 1)))
buffer = Mid(buffer, InStr(1, buffer, " &quot)
buffer = LTrim(buffer)
'
Total = CLng(Trim(Mid(buffer, 1, InStr(1, buffer, " &quot - 1)))
buffer = Mid(buffer, InStr(1, buffer, " &quot)
buffer = LTrim(buffer)
'
sql = "Select * from Cash where Company = '" & Company & "'"
set rst = dbs.OpenRecordset(sql)
If rst.EOF and rst.BOF Then ' New company ...
rst.AddNew
rst!Company = Company
rst!Sales = Sales
rst!Assets = Assets
rst!Total = Total
rst.Update
Else
rst.Edit
rst!Sales = Sales
rst!Assets = Assets
rst!Total = Total
rst.Update
End If
' Skip line in input file
Line Input #1, buffer
Next intLoop

btw,

How does one make code "look" readable here?

Wayne