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