problems with importing data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I’m just exploring the possibilities of VBA in Access and I ran into a couple
of problems. I hope that one of you has a solution.

Problem:
I have to import text files with a ; as a delimiter, into access every
month. I’ve written a script that import these files and it work great. The
only problem is that the first 7 lines (header) and the last line (footer)
contain information about the file, sender etc of the file.
I want to store this data in a separate table in the database for logging
purposes. I’ve tried to do this with a make-table query after importing all
the text files I received (this query copies the 7 header rows and the footer
row into separate tables and combines them into 1 table.)

The problem is that every time I run the query it deletes the old table and
makes a new one. Not so great if you want to log the data for a longer
period. Another problem is that the header/footer data is stored in a single
column in the text file. I want to store every row in a single column.

For example:

The way I receive the data:
H1; fileID
H2; filedate
H3; sendernr
H4; receivernr
H5; product
H6; market
H7; version
F1; number of records

The way I want to store the data in a table:
fileID, filedate, sendernr, receivernr, product, market, version, number of
records

Does anybody have an idea on how to do this? I’ve tried but I can’t come up
with a solution.

Thanks a lot
 
Hi DJ
Let's call the log table log (for example) and the source table source
Create the table with the 8 column you want and then

dim db as database
dim rec_src as recordset
dim rec_trg as recordset

set db=currentdb
set rec_src=db.openrecordset("select * from source order by ID",dbopendynaset)
'I order by ID so I'm sure the first seven and the last one are the line I
want
set rec_trg=db.openrecordset("select * from log",dbopendynaset)
rec_trg.addnew
rec_trg![fileID]=rec_src![name of txt column]
rec_src.movenext
rec_trg![filedate]=rec_src![name of txt column]
rec_src.movenext
etc. for the 7 header
rec_src.movelast
rec_trg![number of records]=rec_src![name of txt column]
rec_trg.update
rec_trg.close
rec_src.close
db.close

Cheers Paolo
 
Thanks,

I'll give it a try. I'll let you know as soon as I've got it working.

Paolo said:
Hi DJ
Let's call the log table log (for example) and the source table source
Create the table with the 8 column you want and then

dim db as database
dim rec_src as recordset
dim rec_trg as recordset

set db=currentdb
set rec_src=db.openrecordset("select * from source order by ID",dbopendynaset)
'I order by ID so I'm sure the first seven and the last one are the line I
want
set rec_trg=db.openrecordset("select * from log",dbopendynaset)
rec_trg.addnew
rec_trg![fileID]=rec_src![name of txt column]
rec_src.movenext
rec_trg![filedate]=rec_src![name of txt column]
rec_src.movenext
etc. for the 7 header
rec_src.movelast
rec_trg![number of records]=rec_src![name of txt column]
rec_trg.update
rec_trg.close
rec_src.close
db.close

Cheers Paolo

DJRave said:
I’m just exploring the possibilities of VBA in Access and I ran into a couple
of problems. I hope that one of you has a solution.

Problem:
I have to import text files with a ; as a delimiter, into access every
month. I’ve written a script that import these files and it work great. The
only problem is that the first 7 lines (header) and the last line (footer)
contain information about the file, sender etc of the file.
I want to store this data in a separate table in the database for logging
purposes. I’ve tried to do this with a make-table query after importing all
the text files I received (this query copies the 7 header rows and the footer
row into separate tables and combines them into 1 table.)

The problem is that every time I run the query it deletes the old table and
makes a new one. Not so great if you want to log the data for a longer
period. Another problem is that the header/footer data is stored in a single
column in the text file. I want to store every row in a single column.

For example:

The way I receive the data:
H1; fileID
H2; filedate
H3; sendernr
H4; receivernr
H5; product
H6; market
H7; version
F1; number of records

The way I want to store the data in a table:
fileID, filedate, sendernr, receivernr, product, market, version, number of
records

Does anybody have an idea on how to do this? I’ve tried but I can’t come up
with a solution.

Thanks a lot
Click to expand...
Click to expand...
 
It works perfectly. The only problem is that I Import multiple files into
one table. So when I've imported two files into the sourcetable it only adds
the headerinfo of the first file and the footer info of the last one. This
means that I get false data (mix between info of file 1 and 2) and that I mis
info (footer of file1 and headerinfo of file2) in my logtable.

I've tried to solve this problem but I the script I've written doesn't work.
It creates a new record but it doesn't copy the data from source to log.

Dim db As Database
Dim rec_src As Recordset
Dim rec_trg As Recordset

Set db = CurrentDb
Set rec_src = db.OpenRecordset("select * FROM source", dbOpenDynaset)

Set rec_trg = db.OpenRecordset("select * from log", dbOpenDynaset)
rec_trg.AddNew

If rec_src![Column1] = H1 Then
rec_trg![FileID] = rec_src![Column2]
End If

If rec_src![Column1] = H2 Then
rec_trg![Filedate] = rec_src![Column2]
End If

If rec_src![Column1] = H3 Then
rec_trg![Sendernr] = rec_src![Column2]
End If

If rec_src![Column1] = H4 Then
rec_trg![receivernr] = rec_src![Column2]
End If

If rec_src![Column1] = H5 Then
rec_trg![Product] = rec_src![Column2]
End If

If rec_src![Column1] = H6 Then
rec_trg![Market] = rec_src![Column2]
End If

If rec_src![Column1] = H7 Then
rec_trg![Version] = rec_src![Column2]
End If

If rec_src![Column1] = F1 Then
rec_trg![NumberofRecords] = rec_src![Column2]
End If

rec_trg.Update
rec_trg.Close
rec_src.Close
db.Close

End Sub
 
Sorry, I thought that in your table you had just one file header.
Your code doesn't write the values in the new record because your if (If
rec_src![Column1] = H1 Then) can't work.You must write h1 etc. between ""
'cause H1 is a string so write it in this way:If rec_src![Column1] = "H1"
Then etc.
If your multiple header are ordered per file you can do in this way:

Dim db As Database
Dim rec_src As Recordset
Dim rec_trg As Recordset

Set db = CurrentDb
Set rec_src = db.OpenRecordset("select * FROM source order by id (or the
field that have the sequential order)", dbOpenDynaset)

Set rec_trg = db.OpenRecordset("select * from log", dbOpenDynaset)
do while not rec_src.eof

rec_trg.AddNew

'If rec_src![Column1] = H1 Then 'you don't need this if because the records
are ordered so the first record contain H1 and his value, the second H2 and
its value etc.
rec_trg![FileID] = rec_src![Column2]

'End If
'now you have to go to the next record in rec_src so you have the data of H2
rec_src.movenext
' If rec_src![Column1] = H2 Then
rec_trg![Filedate] = rec_src![Column2]
'End If
rec_src.movenext
.....
.....
.....
.....

If rec_src![Column1] = F1 Then
rec_trg![NumberofRecords] = rec_src![Column2]
End If
rec_trg.Update
rec_src.movenext
loop
rec_trg.Close
rec_src.Close
db.Close
In this way you read and copy to log all the source.
Regards Paolo

DJRave said:
It works perfectly. The only problem is that I Import multiple files into
one table. So when I've imported two files into the sourcetable it only adds
the headerinfo of the first file and the footer info of the last one. This
means that I get false data (mix between info of file 1 and 2) and that I mis
info (footer of file1 and headerinfo of file2) in my logtable.

I've tried to solve this problem but I the script I've written doesn't work.
It creates a new record but it doesn't copy the data from source to log.

Dim db As Database
Dim rec_src As Recordset
Dim rec_trg As Recordset

Set db = CurrentDb
Set rec_src = db.OpenRecordset("select * FROM source", dbOpenDynaset)

Set rec_trg = db.OpenRecordset("select * from log", dbOpenDynaset)
rec_trg.AddNew

If rec_src![Column1] = H1 Then
rec_trg![FileID] = rec_src![Column2]
End If

If rec_src![Column1] = H2 Then
rec_trg![Filedate] = rec_src![Column2]
End If

