2

I have a requirement to read Access DB Extract in Talend. There is a component in Talend 'tAccessInput' which is used to read Access DB tables. But it requires a connection to Access Database.

However, for my requirement i am given an extract of Access db, say MasterTables.accdb and it is not a live database connection. I need to extract the tables that are present in this Access DB Extract using Talend.

Also, i know there is an option of exporting from Access DB Extract by opening the extract and exporting the required tables, but i do not wont to do it manually.

So, is there a component/steps that can help me achieve my requirement using Talend.

YowE3K
  • 23,852
  • 7
  • 26
  • 40
user3454116
  • 184
  • 1
  • 12
  • Why can't you use tAccessInput ? you can tell it to use an existing connection (by first connecting to your db file, using a tAccessConnection), or configure it with your database file directly in the component itself, by unchecking "Use an existing connection", and giving it the path to your access database file. – Ibrahim Mezouar Dec 27 '17 at 14:43
  • Hi @iMezouar, the access db server is not present on my system. I was just given an extract of access DB that had few of the tables we needed to process and hence i dont have connectivity to the Access DB server. Is there a way i can read this extract ? – user3454116 Dec 27 '17 at 15:23
  • You do not need a server. All you need is the database file (.mdb or .accdb). In the tAccessInput properties you have a field called database, where you can specify the file path to your .accdb file, a user and a password (if it's protected). Give it a try and let me know. – Ibrahim Mezouar Dec 27 '17 at 15:28
  • hi @iMezouar So i tried to give do as suggested, but then how does the tAccessInput know which table to read as i have to define a schema – user3454116 Dec 27 '17 at 16:43
  • Do you know which table you have in your database ? If so, you can specify it in the query. I'm not sure about the exact syntax of Access, but "select * from table" should work. – Ibrahim Mezouar Dec 27 '17 at 16:45
  • I did put the query in the and it extracts the table details from the access extract. Now just this last question, i get below warning: "Error in the metadata of the table: table's row count in the metadata is 2087 but 2078 records have been found and loaded by UCanAccess. All will work fine, but it's better to repair your database." Do you know why i might be getting this warning. – user3454116 Dec 27 '17 at 19:11
  • I was not familiar with this warning, a little search lead me to this thread: https://stackoverflow.com/questions/32596720/ It appears to be a "bug" in Access, to remove the warning you have to open the file in Access, and perform a "Compact and Repair Database" operation, and save the file. – Ibrahim Mezouar Dec 27 '17 at 20:55
  • That works. Thanks for the guidance. Appreciate your help. I will put an answer below. – user3454116 Dec 27 '17 at 22:57
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/162057/discussion-between-user3454116-and-imezouar). – user3454116 Dec 28 '17 at 00:06

1 Answers1

0

As guided by @iMezouar(Thanks for the inputs), i was able to use tAccessInput component of Talend and achieve my requirement. Below are the steps i followed:

Step 1- Configure tAccessInput component. Set the Database field with the path of the .accdb extract. Leave the username and password blank if the extract is not password protected

Step 2- In the Table Name field provide the name of the table that you want to read from your access extract

Step 3- Go to edit schema section and add the column details of the TableName provided in the above step

Step 4- Now go to Query Type and select Guess Query. Once you have clicked this button, it will populate the Query section with the relevant query

Step 5- Next connect the tAccessInput to tMap if you intend to do any processing else connect it directly to an output component. In my case its tFileOutputDelimited and your job is ready to run to extract data from the access dump

Step 6- If you get a Warning issue in the Run Console i.e 'Error in the metadata of the table: table's row count in the metadata is XXX but XXY records have been found and loaded by UCanAccess. All will work fine, but it's better to repair your database', just open the access dump, go to Database Tools tab and select 'Compact and Repair Database'. Then save the file. This will remove the warning issue also.

user3454116
  • 184
  • 1
  • 12