Importing from Text File with Multi-Line Records?

[Elisa Francesca Roselli]

I have a text file that I want to import into an Access table. The table
has already been set up with an Autonumber primary key, then three
fields of memo type called EN, FR and SP. These are to hold equivalent
records in English, French and Spanish.

My problem is that, although I can find information on importing simple
text files where fields are delimited by commas and records are only one
line long, that is not the case here.

Both records and fields can be many lines long and both can contain
practically any character within the body of the field, making choice of
a delimiter problematic.

What I have done is to set up the µ (Greek letter Mu) as field delimitor
and ++++++++++++++ (fifteen plus signs) as the record delimiter.
Obviously other characters or combinations of character could be used.
Here's a sample from the text file:




"Calculating restore volume ..."
µ
"Calcul du volume \`a restaurer ..."
µ
"Calculando el volumen de restauraci\'on ..."

++++++++++++++

"Restore in the present can only be performed on\n"
"deleted files made visible with the depth of field."
µ
"La restauration dans le pr\'esent ne peut s'effectuer\n"
"que sur des fichiers disparus visibles en activant la\n"
"profondeur de champ."
µ
"La restauraci\'on en el presente s\'olo se puede realizar en \n"
"ficheros eliminados que se han hecho visibles con la profundidad de campo."

++++++++++++++

"Abort restore in progress?"
µ
"Arr\^eter la restauration en cours ?"
µ
"\!?Anular la restauraci\'on en curso?"

++++++++++++++

"You do not have read access rights to "%S" ...\n"
"Abort restore in progress?"
µ
"Vous n'avez pas l'acc\`es en lecture sur "%S" ...\n"
"Arr\^eter la restauration en cours ?"
µ
"No dispone de derechos de acceso de lectura a "%S" ...\n"
"\!?Anular la restauraci\'on en curso?"

++++++++++++++

Could someone give me pointers as to how to approach importing this into
Access via VB? I've done some VBA programming before but still am rather
a newbie.

Many thanks,

Elisa Francesca Roselli
Ile de France

 

[Thomas Winkler]

Hi,

Both records and fields can be many lines long and both can contain
practically any character within the body of the field, making choice of
a delimiter problematic.

What I have done is to set up the µ (Greek letter Mu) as field delimitor
and ++++++++++++++ (fifteen plus signs) as the record delimiter.
Obviously other characters or combinations of character could be used.
Here's a sample from the text file:

"Calculating restore volume ..."
µ
"Calcul du volume \`a restaurer ..."
µ
"Calculando el volumen de restauraci\'on ..."

++++++++++++++

it seems as the only way to import these fields is to parse the file by your
own and put the text into the correct fields in a table.

Search for help on: Open, Close, Line Input

create a new module and write a function like "ImportFile".

Public Sub ImportFile(strFile as string)

Const strDeviderField = "µ"
const strDeviderRecord ="+++++++++++++++"

Dim strDummy as string
Dim intFileNum as integer
dim strField as string
Dim rst as DAO.Recordset
dim bytFieldCounter as byte

intfilenum = freefile

strfield=""
bytFieldCounter = 0

set rst = currentdb.openrecordset("<your_destination_table>")
rst.addnew

Open strFile For Input As #intfilenum
Do While Not EOF(intfilenum)
Line Input #intfilenum, strDummy

if (strdummy = strdeviderField) or (strdummy = strdeviderrecord) then
rst(bytFieldCounter) = strfield
bytfieldcounter = bytfieldcounter + 1
strfield=""
elseif strdummy = strdeviderrecord then
rst(bytFieldCounter) = strfield
bytfieldcounter = 0
strfield=""
rst.update
rst.addnew
else
strfield=strfield & strdummy
endif

Loop
Close #intfilenum

rst.undo
rst.close
set rst=nothing
end sub

I did not test this code. It is not written in the VB environment. you might
have to rework it. But it could be a good hint how to write such a function.

HTH.

Thomas

 

[Elisa Francesca Roselli]

Thomas Winkler a écrit :

it seems as the only way to import these fields is to parse the file by your
own and put the text into the correct fields in a table.

Search for help on: Open, Close, Line Input

create a new module and write a function like "ImportFile".

Public Sub ImportFile(strFile as string)

Const strDeviderField = "µ"
const strDeviderRecord ="+++++++++++++++"

Dim strDummy as string
Dim intFileNum as integer
dim strField as string
Dim rst as DAO.Recordset
dim bytFieldCounter as byte

intfilenum = freefile

strfield=""
bytFieldCounter = 0

set rst = currentdb.openrecordset("<your_destination_table>")
rst.addnew