If rec_src![Column1] = H3 Then
rec_trg![Sendernr] = rec_src![Column2]
End If

If rec_src![Column1] = H4 Then
rec_trg![receivernr] = rec_src![Column2]
End If

If rec_src![Column1] = H5 Then
rec_trg![Product] = rec_src![Column2]
End If

If rec_src![Column1] = H6 Then
rec_trg![Market] = rec_src![Column2]
End If

If rec_src![Column1] = H7 Then
rec_trg![Version] = rec_src![Column2]
End If

If rec_src![Column1] = F1 Then
rec_trg![NumberofRecords] = rec_src![Column2]
End If

rec_trg.Update
rec_trg.Close
rec_src.Close
db.Close

End Sub
Click to expand...
 
Thanks,

I found out the "" myself. I was allready working on a different solution
but I'll give yours a try too.

Thank you very much for your time and effort.

Paolo said:
Sorry, I thought that in your table you had just one file header.
Your code doesn't write the values in the new record because your if (If
rec_src![Column1] = H1 Then) can't work.You must write h1 etc. between ""
'cause H1 is a string so write it in this way:If rec_src![Column1] = "H1"
Then etc.
If your multiple header are ordered per file you can do in this way:

Dim db As Database
Dim rec_src As Recordset
Dim rec_trg As Recordset

Set db = CurrentDb
Set rec_src = db.OpenRecordset("select * FROM source order by id (or the
field that have the sequential order)", dbOpenDynaset)

Set rec_trg = db.OpenRecordset("select * from log", dbOpenDynaset)
do while not rec_src.eof

rec_trg.AddNew

'If rec_src![Column1] = H1 Then 'you don't need this if because the records
are ordered so the first record contain H1 and his value, the second H2 and
its value etc.
rec_trg![FileID] = rec_src![Column2]

'End If
'now you have to go to the next record in rec_src so you have the data of H2
rec_src.movenext
' If rec_src![Column1] = H2 Then
rec_trg![Filedate] = rec_src![Column2]
'End If
rec_src.movenext
....
....
....
....

If rec_src![Column1] = F1 Then
rec_trg![NumberofRecords] = rec_src![Column2]
End If
rec_trg.Update
rec_src.movenext
loop
rec_trg.Close
rec_src.Close
db.Close
In this way you read and copy to log all the source.
Regards Paolo

DJRave said:
It works perfectly. The only problem is that I Import multiple files into
one table. So when I've imported two files into the sourcetable it only adds
the headerinfo of the first file and the footer info of the last one. This
means that I get false data (mix between info of file 1 and 2) and that I mis
info (footer of file1 and headerinfo of file2) in my logtable.

I've tried to solve this problem but I the script I've written doesn't work.
It creates a new record but it doesn't copy the data from source to log.

Dim db As Database
Dim rec_src As Recordset
Dim rec_trg As Recordset

Set db = CurrentDb
Set rec_src = db.OpenRecordset("select * FROM source", dbOpenDynaset)

Set rec_trg = db.OpenRecordset("select * from log", dbOpenDynaset)
rec_trg.AddNew

If rec_src![Column1] = H1 Then
rec_trg![FileID] = rec_src![Column2]
End If

If rec_src![Column1] = H2 Then
rec_trg![Filedate] = rec_src![Column2]
End If

If rec_src![Column1] = H3 Then
rec_trg![Sendernr] = rec_src![Column2]
End If

If rec_src![Column1] = H4 Then
rec_trg![receivernr] = rec_src![Column2]
End If

If rec_src![Column1] = H5 Then
rec_trg![Product] = rec_src![Column2]
End If

If rec_src![Column1] = H6 Then
rec_trg![Market] = rec_src![Column2]
End If

If rec_src![Column1] = H7 Then
rec_trg![Version] = rec_src![Column2]
End If

If rec_src![Column1] = F1 Then
rec_trg![NumberofRecords] = rec_src![Column2]
End If

rec_trg.Update
rec_trg.Close
rec_src.Close
db.Close

End Sub
Click to expand...
Click to expand...
 
Back
Top