I cant append data in tables after txt import!!!

[mihalisp]

Hi all,

i want to ask when importing data from a text file ,if this data is appended to a table or just inserted?

What is my problem?
I have 4 txt delimited files which update every week. So once a week i have to import the data from the txt files in 4 different tables in my database.

These 4 tables are related to each other with one to many relationship.
To be more specific,Table 1 is Customers which is related to the other 3 tables with one to many relation.I have deactivated the relationship integrity in all these relations.

When i try to import the txt files ,the data is not appended an an error occurs which says how many rows deleted,how many rows lost due to primary key violation etc...,although i have checked that all data types are ok.When the table is empty,all data are inserted in tha table without problems,but the problem occurs the second time that i will import data in the same table.

I dont want to delete the data already in these tables before attempting to import updated data from txt files because i want to keep old data (old customers who have stopped being active anymore) which maybe dont exist in new updated txt files each week.

Please,can someone help me with this?

Below,i have the code which lets me browse and pick a txt file from the disk and imports it in the table.(2 subs)

Private Sub browseaf_Click()
On Error GoTo Err_browseaf_Click

'Declare a variable as a FileDialog object.
Dim fd As FileDialog
Dim vrtSelectedItem As Variant

'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)


'Reference the FileDialog object.
With fd
'Allow the user to select only one file.
.AllowMultiSelect = False
' Show method to display the File Picker dialog box and return the user's action.
'The user pressed the action button.
If .Show = -1 Then
'Step through each string in the FileDialogSelectedItems collection.

Forms![browse_form]![txtfilenameaf].Enabled = True
Forms![browse_form]![txtfilenameaf].SetFocus


For Each vrtSelectedItem In .SelectedItems

'Lstbox.RowSourceType = "value List"
txtfilenameaf.Text = vrtSelectedItem

Next vrtSelectedItem

'The user pressed Cancel.
Else
Set fd = Nothing
Exit Sub
End If

End With


'DoCmd.SetWarnings False

MsgBox "You selected: " & txtfilenameaf.Text
Af1


' Set form controls
'Forms![browse_form]![txtfilenameaf].Enabled = True
'Forms![browse_form]![txtfilenameaf].SetFocus
Forms![browse_form]![browseaf].Enabled = True



'Set the object variable to Nothing.
Set fd = Nothing

Exit_browseaf_Click:
Exit Sub
Err_browseaf_Click:
MsgBox Err.Description
Resume Exit_browseaf_Click

End Sub
--------------------------------------------------------------------------

Public Sub Af1()
On Error GoTo af1_Err

DoCmd.SetWarnings False
DoCmd.TransferText acImportDelim, "Af1", "Af1", txtfilenameaf.Text, False, ""


af1_Exit:
Exit Sub

af1_Err:
DoCmd.SetWarnings True
MsgBox Error$
Resume af1_Exit

End Sub

 

[Scooterbug]

The flag that went off in my head is this:

When i try to import the txt files ,the data is not appended an an error occurs which says how many rows deleted,how many rows lost due to primary key violation etc...,although i have checked that all data types are ok.When the table is empty,all data are inserted in tha table without problems,but the problem occurs the second time that i will import data in the same table.

Especially when you mention a primary key. Are you using a pre-exsisting field as your primary key?

I import at least 3 files a day, and this is how I do it. I import the data into a local temp table. From there, I do whatever processing of the data that is require. I then append the data to their relevant tables then clear out the temp tables.

What happens when you import the data into a temp table and then try to append it to it's final destination?

 

[mihalisp]

I havent tried it yet.

But i think that this may be the only solution.

The primary key of these tables is not an auto increment number,but it is an id(differnet for each table) which exist in each txt file.
For example the new txt file for customers may include a record that is already an existing customer in the table,but probably there has been a change in th amount that he owrs ,his debt for example.This data cannot be appended and after the import of txt ,the old details for this customer(the old debt) exists in the table.

Anyway,have you got any code for the append query?

1) I am afraid that with the append query,the same thing will happen.I will be getting the same violation messages.

2)What is the difference between the import from txt files and an append query from a temp table??

3)Also i dont want warnings to appear either the one or the other way.

4)As far as i understand i have to copy the 4 tables to be used as temp tables for the data to import from txt.Will these temp tables have the same relations and integrity with the original tables or it doesnt matter??

Thanks.

 

[DCrake]

Lets approach this using an actual example.

Lets say ACME Ltd exists in your customer table and their Primary key is 100, they have a current balance of £500 outstanding at the end of period 3.

