Uploading textfile

Do U need
Simple Solution
   read Ur file in excel
   Modify the sheet to have VALID FieldNames on 1'st row
  then in access Get external data -> import
choose excel type and Ur excel file

that's it

OR
the other Hard Way needs that comma sep file has the field names as 1st line , type as second line


I mean Like
FirstName   ,    Last Name , Age
Text(25)    ,    Text(25)  , Integer

Have you name-column(same name with name in table) in txt-file?

Public Sub TransferData(FileName As String, MDBFileName As String, TableName As String)
Dim a As Long
On Error GoTo RepeatEdit
Dim FieldsTransfer As Variant
Dim DataTransfer As Variant
Dim DataLine As String
Dim FieldsSQL As String
Dim I As Long
Dim QuantityFields As Long
Dim Ind As Long
Dim dbs As Database
Dim qdf As QueryDef
Dim FN As Integer
 
FN = FreeFile
Set dbs = OpenDatabase(MDBFileName, True)

Set qdf = dbs.CreateQueryDef("")
Open FileName For Input Access Read As #FN
Line Input #FN, DataLine
QuantityFields = 0
FieldsTransfer = Split(DataLine, ",")
FieldsSQL = "INSERT INTO " + TableName + " ([" + FieldsTransfer(QuantityFields) + "]"
QuantityFields = QuantityFields + 1
Do
 FieldsSQL = FieldsSQL + ",[" + FieldsTransfer(QuantityFields) + "]"
 QuantityFields = QuantityFields + 1
Loop Until UBound(FieldsTransfer) = (QuantityFields - 1)
FieldsSQL = FieldsSQL + ")  Values ("
Line Input #FN, DataLine
Do While Not EOF(FN)
  FieldsTransfer = Split(DataLine, ",")
  DataTransfer = FieldsSQL
  For I = 0 To QuantityFields - 1
    DataTransfer = DataTransfer + "'" + LTrim(FieldsTransfer(I)) + "'"
    If (I <> (QuantityFields - 1)) Then
        DataTransfer = DataTransfer + ","
      Else
        DataTransfer = DataTransfer + ");"
     End If
  Next I
 
  qdf.SQL = DataTransfer
  qdf.Execute

  Line Input #FN, DataLine
Loop

Close #FN
OutFromFun:
qdf.Close
dbs.Close
Exit Sub
RepeatEdit:
a = MsgBox("Error-" & Err & "(" & Err.Description & "). Exit?", vbOKCancel, "Attention")
If (a = vbCancel) Then Resume

GoTo OutFromFun
End Sub


Comments:
FileName - with path
MDBFileName - with path

Format Txt-file
Field1,Field2,.....
1,2,...
2,4,...

Structure table-file
Field1,Field2,..... - same field with txt-file

This SUB use DAO

strFile = "d:\mydir\myfile.csv"
DoCmd.TransferText acImportDelim, , "TableName", strFile, False

The last argument is True is first row of file is column names.  The arguement I left empty is for a file import specification if you have one but it is not need here.  TableName is your table name and it is in quotes.  I pass the name of the import file as a string variable but if you rather it can be included in the DoCmd.

Jack

Hi emem,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Split points between: johnczimm and jack49a

emem, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer

Per recommendation, force-accepted.

jack49a: points for you at https://www.experts-exchange.com/questions/20342703/For-jack49a-re-20184825.html

Netminder
CS Moderator