Nigel,
A - The Dir() function can be used to find if files exist in a folder. I would save the folder locations in a table, NOT IN YOUR CODE. You might also store the character patterns like HZN, IDA, etc.
B - There are tons of conversion functions for strings and numerics.
C - I would create a temporary import accdb or possibly create links to the text files. I try not to import directly into my files since will cause bloat. I also prefer to not pull directly into the main data tables.
D - Adding an date/time field with a default value of Now() should work.
E - Update queries could possibly be used.
F - I would probably set up an Archive folder and use VBA to move the files.
G - Access can handle lots of records. This might depend on how you have created your tables and how you use them. SQL Server is a good alternative if your Access file gets too large.
Regards,
Duane
I'm starting to plan the new application...so here are the first questions (I'm sure of many!)...
One of the key parts of the new application, and the process, will be downloading the monthly .txt data files and importing into the database for analysis/reporting. I can describe the process, although there are some alternatives that I haven't decided on yet, but from a coding point of view I've no idea which is 'better', and once that's decided, how to actually go about achieving the code/process.
This is the overall process...
1 - access the phone supplier's website, select the monthly data files to be downloaded, download the files (zipped) to a dedicated folder on the company's G:\ drive
2 - unzip the files to the same folder, delete the ZIP file (the files are available to download again if needed)
3 - import the files into the db
4 - either rename the imported files (to add e.g. _IMPORTED) or move the imported files to another dedicated folder
So...
A - I'll need a function to scan the folder for .txt files containing specific characters in the file name - this will dictate which table the data is imported into as there are 2 different data formats (completely different fields). For example files with HZN and IDA in the file name (these are phone calls) will import into one main data table, files with e.g. ACSFF, BB, BBSFI, PANFF, CESFF... will import into another
B - Certain fields will have to be edited/formated - e.g. the field with customers line identifier (CLI) is in text format with a leading +...this needs to be number format without the + for other database operations, the date and time fields needs to be Date/Time format, the price fields need to be currency or long integer (not decided yet)
C - Is it preferable for the function to import into a new (temporary) table upon which I can run queries to modify the relevant fields, then append from this table into the correct main data table...or append straight into the main data table and edit the data format at the same time?
D - In the main table I want to add a field (that isn't in the txt files) for import date and time - this can default to Now()
E - Once data is imported, I need a function/query to run that will match the CLI field in the imported data with the same CLI data in another table (where fields are Key, CLI, CustomerName), and retrieve CustomerName - customer name needs to be include in another new field in the main data table for the recently imported rows
F - Once data is imported, the original txt file/s need to be moved &/or renamed so ready for next month's files. I favour renaming and moving the files
G - In the data import files HZN and IDA there are currently about 1500 new rows each month. As customers and call numbers increase this could grow hugely, so the main table will get very big. I don't know how to sensibly handle a dataset that grows very quickly in terms of archiving data (but still having it accessible (?)
Some of this is a bit vague at the moment, but some initial advice and pointers would be gratefully received - what I don't want to do is start building a structure and spending lots of time researching/coding, and asking you for help, only to find that I didn't go down the right route at the start!
Many thanks in advance.
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of nigel@pegasusconsulting.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, March 21, 2018 7:52 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: New project - I will need some help I'm sure
Sent: Wednesday, March 21, 2018 7:52 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: New project - I will need some help I'm sure
I'm starting to plan the new application...so here are the first questions (I'm sure of many!)...
One of the key parts of the new application, and the process, will be downloading the monthly .txt data files and importing into the database for analysis/reporting. I can describe the process, although there are some alternatives that I haven't decided on yet, but from a coding point of view I've no idea which is 'better', and once that's decided, how to actually go about achieving the code/process.
This is the overall process...
1 - access the phone supplier's website, select the monthly data files to be downloaded, download the files (zipped) to a dedicated folder on the company's G:\ drive
2 - unzip the files to the same folder, delete the ZIP file (the files are available to download again if needed)
3 - import the files into the db
4 - either rename the imported files (to add e.g. _IMPORTED) or move the imported files to another dedicated folder
So...
A - I'll need a function to scan the folder for .txt files containing specific characters in the file name - this will dictate which table the data is imported into as there are 2 different data formats (completely different fields). For example files with HZN and IDA in the file name (these are phone calls) will import into one main data table, files with e.g. ACSFF, BB, BBSFI, PANFF, CESFF... will import into another
B - Certain fields will have to be edited/formated - e.g. the field with customers line identifier (CLI) is in text format with a leading +...this needs to be number format without the + for other database operations, the date and time fields needs to be Date/Time format, the price fields need to be currency or long integer (not decided yet)
C - Is it preferable for the function to import into a new (temporary) table upon which I can run queries to modify the relevant fields, then append from this table into the correct main data table...or append straight into the main data table and edit the data format at the same time?
D - In the main table I want to add a field (that isn't in the txt files) for import date and time - this can default to Now()
E - Once data is imported, I need a function/query to run that will match the CLI field in the imported data with the same CLI data in another table (where fields are Key, CLI, CustomerName), and retrieve CustomerName - customer name needs to be include in another new field in the main data table for the recently imported rows
F - Once data is imported, the original txt file/s need to be moved &/or renamed so ready for next month's files. I favour renaming and moving the files
G - In the data import files HZN and IDA there are currently about 1500 new rows each month. As customers and call numbers increase this could grow hugely, so the main table will get very big. I don't know how to sensibly handle a dataset that grows very quickly in terms of archiving data (but still having it accessible (?)
Some of this is a bit vague at the moment, but some initial advice and pointers would be gratefully received - what I don't want to do is start building a structure and spending lots of time researching/coding, and asking you for help, only to find that I didn't go down the right route at the start!
Many thanks in advance.
__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (4) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
.
__,_._,___
Tidak ada komentar:
Posting Komentar