import text specification

[brunoylupe]

Hi, I have data in comma delimited files. I have a database shell that I want to import the text files into. The database shell (downloaded from the Census) contains import specifications for different types of data to import. I can import each file one at a time manually and choose the correct import specification from a list of values. I want to patch together a macro or some code to automate the procedure because it is very tedious and easy to make an error. I need it for my thesis research. I am less proficient with the code, but I am trying to learn. I have created a few macros for other projects.

[RuralGuy]

How many different import specifications do you think you will need?

[brunoylupe]

thanks for the reply Rural Guy, I read with appreciation your reply and code to the instructor. I was hoping to get to share with you. I need at least two per 'table'. The data resides in huge flat files. The geographic data (file) is one specification that is needed to make sense out of where the data is representative. The other file, I will call the data file, could be one of many, depending on whether the parameters I need are closely related. So I may have to iterate this process many times. That is why I need to automate. It hurts my hands! I will isolate where in the range (of huge flat files) the data I seek is located, then I will systematically (50 states, 50 times) go after that 'huge flat file' (and the corresponding geo file). I had only considered doing this one variable (or parameter, or element) at a time, but if the solution is so elegant, I could see how 3 or more import specifications could be applicable. Still one geographic, but possibly more than one huge flat file (slice of data) if one seeks more than one variable at once. That sounds more complicated to me. To date, with my manual efforts I have only attempted two (one geo) during the same import 'event'. The 2 Giga byte database size limit does come into play, eventually, I wonder if I could fit two specifications in without deleting data (to make more room). When I am done I can view the data and the field names in MS Access.

[RuralGuy]

You will have to forgive me but what you said did not make any sense to me. Let me try another question. How many different "flat files" will you be dealing with and how many time will you need to iterate through them with different specifications?

[brunoylupe]

My apology, I ramble, and I am not clear. I do this by hand one at a time, but I think in terms of two at a time so I do not get confused. If I want to map the entire U.S., say for number of people who walk to work, I do this for 100 files. If I just want to map one state I only need to do this twice.

[brunoylupe]

To map one state I need to do this for two files. To map the U.S. I need to do this for 100 files. I cannot see the answer I just typed. I hope this is not a duplicate. It is done one at a time, but I think in groups of two at a time.

[RuralGuy]

Is each state in a different format? I would think one specification would work for all states.

[brunoylupe]

Yes, same format. Two formats per state. Geographic data and census data.

[RuralGuy]

So all you should need is two import specifications, right?

[brunoylupe]

Correct.
I have been looking at the specifications (they already exist, I do not know how to use [call] them), and they appear similar.
The only way I know to 'see' them is to actually go through the import wizard.
That is the geographic specifications (each state has one) appear similar; and, the data specifications 002, 003, 004... appear similar.

I thought I needed (the ability) to call each specific data specification exactly (for instance the work I have done to date uses only the 003 specification for data) because I did it 'wrong' before and the data was not usable.

[RuralGuy]

Can you point me to the Census site you used so I can download and play with some of the data and tools?

[RuralGuy]

It looks to me like Linking is a better option than Importing for your situation but I'm still playing with it.

[brunoylupe]

Yes
http://www2.census.gov/census_2000/d...ummary_File_3/