Open strFile For Input As #intfilenum
Do While Not EOF(intfilenum)
Line Input #intfilenum, strDummy

if (strdummy = strdeviderField) or (strdummy = strdeviderrecord) then
rst(bytFieldCounter) = strfield
bytfieldcounter = bytfieldcounter + 1
strfield=""
elseif strdummy = strdeviderrecord then
rst(bytFieldCounter) = strfield
bytfieldcounter = 0
strfield=""
rst.update
rst.addnew
else
strfield=strfield & strdummy
endif

Loop
Close #intfilenum

rst.undo
rst.close
set rst=nothing
end sub

I did not test this code. It is not written in the VB environment. you might
have to rework it. But it could be a good hint how to write such a function.

HTH.

Thomas

Thanks for your return. I've been mulling over it and trying to figure
it out.

I had some problems with the incrementing of bytfieldcounter because the
input file includes some empty lines which I don't want to be read as
fields. So I've modified the code like this:

*****

Public Sub ImportFile(strFile As String)

Const strDeviderField = "µ"
Const strDeviderRecord = "++++++++++++++"

Dim strDummy As String
Dim intFileNum As Integer
Dim strField As String
Dim rst As DAO.Recordset
Dim bytFieldCounter As Byte

intFileNum = FreeFile

strField = ""
bytFieldCounter = 0

Set rst = CurrentDb.openrecordset("T_langpairs")
rst.AddNew

Open strFile For Input As #intFileNum
Do While Not EOF(intFileNum)
Line Input #intFileNum, strDummy


If (strDummy = strDeviderField) Then
rst(bytFieldCounter) = strField
bytFieldCounter = bytFieldCounter + 1
strField = ""

ElseIf (strDummy = "") Then
bytFieldCounter = bytFieldCounter

ElseIf strDummy = strDeviderRecord Then
rst(bytFieldCounter) = strField
bytFieldCounter = 0
strField = ""
rst.Update
rst.AddNew
Else
strField = strField & strDummy

End If
Loop
Close #intFileNum

' rst.Undo
rst.Close
Set rst = Nothing
End Sub

*****

I still have the problem that when the record is multi-line, it goes
into Access all on one line. In fact, I will eventually need it to come
_out_ of Access exactly as it went it, occupying the same number of
lines as in the input file. I thought a line break was possible in
fields of Memo data type?

Anyway, thanks for your help and I'll keep researching.

EFR
Ile de France

 

[Thomas Winkler]

Hi,

I had some problems with the incrementing of bytfieldcounter because the
input file includes some empty lines which I don't want to be read as
fields. So I've modified the code like this:

[...]


Therefore i included a little more elegant solution. Just see the following
code...

I still have the problem that when the record is multi-line, it goes
into Access all on one line. In fact, I will eventually need it to come
_out_ of Access exactly as it went it, occupying the same number of
lines as in the input file. I thought a line break was possible in
fields of Memo data type?

Yes, it is. The line break in VBA is "vbCrLf" which stands for "Carriage
Return + Line Feed). Just see the code...

Public Sub ImportFile(strFile As String)

Const strDeviderField = "µ"
Const strDeviderRecord = "++++++++++++++"

Dim strDummy As String
Dim intFileNum As Integer
Dim strField As String
Dim rst As DAO.Recordset
Dim bytFieldCounter As Byte

intFileNum = FreeFile

strField = ""
bytFieldCounter = 0

Set rst = CurrentDb.openrecordset("T_langpairs")
rst.AddNew

Open strFile For Input As #intFileNum
Do While Not EOF(intFileNum)
Line Input #intFileNum, strDummy

'to delete non-visible characters
strDummy=Trim$(strDummy)

'no empty line?
if len(strDummy) > 0 then
If (strDummy = strDeviderField) Then
rst(bytFieldCounter) = strField
bytFieldCounter = bytFieldCounter + 1
strField = ""
ElseIf strDummy = strDeviderRecord Then
rst(bytFieldCounter) = strField
bytFieldCounter = 0
strField = ""
rst.Update
rst.AddNew
Else
'not only add strDummy but include vbCrLf if necessary
strField = strField & iif(len(strfield)>0, vbCrLF, "") & strDummy
End If
endif
Loop
Close #intFileNum

'When the last line in text file is strdeviderfield
'then you should call .Update to avoid errors.
strDummy = strDeviderField then rst.update

'If Undo causes no troubles, you should include it, because, when
'the last line in your text file is like strdeviderrecord
'an .Addnew is called without following data input and .Update.
'May be an "if" could help you...
if strDummy = strDeviderRecord then rst.Undo

rst.Close
Set rst = Nothing
End Sub

HTH

Thomas