new VBA script to batch import xml files in MS access
Budget: £20 – £250 GBP
I have a script written by someone else that imports xml files into an access table ("Prezzi"). It did the job just fine, with a couple of issues mentioned below.
I now have another 3 sets of a total of 20,000+ xml files. Like before, the structure of the xml and therefore table doesn’t change much over time, but from time to time the issuer of xml files has added some fields and dropped others, so we need to take it in steps in the sense that we need to be patient if not all fields are present and add them.
I need to add this next lots of xml files to the corresponding tables in the access file:
1) “ServiziDispacciamento”
2) MBPAltriServizi
3) MBPRiservaSecondaria
The files are in chronological order and the follow the structure:
- YYYYMMDDServiziDispacciamento.xml
- YYYYMMDDMBPAltriServizi.xml
- YYYYMMDDMBPRiservaSecondaria.xml
The xml files need to be appended to the corresponding tables in the access file. The xml files are named in sequence with the following structure YYYYMMDDnameofthetable.xml where the suffix nameofthetable obviously does not change. Some files are attached so that you can test the script. I am not sure if there are missing files, should the file be missing, the script should write down the filename/date in a (single) txt file or in a separate table, and fill in the 24 rows (hours) for that day with an empty cell. Not a N/A, not a 0 (that would mess up the formulas I will need to use later on in the process).
Once complete, the script should write in the txt the outcome of the import process: something like x files were successfully processed, y files were missing.
I should be able to run this script as a macro from within MS access with the ability to change the dates of "from" and "to" or "all files in the folder xyz". If a date is already present in the access database, an option should be given before starting (not at each occurrence) to either overwrite or to skip that date/data.
I would like the xml files to be stored in separate folders so that I can keep a track of what is being do
Compared to the script in the access file attached that you can freely leverage, I would need to you to make changes to the code in the access file attached so that the data are easier to use/query afterwards:
- Numbers should be imported in fields that are classified as numbers with “double” length, rather than text strings
- Numbers currently contain a comma as a decimal separator. This should be converted to be a dot (“.”) in the table
The access file contains the empty tables ready for the import of xml files above. Each of the fields are classified as long text, but they all (except for "data" and "ora") need to be converted into a number with "double" length.
This should be everything, please ask away if you have any questions.
I will consider these main elements in assessing your proposals:
- evidence of experience at previous similar jobs
- time to deliver
- cost
I now have another 3 sets of a total of 20,000+ xml files. Like before, the structure of the xml and therefore table doesn’t change much over time, but from time to time the issuer of xml files has added some fields and dropped others, so we need to take it in steps in the sense that we need to be patient if not all fields are present and add them.
I need to add this next lots of xml files to the corresponding tables in the access file:
1) “ServiziDispacciamento”
2) MBPAltriServizi
3) MBPRiservaSecondaria
The files are in chronological order and the follow the structure:
- YYYYMMDDServiziDispacciamento.xml
- YYYYMMDDMBPAltriServizi.xml
- YYYYMMDDMBPRiservaSecondaria.xml
The xml files need to be appended to the corresponding tables in the access file. The xml files are named in sequence with the following structure YYYYMMDDnameofthetable.xml where the suffix nameofthetable obviously does not change. Some files are attached so that you can test the script. I am not sure if there are missing files, should the file be missing, the script should write down the filename/date in a (single) txt file or in a separate table, and fill in the 24 rows (hours) for that day with an empty cell. Not a N/A, not a 0 (that would mess up the formulas I will need to use later on in the process).
Once complete, the script should write in the txt the outcome of the import process: something like x files were successfully processed, y files were missing.
I should be able to run this script as a macro from within MS access with the ability to change the dates of "from" and "to" or "all files in the folder xyz". If a date is already present in the access database, an option should be given before starting (not at each occurrence) to either overwrite or to skip that date/data.
I would like the xml files to be stored in separate folders so that I can keep a track of what is being do
Compared to the script in the access file attached that you can freely leverage, I would need to you to make changes to the code in the access file attached so that the data are easier to use/query afterwards:
- Numbers should be imported in fields that are classified as numbers with “double” length, rather than text strings
- Numbers currently contain a comma as a decimal separator. This should be converted to be a dot (“.”) in the table
The access file contains the empty tables ready for the import of xml files above. Each of the fields are classified as long text, but they all (except for "data" and "ora") need to be converted into a number with "double" length.
This should be everything, please ask away if you have any questions.
I will consider these main elements in assessing your proposals:
- evidence of experience at previous similar jobs
- time to deliver
- cost