Import Advice

  • Thread starter Thread starter Daniel
  • Start date Start date
D

Daniel

I have written a program in FoxPro that imports many different types of text
files. Once imported, the data is then manipulated and exported to one big
table. So basically the file formats are totally different, but the final
table puts all the data into a readable format.

I want to do this in Access. What is the easiest way to do this. Linking
to the text file? Then grabbing the data that way. The procedure will be
done everyday.

Can someone point me in the right direction?

Thanks
 
Hi Daniel,

Linking to the text files is probably the right way to go, provided they
are regular data files (e.g. CSV, tab-separated, or fixed-width). You'd
probably use

DoCmd.TransferText acLink

to link a text file as a temporary table. Then you could use something
like

Dim dbD As DAO.Database
Dim rsR As DA0.Recordset
Dim fldF As DAO.Field
Dim strSQL As String

Set dbD = CurrentDB()
Set rsR = dbd.OpenRecordset("MyTempTable")
For Each fldF in rsR.Fields
'extract the field names, check the data they contain
...
'build the SQL statement for an update query
'that manipulates the data and appends it
'to your main table
...
strSQL = ...
...
Next
dbD.Execute strSQL
 
John,

I imported them as fixed width, the data is very scewed, I have to grab info
from one line, skip down three lines and grab more data. Then I look for
something else in another field and start the procedure all over again.

Is this still the way to go.

Thanks for the quick response.

Daniel
 
Daniel,

In that sort of situation it's often better not to use the import
wizard. Instead, write code that uses the VBA file-handling statements
to read the file a line at a time, handling each line as required (e.g.
assembling one record for your Access table from several input lines,
and then appending it to the table). Joe Fallon MVP has posted this
skeleton code:

Public Sub ImportFile(strPath As String)
'Originally posted by Joe Fallon MVP
Dim db As DAO.Database, rs As DAO.Recordset
Dim sLine As String, sTrimmed As String
Set db = CurrentDb()
Set rs = db.OpenRecordset("TableName", dbOpenTable)

Open strPath For Input As #1

'Read a single line from an open sequential file
'and assign it to a String variable.
Line Input #1, sLine
'Trim the leading blanks
sTrimmed = LTrim(sLine)

Do While Not EOF(1)
'read the next line of the file
Line Input #1, sLine
sTrimmed = LTrim(sLine)

'manipulate the string if necessary, then add it to the rs table.
If rs.BOF = True Then
rs.AddNew
Else
rs.Edit
End If
rs.Update
Loop
End Sub



John,

I imported them as fixed width, the data is very scewed, I have to grab info
from one line, skip down three lines and grab more data. Then I look for
something else in another field and start the procedure all over again.

Is this still the way to go.

Thanks for the quick response.

Daniel
Click to expand...
 
Absolutely awesome. Thanks John.


John Nurick said:
Daniel,

In that sort of situation it's often better not to use the import
wizard. Instead, write code that uses the VBA file-handling statements
to read the file a line at a time, handling each line as required (e.g.
assembling one record for your Access table from several input lines,
and then appending it to the table). Joe Fallon MVP has posted this
skeleton code:

Public Sub ImportFile(strPath As String)
'Originally posted by Joe Fallon MVP
Dim db As DAO.Database, rs As DAO.Recordset
Dim sLine As String, sTrimmed As String
Set db = CurrentDb()
Set rs = db.OpenRecordset("TableName", dbOpenTable)

Open strPath For Input As #1

'Read a single line from an open sequential file
'and assign it to a String variable.
Line Input #1, sLine
'Trim the leading blanks
sTrimmed = LTrim(sLine)

Do While Not EOF(1)
'read the next line of the file
Line Input #1, sLine
sTrimmed = LTrim(sLine)

'manipulate the string if necessary, then add it to the rs table.
If rs.BOF = True Then
rs.AddNew
Else
rs.Edit
End If
rs.Update
Loop
End Sub
Click to expand...
 
Back
Top