(Is cross-posting possible? Is it kosher?
This same topic is co-posted Access Forums > Import/Export Data. Please forgive me if it is rude to do this. This is my first time on this site.
)
<moderator note: duplicate thread deleted. Not rude, but we prefer it not be done. >
Hello All,
How can I link text files via code with a schema.ini file?
If my schema.ini file look like this:
Code:
[File One.txt]
ColNameHeader=True
Format=Delimited(|)
CharacterSet=ANSI
TextDelimiter="
Col1=LOAN_NUMBER Char Width 255
Col2=PRODUCT_TYPE Char Width 255
Col3=BALANCE Char Width 255
Col4=RATE Char Width 255
Col5=ORIGINATION_DATE Char Width 255
Col6=MATURITY_DATE Char Width 255
Col7=MONTH_END_DATE Char Width 255
Col8=F_V_INDEX Char Width 255
Col9=SPREAD Char Width 255
Col10=PERCENT_OWNERSHIP Char Width 255
Col11=REVOLVING_IND Char Width 255
Col12=IS_NON_PERFORMING Char Width 255
Col13=PRODUCT_DESCRIPTION Char Width 255
Col14=PRODUCT_SUB_TYPE Char Width 255
Col15=PRODUCT_TYPE_1 Char Width 255
or even this:
Code:
[File Two.txt]
ColNameHeader=True
TextDelimiter="
Format=Delimited(|)
CharacterSet=ANSI
and I run the following code:
Code:
Sub LinkToTextFiles()
On Error GoTo ErrHandler
' include a reference to the "Microsoft Scripting Runtime"
Dim pfdgFolder As FileDialog
Dim pstrFolder As String
Dim pstrFile As String
Dim pstrTableName As String
Set pfdgFolder = Application.FileDialog(msoFileDialogFolderPicker)
pfdgFolder.AllowMultiSelect = False
pfdgFolder.Title = "Select a Folder"
pfdgFolder.Show
pstrFolder = pfdgFolder.SelectedItems(1)
ChDir pstrFolder
pstrFile = Dir("*.txt")
Do Until pstrFile = ""
pstrTableName = Replace(Left(pstrFile, 7), "-", "")
DoCmd.TransferText acLinkDelim, , pstrTableName, pstrFile, True
pstrFile = Dir()
Loop
GoTo CleanUp
ErrHandler:
MsgBox Err.Description
Resume CleanUp
Resume 'debugging only
CleanUp:
MsgBox "done."
End Sub
...I incorrectly wind up with a table for each file containing only one column...the first column.
Yet, when I import with the following code (and the same schema.ini file):
Code:
Sub LinkToTextFiles()
On Error GoTo ErrHandler
Dim pfdgFolder As FileDialog
Dim pstrFolder As String
Dim pstrFile As String
Dim pstrTableName As String
Dim pcdb As DAO.Database
Set pcdb = CurrentDb()
Set pfdgFolder = Application.FileDialog(msoFileDialogFolderPicker)
pfdgFolder.AllowMultiSelect = False
pfdgFolder.Title = "Select a Folder"
pfdgFolder.Show
pstrFolder = pfdgFolder.SelectedItems(1)
ChDir pstrFolder
pstrFile = Dir("*.txt")
Do Until pstrFile = ""
pstrTableName = Replace(Left(pstrFile, 7), "-", "")
pcdb.Execute "SELECT * INTO " & pstrTableName & " FROM [Text;FMT=Delimited;HDR=Yes;DATABASE=" & pstrFolder & ";].[" & Replace(pstrFile, ".", "#") & "];", dbFailOnError
pcdb.TableDefs.Refresh
pstrFile = Dir()
Loop
GoTo CleanUp
ErrHandler:
MsgBox Err.Description
Resume CleanUp
Resume 'debugging only
CleanUp:
MsgBox "done."
End Sub
I successfully import all columns from all files into individual tables. But I don't want to import data (essentially duplicating the data). I want to link to the text files.
Is there another way to link text files via code (using DAO methods)? Am I doing something wrong when trying to use DoCmd.TransferText? Is there something wrong with my schema.ini file (doesn't seem like it since it works for the DAO.Execute method)?
Thanks for any help anyone can provide,
CTB