|
| Append Table after import |
| Iniciado por steven, 28,ene. 2016 22:31 - 4 respuestas |
| |
| | | |
|
| |
| Publicado el 28,enero 2016 - 22:31 |
Hi Guys,
I am having a slight issue with figuring out what is the correct method of doing the equivalent of an Append Query in Access.
I have an excel report that I am importing into a table. I have managed to get the Excel File imported into the Database, but I am struggling to figure out how to deal with duplicates if I have to re-import this list due to additions to the original excel report.
I had looked to use a separate import table, the to append the new records into the main database without having any duplicates.
All the methods for importing excel seem to have issues surrounding the handling of duplicates for what I want to do, by either adding in all the records again & creating a large number of duplicates, or having an error report for every duplicate that is being attempted to be imported.
Excel file can contain in excess of 70,000 records & there may be only 1 or 2 additions to this file between imports, however there is no means of identifying just the new records to allow for entry of just these new items.
I am using WinDev 20, so have used the HImport]XLS method, but can't seem to figure out the best way of removing the Duplicate information.
What is the correct way of doing the equivalent of a Append Query in accessso that I can use a temporary import table, then only add the new records.
Similarly, what is the equivalent of the Update Query in Access?
The Update query only seems to let me update to a manual entry, unless I am not using it correctly.
New to WinDev so easily confused. I have searched through the tutorials & this forum / WinDev online Help, but have not been able to find the information I am after.
I am sure there is probably an easy way to do it, but I can't seem to find it for some reason.
I have tried importing an access database, but it takes the query in as SQL so does not show me the WinDev way of doing it.
Any help appreciated.
Steven |
| |
| |
| | | |
|
| | |
| |
| Publicado el 29,enero 2016 - 13:13 |
Hi Steven
As I'm not using access, I cannot give you any equivalent method.
However to import an xls file, as you requested, I would :
- first transform the xls in csv (for speed processing), either before
my processing, or in my processing, with OLE automation orders to excel
- then read the whole file in a string with floadtext
- loop through the file with extractstring, on the CR separator
- then add each record with a hadd
- and of course, ignore all the duplicate errors with honerror
Best regards
--
Fabrice Harari
International WinDev, WebDev and WinDev mobile Consulting
Ready for you: WXShowroom.com, WXReplication (open source) and now WXEDM
(open source)
More information on http://www.fabriceharari.com
On 1/28/2016 4:31 PM, Steven A wrote:
Hi Guys,
I am having a slight issue with figuring out what is the correct method
of doing the equivalent of an Append Query in Access.
I have an excel report that I am importing into a table. I have managed
to get the Excel File imported into the Database, but I am struggling
to figure out how to deal with duplicates if I have to re-import this
list due to additions to the original excel report.
I had looked to use a separate import table, the to append the new
records into the main database without having any duplicates.
All the methods for importing excel seem to have issues surrounding the
handling of duplicates for what I want to do, by either adding in all
the records again & creating a large number of duplicates, or having an |
| |
| |
| | | |
|
| | |
| |
| Publicado el 29,enero 2016 - 13:55 |
Fabrice,
Thanks again for your help.
I have managed to import the XLS file into a separate table, but it is the duplicate error handling that I am struggling with.
I have done a bit of digging on the HOnError, but still a bit confused as to what I should be doing with this.
I have imported the XLS into a separate table (Temporary Import Table) at present & was trying to figure out how to import the new activities & update the existing records with the latest information into the separate live file.
I have tried playing around with the HAdd & HModify, but still run into the duplicate entry issue.
How do I handle the Duplicate entries so that if a record exists it gets updated & if it does not exist then add as new record.
Thanks again.
Steven |
| |
| |
| | | |
|
| | |
| |
| Publicado el 30,enero 2016 - 01:16 |
Hi Steven,
importing in a first table means slower process.
However, once you have your first table (or my text file in memory), and
now that we know that you want to add/edit, and not only add new lines,
you can do this:
- loop on reading lines in your temporary file (hreadfirst/hreadnext)
- hreadseek on the main file
- if found, hmodify, if not hadd
Best regards
--
Fabrice Harari
International WinDev, WebDev and WinDev mobile Consulting
Ready for you: WXShowroom.com, WXReplication (open source) and now WXEDM
(open source)
More information on http://www.fabriceharari.com
On 1/29/2016 7:55 AM, Steven A wrote:
Fabrice,
Thanks again for your help.
I have managed to import the XLS file into a separate table, but it is
the duplicate error handling that I am struggling with.
I have done a bit of digging on the HOnError, but still a bit confused
as to what I should be doing with this.
I have imported the XLS into a separate table (Temporary Import Table)
at present & was trying to figure out how to import the new activities &
update the existing records with the latest information into the
separate live file.
I have tried playing around with the HAdd & HModify, but still run into
the duplicate entry issue.
How do I handle the Duplicate entries so that if a record exists it gets
updated & if it does not exist then add as new record.
Thanks again.
Steven |
| |
| |
| | | |
|
| | |
| |
| Publicado el 30,enero 2016 - 18:07 |
Fabrice,
Once again, thanks for your help.
Will give this a try.
Preference would be to do this into the main table, but was struggling to get round the duplicate entry issue.
Regards,
Steven |
| |
| |
| | | |
|
| | | | |
| | |
|