I'm not certain how to handle this one. The data import procedure in my database is simply a set of VBA/command buttons that allow the user to browse the network and select a CSV file. The code deletes the existing table, and imports the new CSV file as a new table, and proceeds to append/update the data from that point.
The CSV files being imported, that my queries are based upon can be of two basic formats, in both cases, having the same fields, and headers, with one exception. Sometimes the input will have one specific field present, other times it will not, all other fields remaining labeled, and the same. My issue is that when the field is not present, I do not know how to put a dummy string in all entries for that value, or preferably leave null, but still append the data with that field entry.
Unfortunately, of late, the powers that be, decided that we needed a new classification for some entries, and as such a new field has appeared. This field is sporadic however, some runs it is present, some runs it is not, it depends on the data being imported. As long as the field is present I can do business as usual. However, if the field is missing from the CSV file, my queries ask for user input, because the referred field in the import table is missing. Perhaps I'm going about this the wrong way, is there a better way to import the data and then manipulate it, that I'm missing? Or is it merely a function checking for existence of a field? I've attempted to use isnull, and iif statements to attempt to enter values for the field if it is not there, but so far nothing has worked.
Original Header String: (still used)
SOURCE,REGION,AUID5_20,S,CO3,TCM,AS,TOTAL_VOLUME,TOTAL_MASS
New Header String: (Used for only certain circumstances)
SOURCE,REGION,MATERIAL,AUID5_20,S,CO3,TCM,AS,TOTAL_VOLUME,TOTAL_MASS
I realize the simplest solution would be to include the field in all CSV files, and have it blank for those instances where it's not needed, however I'm not in control of that step of the process, and as a result must try to find a way to work with variable input data streams. Should I create a blank table template and via VBA make a copy of the table structure to import to, or is there a way in the SQL code to import the "MATERIAL" field as it exists in the CSV, or as null, or a text string if it does not exist? Ie: for loop through the headers first and determine what's present before actually importing the data?
The CSV files being imported, that my queries are based upon can be of two basic formats, in both cases, having the same fields, and headers, with one exception. Sometimes the input will have one specific field present, other times it will not, all other fields remaining labeled, and the same. My issue is that when the field is not present, I do not know how to put a dummy string in all entries for that value, or preferably leave null, but still append the data with that field entry.
Unfortunately, of late, the powers that be, decided that we needed a new classification for some entries, and as such a new field has appeared. This field is sporadic however, some runs it is present, some runs it is not, it depends on the data being imported. As long as the field is present I can do business as usual. However, if the field is missing from the CSV file, my queries ask for user input, because the referred field in the import table is missing. Perhaps I'm going about this the wrong way, is there a better way to import the data and then manipulate it, that I'm missing? Or is it merely a function checking for existence of a field? I've attempted to use isnull, and iif statements to attempt to enter values for the field if it is not there, but so far nothing has worked.
Original Header String: (still used)
SOURCE,REGION,AUID5_20,S,CO3,TCM,AS,TOTAL_VOLUME,TOTAL_MASS
New Header String: (Used for only certain circumstances)
SOURCE,REGION,MATERIAL,AUID5_20,S,CO3,TCM,AS,TOTAL_VOLUME,TOTAL_MASS
I realize the simplest solution would be to include the field in all CSV files, and have it blank for those instances where it's not needed, however I'm not in control of that step of the process, and as a result must try to find a way to work with variable input data streams. Should I create a blank table template and via VBA make a copy of the table structure to import to, or is there a way in the SQL code to import the "MATERIAL" field as it exists in the CSV, or as null, or a text string if it does not exist? Ie: for loop through the headers first and determine what's present before actually importing the data?
Last edited:
