Table Import Headings

[cms370]

Does access handle automatic column heading assigments for tables?

I have files that I am importing via VBA. As of now the column headings are the same, but in the future, they will change. I would like to continue to import the files into the same database and have access automatically create the new columns.

I tried a simple test and it didnt work. Any help would be appreciated. Thanks.

 

[boblarson]

What is it you are importing? From what type of files?

 

[cms370]

The files are just simple text files. Column headings on the first row and data starts on the second. The files come into the share drive via FTP as binary. I have a perl script that grabs the binary files and converts to text. Then access takes over from there.

 

[cms370]

I should have mentioned that the files contain approximately 8-10 variable channels. These channels are configured for individual assets and that is the reason that they can change. As of now, I have a controlled population that is configured uniformly.

I would like to contain all of the data in one table...but maybe the better approach is to create a unique table for every unique channel setup? This sounds messy and I would have assumed that some capability was embedded within access to handle this particular situation.

However, I am relatively new to this...

 

[gemma-the-husky]

this is tricky

generally there are two options with automated imports

a) ignore column headings, and rely on the columns layout to be correct
b) accept column headings - in which case, invalid data will cause problems

now if you have a file you want to import manually, a line at a time, and process it depending on what you find - you will need to write stuff to do this.

its not too hard, but its harder than the automated stuff

you will need to like at text file handling, and decide how to parse the text into the data you want. if its a csv, you can try "split" function. If its fixed width, then you will need string hanlding functions.

to manipulate the file try these functions

"freefile"
"open for input"
"line input"

 

[cms370]

I am starting to look at an approach that ignores column headings. Data will be read into the file for each asset. I was thinking about having a seperate table that specifies configuration for each serial number (along with the effective date of that config setup).

I wasnt aware of the "text file handling" functions. Do you have a reference or sample code I could use to get familiar. Otherwise I will just check out the help reference in access.

Thanks for the suggestion.

 

[cms370]

Ok...ive decided to bring everything in without column headings. This will allow me to store all asset information in one table. Ive created another file that determines the channel information for each asset.

What I would like to do next is set up the queries so that it will first request the user as to which channels they would like to export, then reference the channel information sheet and finally extract the information from the data table.

Can this be done with simple query functions or do I need to implement this in SQL? Thanks again.