Now you want to update your records in your table via the txt import for period 4. The contents of the txt file for ACME Ltd has a new outstanding balance of £3500. If you were to append (add this information as a new record in the customers table) tow things would happen. 1) you would create a duplicate record for ACME Ltd. They would appear twice in the table. 2) you would have 2 different balances.

If you import (Append) the new txt file into a temp file you would have your live customers table and the temp table. Now in your temp table you may have new customers that do not exist in the customers table. Your first task would be to append all new customers form the temp table into the customers table. Next you would have an update query that, for this example updated the current balance, os your query would contain both the old and the temp table with a join on the PK in both tables. You would then update the current balance in the customer table with the new balance from the temp table. Any other fields that may need updating can be done at this point as well.

When you have finshed you can then delete the contents of the temp table.

Repeat the function for all the 4 table making the changes as neccessary.

David

 

[mihalisp]

Next you would have an update query that, for this example updated the current balance, os your query would contain both the old and the temp table with a join on the PK in both tables. You would then update the current balance in the customer table with the new balance from the temp table. Any other fields that may need updating can be done at this point as well.

I dont really understand that.I think that for each table (and temp table together) there will be an update query???Or the update query will take place in 2 steps???First,the new customers update and then append all the rest (the already existed customers in the table which may have new updated outstanding balance).

Anyway,by the method of append query,are you sure that there will be no warnings from the system???

 

[DCrake]

To turn off any *Warnings* from the system you need to place

DoCmd.SetWarnings False
....
DoCmd.SetWarnings True

above and below your OpenQuery command line.

And Yes there will be two queries to run. 1 that appends and one that updates. You can't run both at the same time.

David

 

[Scooterbug]

To add on to what David said:

The primary key of these tables is not an auto increment number,but it is an id(differnet for each table) which exist in each txt file.
For example the new txt file for customers may include a record that is already an existing customer in the table,but probably there has been a change in th amount that he owrs ,his debt for example.This data cannot be appended and after the import of txt ,the old details for this customer(the old debt) exists in the table.

I'm pretty sure that is where your issues lie. If you have an ID number assigned as a PK, then trying to insert a record with the same number will throw up an error. Also, if the data being brought in is a debt number, I would store the debt amount(s) in a separate table and just use a query to add them up.

Anyway,have you got any code for the append query?

1) I am afraid that with the append query,the same thing will happen.I will be getting the same violation messages.

I'll give you an example of one of the imports that I do here at work. Everyday, I receive a file from our bank pertaining to our electronic debits for orders. Now, because the people we get the file from do not allow us to reuse an order number I have to edit the file (We will resubmit orders for various reasons.) I have to edit it because we will add letters to the beginning of the order numbers that have been resubmitted. I store the order numbers as a number datatype, but because some can have an alpha prefix they come in as text. So I have code that cycles through the numbers and checks for those prefixes. If it finds one, it removes it. Now I can append the data to the table that stores all the transactions.

In your case, correct me if i'm wrong, you are importing debit (and or credits I would assume) amounts. You might also be importing customer information as well, correct? Well, for the customer data, you need to determine if that customer already exsists. One way to do that is to build a query that links the pre exsisting customer table with the temp table via the CustomerID number. Change the join to include all records from the temp table. You can then set the criteria for the CustomerID from the Main table to IS NULL, and you will get a list of customers that were imported but do not exsist in the Main customer table. You can then run an append query to add those records.

2)What is the difference between the import from txt files and an append query from a temp table??

Once the data is in a temp table, you can modify, compare or process the data before putting it into the main table.

3)Also i dont want warnings to appear either the one or the other way.

You can also use:
Currentdb.execute "SqlStatementOrQueryName
in lieu of using the docmd.runsql

4)As far as i understand i have to copy the 4 tables to be used as temp tables for the data to import from txt.Will these temp tables have the same relations and integrity with the original tables or it doesnt matter??
Thanks.

Think of the temp tables as the mixing bowl. You use them to do what you need to do to the data to make it fit into your main table. Once you have it molded the way you want, you can transfer the data to the main table and clear out the temp table.

 

[mihalisp]

Well,i found something similar here http://en.allexperts.com/q/Using-MS-Access-1440/Update-Append-text-file.htm

The criteria for him to update or append is a date,what will be the criteria in my situation?the isNull??This is where i am a little bit confused ,about the 2 queries i need .

I mean: 1)In the append query,by which criteria will or how will the query understand which records to append

and

2)in the update query ,how the query will know which records to update?

Also ,i forgot to mention that in these 2 queries i want to merge,join,combine or concatenate(i dont know which of them is the proper word) 3 fields(string) to one(string) and then add or update this one field in the main Table .But,i dont want all the records of these 3 fields to be cancatenated,but only some of them depending on if a certain field is Null or Not.I believe i made clear what i need.Excuse me if i cant describe it well.

