Hi!
On the Bureau of Labor Statistics web site ( there is a link to an FTP site (ftp://ftp.bls.gov/pub/time.series/sa) where they make available data files in ascii format that have names like "sa.data.5c.California." There are often three or four files per state, and some of them are quite large. As you can see, these files don't have the standard '.txt' extension, and they have multiple periods in the titles, so ACCESS 2000 won't allow me to link to them or import them.
Interestingly, I can import some of the smaller ones directly into Excel using "Get External Data," but many of the files are too large to import into into Excel.
Microsft has a couple of support articles on this topic, providing two general solutions: changing the registry to add a new extension to the list of acceptable text extensions, or temporarily changing the file name by adding the .txt extension. Neither of these are valid solutions, however, since the files contain multiple "periods" in their names, and the final extension is the state name usually. Additionally, I'm trying to avoid having to manually copy the text file down to a drive here and play with the filenames each month. By the way, I don't know why the bls uses that kind of file naming convention!
My goal is to create a module or procedure that will populate a table that contains the data we need for about 12 states. I want someone to be able to click a button on a form that says "Update Data" and a background process will go out to the ftp site and import selected files into a table for them.
Any suggestions?
Thanks!
Karen Grube
On the Bureau of Labor Statistics web site ( there is a link to an FTP site (ftp://ftp.bls.gov/pub/time.series/sa) where they make available data files in ascii format that have names like "sa.data.5c.California." There are often three or four files per state, and some of them are quite large. As you can see, these files don't have the standard '.txt' extension, and they have multiple periods in the titles, so ACCESS 2000 won't allow me to link to them or import them.
Interestingly, I can import some of the smaller ones directly into Excel using "Get External Data," but many of the files are too large to import into into Excel.
Microsft has a couple of support articles on this topic, providing two general solutions: changing the registry to add a new extension to the list of acceptable text extensions, or temporarily changing the file name by adding the .txt extension. Neither of these are valid solutions, however, since the files contain multiple "periods" in their names, and the final extension is the state name usually. Additionally, I'm trying to avoid having to manually copy the text file down to a drive here and play with the filenames each month. By the way, I don't know why the bls uses that kind of file naming convention!
My goal is to create a module or procedure that will populate a table that contains the data we need for about 12 states. I want someone to be able to click a button on a form that says "Update Data" and a background process will go out to the ftp site and import selected files into a table for them.
Any suggestions?
Thanks!
Karen Grube