Importing specific fields from text file

[jarico75@yahoo.]

I have a text file with 261 fields that I need to import inot Access. Due to the limitation of 255 columns in an access table I need to import the fields into separate table. I have attempted to do this using the below code. I am receiving an error of "Syntax error in FROM clause" any help will be much appreciated.

Public Sub ImportFilePortion()
Dim cn As New ADODB.Connection
Dim sqlstring As String
Dim rs As New ADODB.Recordset
Dim thefile, thepath, thefilename, themdb As String
thepath = "J:\Data Conversion\Loan Boarding\A_Ben\"
thefilename = "thefile.txt"
thefile = "J:\Data Conversion\Loan Boarding\A_Ben\thefile.txt"
themdb = "C:\Documents and Settings\josepha\My Documents\"
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & thepath & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""
' rs.Open "select ARM_INITIAL_RATE_ADJ_PERIOD from thefile.txt", cn, adOpenStatic, adLockReadOnly, adCmdText

sqlstring = "Insert INTO [zWholeSaleData] " & _
"SELECT ARM_PAYMENT_OPTION_INDICATOR,ARM_PAYMENT_OPTION_RATE " & _
"FROM [Text;DATABASE=" & thepath & ";" & _
"HDR=YES;FMT=Delimited].[thefile.txt]"

cn.Execute sqlstring
End Sub

 

[DJkarl]

SQL in Access has the same 255 column limit, you will not be able to read the file using this method. Look at FileScriptingObjects or the Open method. You will need to read the data into variables and parse it out into seperate tables.

FYI 261 fields is a HUGE number of fields, I have rarely run across circumstances that warrent this many fields. If you have any control over the data source you might look at changing it.

 

[jarico75@yahoo.]

So I cannot specify which fields to grab using this method? That is helpful. I was hoping to avoid having to loop through variables to insert the data. I unfortunately do not have any control over the file.

 

[jarico75@yahoo.]

I found a fix....... If anyone would like to discuss let me know.

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim PathtoTextFile As String
Dim PathtoMDB As String
Dim myarray() As Variant
Private Sub Form_Load()
PathtoTextFile = "C:\"
PathtoMDB = "C:\"
End Sub​

Private Sub CmdInsert_Click()
Set Cat = New ADOX.Catalog
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & PathtoMDB & "db3.mdb"
'Open the Catalog
Set Cat.ActiveConnection = cn
'delete the table if it exists
On Error Resume Next

'Insert into table1 the contents of textfile.txt;
cn.Execute "INSERT INTO table1 SELECT Field1, Field2,... FROM " & _
"[Text;Database=" & PathtoTextFile & ";HDR=YES].[theFile.txt]"
cn.Close
MsgBox "Finished Inserting into MDB"
End Sub​