importing text files to Access 2000+ and ADO 2

[yetanotherjo]

I need to import a .sif file into Access using VBA (it's equivalent to a column delimited file). So far I've only been able to import it using "transfer text" if I've renamed it to have a .txt extension, otherwise Access won't recognise it as a file it can read.

The other clunkiness in this is that as far as I could tell I had to save the import structure and can't find a way to move this between databases without manually creating and saving it again...is there a way to easily port this between databases? Oh, and can this be edited using code?

 

[JerryKlmns]

yetanotherjo,

If you want to import that ImportSpecification to a new DB, open the new DB and on the menu click File->Import. Select the database where you have saved the ImportSpecification. On the Import Objects form, click on Options>> and just select Import/Export Specs.

As for editing that ImportSpecification, I have come to a dead end

 

[PWise]

MSysIMEXColumns
MSysIMEXSpecs

are names of 2 hidden tables that have spec info

how to edit the tables beats me

 

[yetanotherjo]

Worked a treat, thanks Jerry.

pwise: I didn't think of the system files...at least I can easily docoment the specs with those. Thanks.

Next question is any idea how to tell Access that a eg. "*.s01" file is really just a text file?

 

[JerryKlmns]

yetanotherjo, the next line renames the *.sif to a .txt file. If you need more help or the name of that file isn 't always the same, let me know

Name (Data_Folder & YourFileName & ".sif") As (Data_Folder & YourFileName & ".txt")

where Data_Folder is the full path
YourFileName is just the name of the file

 

[JoeCruse]

Jerry,

I have a problem similar to yetanotherjo, where my file is a .qan, and I want to get it to a .txt format. The file name will change, also.

Does the code above go into a batch file, or is it to go in the Access application?

I'd appreciate any advice you could pass along, Jerry. I went through an upgrade on an instrument, and the software stores data completely different than before, meaning I'll have to import the data in a totally different way. I'm looking at importing it in individual analysis test files, but am having problems seeing my way around this. Your post loks interesting.

Thank you in advance.

Joe

 

[yetanotherjo]

Thanks Jerry

Joe: I put Jerry's code in my module and used a variable to hold the file extension so I could rename the file back to it's orginal name once I was done with it. I read the original file name in with the extension attached so this will now handle any text format files with an extension not ".txt".

Good luck with yours!

 

[JerryKlmns]

JoeCruse,

What do you mean with

"the software stores data completely different than before, meaning I'll have to import the data in a totally different way."

Is the record layout of the new file saved by the new softwear? Also, how many records do you have to Import? Should you run a check for acceptable values before importing the file?

Pls, be more specific....

 

[JoeCruse]

JerryKlmns,

I think I have the main import of the text file issue figured out now.

What we had dealt with previously was: the spectrometer kept results stored in to related tables in its own .mdb files. I simply linked these tables from this .mdb file to my own Access LIMS, and ran a query to append data to my own stuff.

With the software change came a change in data storage. The software still has the same .mdb file, but it is not used to store routine analyses anymore. The software creates .rdb files for each archive name in the software that results are stored in. We have to use many archives, because ofthe need to separate product data. This situation makes our old way of life untenable.

The software will let you make it send individual results, with all needed information, to a text file, every time it runs a sample analysis. The route I'm going with is to import the data in this text file. I finally got this worked out, thank goodness.

I do have a question for you on renaming files though. This software spits out the individual sample analyses as a XXXXXXX.qan file. I need to be able to change the file extension programatically to XXXXXX.txt, so Access can read it (I'm running WIN XP and Access 2002). Another issue is that the file name is never the same. The software spits the file out using the time/date transmitted as the file name.

My question would be: where would the Renaming code you posted above be placed, and how could it handle situations where the file name is never the same?

Thank you for your help,

Joe

 

[p27br]

hi

use the following code to fetch all files in a directory and import them :

    'With Application.FileSearch
    '    .NewSearch
    '    .FileName = "*.txt" 'use wildcard for file name
    '    .FileType = msoFileTypeAllFiles
    '    .LookIn = "c:\my documents"
    '    .LastModified = msoLastModifiedToday
        
    '    If .Execute > 0 Then
                  
    '        For I = 1 To .FoundFiles.Count
                 Docmd.transfertext ....foundfiles(i)
    '        Next I
    '    End If
    '    intFileCount = .FoundFiles.Count
    'End With

or

sFile = Dir$(strPath & "\" & strFileName, vbNormal)

Do While sFile <> ""

    '  Do something with the file here.

    sFile = Dir$
Loop

 

[JerryKlmns]

Just set a reference to the Microsoft Office 9.0 Object Library in the References dialog box (found in Tools menu), by selecting the appropriate check box and follow what p27br suggested at first. Change

.FileName = "*.txt"

to your extension and the renaming code I posted, place it inside the For ... Next loop

Feel free for a new question ..........

 

[JoeCruse]

JerryKlmns and p27br,

Thanks for the help guys. I ended up writing a batch file to do this instead. I have the batch file renaming the .qan text file that is generated for every analysis, and it renames it "superq.txt. My Access module runs, and imports the needed text, and then deletes the file.

Thanks again,

Joe