Create an import specification on a variable text file??? (1 Viewer)

[NJudson]

I was wondering if its possible to create an import spec that would import a designated tab-delimited text file which would have a randomn # of fields on each import. I'd like to just set an import spec to import file
-with tab-delimiter
-1st row as header
-all fields as text
-access assign primary key
-create new table

I couldn't think of a way to do this and I couldn't find anything on internet that mentioned if it was possible so I'm thinking its probably not possible but wanted to check for sure. Thanks.

 

[The_Doc_Man]

which would have a randomn # of fields on each import

I won't say "flat out NO" but that's the way I'm leaning right now. An import spec is usually made in the context of a specific file and a destination table. I think it carries too much "baggage" from one import to the next.

To do what you describe is more like a bit of VBA code than an Import spec as far as I understand the situation.

 

[NJudson]

Thanks Doc_Man. It's not that big a deal if I was to import the table manually each time but was hoping that I could automate it on a form. You say that it may be possible with VBA? Is this difficult to do? Off the top of your head do you know of any examples of this? Thanks.

 

[NJudson]

I think I found a possible way to do it programmatically. I can take the source text file and create a schema.ini file on the fly for it. Then use the schema.ini file as my import spec. Sounds like more work than I care to do though. I guess that option is there for the future but I may just do a manual import for now.

 

[Shadez]

The answer is yes! VBA can do the job.

Its a bit tricky so hope you can understand,

Open a ado or dao recordset with all the data you need. With this data you can then create a text delimitated file in your format (i used the following format)

{tablename}filed1,field2,field3,fieldN..........

you have now exported the data.

to import i selected the top record form the table {tablename} and used the field names from the field collection to build an insert or update query.

i kept the order of the fields in my file the same as they apear in the table.








ShadeZ

 

[The_Doc_Man]

The way I would approach this problem involves use of VBA to do text processing.

Given these facts:

1. First record is slot names.
2. Fields are delimited by fixed character.
3. Names in first record are ALSO delimited by (same) fixed character.
4. Either
4.1 - the delimiter character NEVER appears as a legit member of a field's data or
4.2 - the fields are encapsulated in single or double quotes or
4.3 - the fields are encapsulated in quotes IF they contain the delimiter character AND the quoting character is never a legit member of a field's data...

I would use the VBA Open name For Input As File n to open the file, then use Line Input to read one line at a time.

I would use the InStr and Mid functions to pick apart the fields. Remember that InStr has a way to remember where you were when you stopped the previous character search.

I would create a new table based on the elements of the first row. As to the primary key, I don't know if that could be done so easily unless you could predict which column it will be. But you COULD do an autonumber no matter what.

For each line you can input, I would parse out the line and populate the fields based on their order of appearance. Remember that within a recordset, recset.Fields is a valid method of selecting a field.

The topics you need to examine in Help are

Open statement
Line Input statement
CreateTableDef method
CreateField method
InStr function
Mid function

 

[deekras]

importing different .txt into one table

every morning i have to import a .txt file into a specific table. the problem is the table can have a different number of fields or the fields can be in a different order. this complicates the import specification. i am looking for a simpler solution.

 

[WayneRyan]

njudson,

If you can post a small sample of the file, I'll get you started. Shouldn't be
too difficult.

Wayne

 

[deekras]

here are samples of two of the files that i have to import into a table 'tblNew' (from there i append it to tblArchive.

TITLE1 TITLE2 REPORT WEIGHT SHAPE/CUT COLOR CLARITY COMMENTS: DESCRIPTION: DATE PICTURE
USA THE ID CARD™ US 33316501D 0.74 ct. twt PRINCESS G-H SI2-I1 DIAMOND STUD EARRINGS: Nov 22, 2004 WH2239531

TITLE1 TITLE2 REPORT WEIGHT SHAPE/CUT COLOR CLARITY measurements COMMENTS: DESCRIPTION: DATE PICTURE
USA THE ID CARD™ US 33225901D 0.28 ct. twt PRINCESS E-F SI2 0.00 - 0.00 x 0.00 DIAMOND STUD EARRINGS: Nov 18, 2004 WH2239457


notice that the second one has the 'measurements' field.

I will have more files with different fields included or excluded. (up to 50 different files, with between 10 and 20 fields). I will know all the possible field names.
tblArchive has all possible fields.

how do i automate the import or link (makes no difference to me which one i use).

thanks