problems with importing data

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
 
G

Guest

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
 
G

Guest

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...
 
G

Guest

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
 
G

Guest

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...
 
G

Guest

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Data Access Page | Why use this feature? 3
Inserting/deleting data in table in multiuser environment 5
Help needed with TransferSpreadsheet method 3
Simple Insert Into... 28
Multiply qty of filtered records 7
Check if data already exists in database. 3
Excel Import Specification 1
Import Several Excel Workbooks; Create Several Tables 11

Top