473,320 Members | 1,926 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Importing data from text file to append to table

68
I am trying to export some values from a table in my database to a text file then have these values imported and apprnded to another copy of the same database table the same table in another location

When I tried to Import the values I got an error stating that X record have been lost due to a key violation. I suspected that I was trying to put values to a auto number field and hence the error occured.

I then created a query that exported all other fields but the primayrkey autonumber field. To this action i also get an error stating that I cannot record changes because the value entered violates settings defined for the table or set.

I am working in access 2007

I do hope that there is a way to import what I Exported and append directly to a table as I want to be able to execuit the emport process with just one mouse click.

This post is connect to the project as describe in my previous post at Automating Data export and import in Access
Jan 25 '10 #1
11 7702
nico5038
3,080 Expert 2GB
Just make sure that the sending department ID is part of your key and make sure that for every specified index this field is present to prevent dupes.

Nic;o)
Jan 25 '10 #2
NeoPa
32,556 Expert Mod 16PB
Do you have any fields set in the destination table with any restrictions set? Cannot be Null for instance.

These should be at least as loosely set as the originating table. Ideally loosen them up fully and add back in any restrictions you want until it either works or you see which restriction is causing the problem.
Jan 26 '10 #3
KingKen
68
I want the data to be imported into the table with all of its structure intact. this is not a one off import it has to be done every month from about twenty different databases. i am thinking about creating a temporary table at the importation of the data then run an append query to add the records to the table.

This method just might work but it brings a lot of over heads. I have to some how remove the table after I finish appending the data to the main table... I am still thinking about how best to go about this.

Is this the right approach? would it work? is there a lighter idea floating around someware out there...? I love to heare from you please help!
Jan 26 '10 #4
KingKen
68
Not sure that I understand what Nico 5038 is saying. The ID field in the table is of type AutoNumber. I dont want to send it to the other table as I would have to allow duplicates in the primary key (I think this is what the first error gave). so I send the the file without the primary key field and hence the second error. I think NeoPa have good Idea of the proplem however I want the table to remain as is and still accept the imported data. Please dont stop sharing your ideas, there are helping me to think outside the box and I am better off with your help.
thenks a lot
Jan 26 '10 #5
nico5038
3,080 Expert 2GB
My proposal is to store the original (autonumber) ID and the code for the department "together" as the final key. This will allow you to trace the records back to the origin (department) and identify that record the next time by the (autonumber) ID.
The autonumber ID needs to be stored in a "normal" numeric field and will have duplicates as the different departments can have the same autonumber ID.

Getting the idea ?

Nic;o)
Jan 26 '10 #6
TheSmileyCoder
2,322 Expert Mod 2GB
I think Nico Makes a very valid point there.

I then created a query that exported all other fields but the primayrkey autonumber field. To this action i also get an error stating that I cannot record changes because the value entered violates settings defined for the table or set.
Your getting an error when your trying to do the export, or when your importing the result of that query?

Exactly what errors are you getting? Error number please, and complete Error msg

What types of data do your table contain, besides the primary key.
Jan 26 '10 #7
NeoPa
32,556 Expert Mod 16PB
@KingKen
You have already hit on the idea I was thinking of moving on to. The suggestion to loosen up the restrictions was simply to identify where the problem was though. It wasn't intended as a long-term strategy.

The idea of an intermediary import table is a good one. It is probably better to create it once, then empty it before and after (belt & braces approach) use each time. This is the way i'd be inclined to go. That way, any errors in the data can be picked up after importing into the import table, and reported on within Access.
Jan 27 '10 #8
KingKen
68
Ok I have tried my new idea and still have problems appending the records. The attachment shows a picture of the error that I have been receiving. When I remove all the primary keys (there are three of them and they are set to "indexed(Allow duplicates)'.

I am thinking that the three keys together form the primary key and though they are set to allow duplication individually they do not allow duplication of the same combination.

Is this so?
Attached Images
File Type: jpg Error.jpg (13.4 KB, 309 views)
Jan 28 '10 #9
TheSmileyCoder
2,322 Expert Mod 2GB
A primary Key cannot contain duplicates, as far as I know. So yes, its quite likely that some combination of of keys sets a primary key.

I am not familiar with Access 2007, and if I recalll correctly, thats what you were working in. There should however still be a button in hte ribbon called "indexes". In 2003 its icon is a few lines of text on the left and some lightning on the right. Click it, and you can see an overview of the indexes in the table, and also which are set as primary key (or if a combination is set as primary key)
Jan 28 '10 #10
NeoPa
32,556 Expert Mod 16PB
There seems to be some misunderstanding here Ken. There can be only one Primary Key. Multiple indices, but only one of them can be primary. Indices may also be unique or allow duplicates. A Primary Key must be unique. Perhaps if you post (in text form please) a list of your indices for us to peruse we could help further.

You mention also you are continuing with your new idea. Considering the comments inbetween, about which you say nothing, it's unclear what you mean by this. Conversations (communication) is best managed by responding one way or another to all points raised. Without this it's very easy to get lost and misunderstand. This is to no-one's benefit.
Jan 29 '10 #11
nico5038
3,080 Expert 2GB
I am trying to export some values from a table in my database to a text file then have these values imported and apprnded to another copy of the same database table the same table in another location
I think the duplicates trouble comes from the multiple databases holding the same table (and autonumber). Combining these in a "master" won't work when there's no additional unique making field. Thus my post that the best way is to create on the "master" table a multiple field unique index consisting of the original autonumber PK combined with the Department field. Thus there's always a reference to the origin in the departments database table and the "master" table will be unique.

Creating a multiple field unique key is done by selecting both fields in the table and than pressing the key button.

Nic;o)
Jan 30 '10 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: sparks | last post by:
I have never done this and wanted to ask people who have what is the best way. One person said import it to excel, then import it into access table. but since this will be done a lot, I am...
6
by: Sam Lazarus | last post by:
I need to import data from a website, but the text is arranged badly: Name:John Doe Title:Grunt ID:314159 Name:Jane Doe Title:Queen-Bee ID:271828 etc...
1
by: Jenny | last post by:
I need to import dates from a txt file that come in the format 01JAN2003. Is there a way to do this when I am importing the file into access? Any advice would be great!
2
by: kuhni | last post by:
Hi everybody, I am totally desperate because I cannot solve a really simple problem: I have a specific text-file which I want to import into an existing database. The problem is that the columns...
1
by: don | last post by:
I'm trying to import a comma delimited text file into MS Access 2002 version - the first column is set for an auto increment primary key and everything works right the first time I import a text...
2
by: nutthatch | last post by:
I want to be able to import an Excel spreadsheet into Access 2K using the macro command Transferspreadsheet. However, the file I am importing (over which I have no control) contains some records...
11
by: kaisersose1995 | last post by:
Hi, I've got an import procedure working, using a standard import specification to import a .csv file into a temporary table. The problem i'm having is that i have 4 different sets of borrower...
2
by: Debbiedo | last post by:
I have a text file that I am importing into an Access table that was generatred from data exported from a Word file. Several (about 20-30) fields are from check boxes on the Word form. These fields...
4
by: chimambo | last post by:
I have 2 problems: 1. I want to import a single text file into an access table using a Macro. I am however getting an error that I need to put a specification name argument. What does this mean?...
4
by: johnporter123 | last post by:
Does anyone have a method of importing a large "FLAT" CSV file into access. The file has well over 255 columns (Fields). Before anyone flames me over normalization, I do not have access to the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.