Import all Fields as Text

[dssrun]

I have about 30 text files with different number of fields. I am importing them with a script and I want to make sure all data is imported without data type errors (all files are comma deliminated without a field header). My solution is to import all fields as text. How can I accomplish this in VB? I created a import specification with a default of 150 fields all set to text but this did not work). I know one file has at least 70 fields, so i chose 150 to be on the safe side. Can this be accomplished?

[Bob McClellan]

Not sure how to answer this but I have to ask.... you actually want to import 30 files as tables having 70+ columns with no headers??? you will be working with tables having fields labeled as Field1, Field2....... This will be a nightmare to deal with. Is there no way of getting the headers so you at least have a fighting chance?

[Rawb]

I have to assume you're importing them to a temporary Table and then massaging things around into a proper normalized database structure. Because if you're not, I'm with Bob on this one: managing it will be a nightmare!

My solution though, would be to use a temp Table to import the data to. Then you can use VBA to copy your imported data to the actual Table it goes to.

If you use this method, you can convert the values to strings/text by just using the CStr() Function when you're copying the data to it's final location.

[dssrun]

I am using Access as an automated solution, not so much as a database container. Here is my process:

1. Copy the files over to my PC and import them to access (giving the tables the name of the file)
2. Based on the data I have in access already, compare the newly imported data and export only the new data from each file.

Do you see a better solution then the one I propose (maybe I am going about this the wrong way and should just try and compare the raw text files)? I was going to create a VBA procedure that created LEFT OUTER JOINS to find the new data and export it.

[Rawb]

Well then. . .

If you pre-create the Tables you'll be importing to, you'll be able to set the data type of the Fields to whatever you want.

Access has pretty loose rules for data type integrity so, as long as you import into a Table with text data type Fields, Access will keep them as text.

Unfortunately, this means you have to manually create the temp Tables you're going to import into.

[dssrun]

Yes i was pursuing that option until I started playing around with the textstream object. Since all the lines are in the same order from file to file and new data is added to the end, i decided to not use access and create a different solution. I appreciate the suggestion and I will probably test both methods out. If i import it to access I will have to play around with creating two tables for each file and a LEFT OUTER JOIN query.