Transfertext import text file - pls help!!! 2

[RichM1]

I've been working for days on trying to import a simple text file which is in the format below (16 columns, each separated by a

MF;;Admin;;;21152;SB WCTR LAB;11/07/04;21:44:44;1;;;;;;0

I've tried the following command which doesn't work because this looks for commas as a delimiter. This code when ran places everying into one cell (I guess I could work with this but seems like a lot of work). I used to have a Spec file in my mdb but now that it's an adp the spec file will NOT work (I can't even create a spec file - the save as option isn't available).

DoCmd.TransferText acImportDelim, , "Upload", PortPath & "\UPLOAD.TXT", False

Can anyone help??? I've tried stuff like:

lngHandle = FreeFile()
FileName = PortPath & "\UPLOAD.TXT"
Delimiter = ";"
Open FileName For Input As lngHandle

Not sure where to go after this....

 

[psemianonymous]

Wow. If you're actually using the Access application, you can use File->Get External Data->Import.

Then select "Text File *.txt" as the file type. This should pop up the Text Import Wizard, which you will be happy to note has an advanced option to change the delimiter from comma to ... anything you want, including semicolon.

Later, once you've got the wizard working well enough, you can save the text import settings, and use these settings for the TransferText function.

So ... so I'm telling you to use the wizard; it exists.

 

[RichM1]

I'm trying to automate the process. I need to automatically import a text file to an access table. I'm using ADO in an access project ADP. I used to use the MSysIMEXSpecs table and specify my settings in Access97. Now that I've converted to an Access 2003 ADP project I don't have the MSysIMEXSpecs table and can't import using the transfertext command like I used to. Everyone says to click the advanced button when doing a manual import to create a SPEC file and use that with the docmd.transfertext but the SAVE AS and SPEC buttons are grayed out and not accessible! I've learned through hours of reading that it's not available in an ADP project.

 

[PHV]

A starting point:
lngHandle = FreeFile()
FileName = PortPath & "\UPLOAD.TXT"
Delimiter = ";"
Dim myArr As Variant
Open FileName For Input As #lngHandle
Do While Not EOF(lngHandle)
Line Input #lngHandle, InputData
myArr=Split(InputData, Delimiter)
If UBound(myArr) = 15 Then ' got the 16 fields
' Stuff dealing with myArr(0) ... myArr(15)
End If
Loop
Close #lngHandle

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[payback]

Did you also double-post this in the ADP forum? I have replied to you in there.

 

[RichM1]

Thanks for everyone's help, it worked!