Schema.ini for multi-row import?

[Mike L]

I've got some data which is basically just an outputted report. I need to
create one record out of a file that is like

NAME ACCOUNT

ADDRESS1 ADDRESS2
<JUNK> PHONE

etc.etc.

Each record is the exact same layout and separated by a page break. I know
if each record was on one line the schema.ini would be pretty easy to make.
Can a multi-row schema.ini file be set up?

Thanks.

Mike L.

 

[John Nurick]

Hi Mike,

As far as I know there's no way of getting the text file driver to use
FF - Chr(12) - as the record separator: it assumes you want to use CR+LF
- Chr(13) & Chr(10).

So I think your choices are

1) write VBA code that opens a recordset, assembles records and writes
them to disk; or

2) use an Access report and export it to a text file. If you make sure
that all vertical dimensions in the report are multiples of 0.25 inches,
and format all the text in 12 point Courier New or other monospaced font
things should work OK. Sometimes it helps to install the Windows
Generic/Text Only printer driver and use that for the report.

 

[Mike L]

Any chance you could post a snippet of code showing read line 1 10 chars to
Field 1, read line 2 10 chars to Field 2 to then next rec on FF? I should
be able to extrapolate from that. I'm not a programmer, just a poor O/S
guy!

(e-mail address removed)

 

[John Nurick]

This should get you started:

Dim rsR as DAO.Recordset
Dim lngFN as Long
Dim strLine As String
Dim strBuf As String

Set rsR = CurrentDB.OpenRecordset("MyTable")

lngFN = FreeFile() 'get number for file handle
Open "C:\folder\file.txt" For Input As #lngFN

'main loop to read file
Do Until EOF(lngFN)

'Append a new record to work on
rsR.AddNew

'read a line
Line Input #lngFN, strLine

'get characters 1 to 10
strBuf = Mid(strLine, 1, 10)

'put them in a field
rsR.Fields("Field1").Value = strBuf

'repeat for other fields. You can address fields by
'number, too: rsR.Fields(0) is the first field.


'save the new record
rsR.Update

Loop

rsr.Close
Close #lngFN

 

[Mike L]

Thank you.

This should get you started:

Dim rsR as DAO.Recordset
Dim lngFN as Long
Dim strLine As String
Dim strBuf As String

Set rsR = CurrentDB.OpenRecordset("MyTable")

lngFN = FreeFile() 'get number for file handle
Open "C:\folder\file.txt" For Input As #lngFN

'main loop to read file
Do Until EOF(lngFN)

'Append a new record to work on
rsR.AddNew

'read a line
Line Input #lngFN, strLine

'get characters 1 to 10
strBuf = Mid(strLine, 1, 10)

'put them in a field
rsR.Fields("Field1").Value = strBuf

'repeat for other fields. You can address fields by
'number, too: rsR.Fields(0) is the first field.


'save the new record
rsR.Update

Loop

rsr.Close
Close #lngFN