Thank you in advance.

 

[Scooterbug]

Would it be possible for you to post the db? You can take out any sensitive data (or better yet, add some dummy data). Include some of the data you want imported. I know it's easier for me to come up with a solution if I can see it in front of me. Also, if possible, upload the db as a MDB file if you are using A2007 (Not everybody uses A2007, so you will get better responses with a MDB file)

 

[mihalisp]

Well, for the customer data, you need to determine if that customer already exsists. One way to do that is to build a query that links the pre exsisting customer table with the temp table via the CustomerID number. Change the join to include all records from the temp table. You can then set the criteria for the CustomerID from the Main table to IS NULL, and you will get a list of customers that were imported but do not exsist in the Main customer table. You can then run an append query to add those records.

This is what i wanted to know about the append query in my situation.The isNull will be the criteria.

What will be the criteria for the update query?or there is no need for criteria in this query?On second thoughts,i think that there is no need for criteria here,am i right?

 

[Scooterbug]

This is what i wanted to know about the append query in my situation.The isNull will be the criteria.

What will be the criteria for the update query?or there is no need for criteria in this query?On second thoughts,i think that there is no need for criteria here,am i right?

The sql will be something like this:

INSERT INTO tblCustomers (CustomerID, CustomerName, {Other fields})
SELECT tblTemp.CustomerID, tblTemp.CustomerName {Other fields}
FROM tblTemp 
RIGHT JOIN ON tblTemp.CustomerID=tblCustomers.CustomerID
WHERE tblTemp.CustomerID IS NULL

I'm not that good with writing actual Sql from scratch...so if the right join isn't the right one, then use a left join. But, if you build it in the Query Builder you can change the join type by right clicking on the join and selecting the correct one.

 

[mihalisp]

Thanks

I made the update query with the 2 tables

Here is the code of the update query:

UPDATE Cf1 INNER JOIN Cf1_temp ON Cf1.customer_id = Cf1_temp.customer_id SET Cf1.customer_id = Cf1_temp.customer_id, ................................ Cf1.[position] = [Cf1_temp].[position], Cf1.status = [Cf1_temp].[status];

It doesnt let me use the asterisk to gather all thefields though...

When i finish them all i will let you know.

 

[Scooterbug]

It doesnt let me use the asterisk to gather all thefields though...

That's correct, you cant update a table using the * you have to specify each field so that access knows what data to put into what field

 

[mihalisp]

I am working on it,but something else came up.

After importing the data in one table i want to do the following and i am stuck.
I want to concatenate 3 fields in one but only where its value is null.

SELECT Af1_temp.card_number, Af1_temp.entity_code, Af1_temp.branch_code, Af1_temp.account_number, Af1_temp.[entity_code] & [branch_code] & [account_number] AS new_card
FROM Af1_temp
WHERE (((Af1_temp.card_number) Is Null));

When i try to make it update query it doesnt work.I want the values of new_card to be placed in card_number where there are null values.

Should this be an update or append query?

Thanks

 

[Scooterbug]

You want an update query. Something like this:

UPDATE af1_temp SET card_Number = [Field1] & [Field2] & [Field3]
WHERE nz(card_Number,0) = 0

What this will do is to update card_number with the concanted value of the three fields when card_number = 0. The Nz() function replaces a null value with the 0. I dont know if the card_number is numeric or text, which is why I used the nz. (It's a good practice to get use to using nz(), expecially when dealing with strings)

 

[mihalisp]

it is a string.

I have been confused with the brackets it doesnt work.

 

[Scooterbug]

What is the sql statement that you tried? I put the brackets in there to signify the different field names that you want to add all together. You need brackets if your field name(s) contain spaces (It's a habit to include them when helping ppl out).

 

[mihalisp]

When i design the query,in the sql view,the code which is generated is the following:

UPDATE Af1_temp SET Af1_temp.card_number = [entity_code] & [branch_code] & [account_number]
WHERE (((Af1_temp.card_number) Is Null));

But anyway something is wrong with this code,i changed it to the following:UPDATE Af1_temp SET Af1_temp.card_number = [entity_code] & [branch_code] & [account_number]
WHERE (((Af1_temp.card_number)="a"));

I also changed the value from Null to " a " in 3 records in the proper table(Af1_temp).

But the result is the following:

card_number
a
a
a

Instead,It should show combined card numbers not a' s ,correct??

 

[Scooterbug]

I have to take off now....I'll take a look tomorrow if nobody beats me to it

 

[mihalisp]

Sorry,it works with the is Null

I was performing the queries in view mode and actually it didnt do anything at all

I double clicked on it and it did the job.

Thanks for everything.