append text to table

• • Reply

  19 years ago

  by UltimateWilliam

  UltimateWilliam

  , United States

  Joined 19 years ago

  hi everyone!
  

  
  hope u could help me append this text file into a table in msaccess.
  

  
  

  
  N 044 00 DN810   T000025 09/14 11:46 00:00:10 94368220
  N 045 00 DN810   T000021 09/14 11:46 00:00:04 9645
  N 046 00 DN504   T000010 09/14 11:46 00:00:18 93744372
  N 047 00 DN201   T000034 09/14 11:46 00:01:00 98708720
  N 048 00 DN610   T000005 09/14 11:47 00:00:02 992521
  N 049 00 DN226   T000036 09/14 11:44 00:02:50 97273009148
  N 050 00 DN610   T000038 09/14 11:46 00:00:40 992524012198
  

  
  my table has these structure"
  

  
  RecType text 1
  RecNo    text 3
  CustNo text 2
  LocNo  text 5
  TrunkID text 7
  MonthDay text 5
  HrMin text 5
  DurPin text 8
  DialdNo text 30
  

  
  don't know if i'll read line by line or there's an sql statement. . . thanks!
  

  Report abuse
• • Reply

  19 years ago

  by James Crowley

  James Crowley

  London, United Kingdom

  Joined 20 years ago

  I would read it line by line, and generate an SQL statement for each of the entries.

  Report abuse
• • Reply

  19 years ago

  by CodeWarrior76

  CodeWarrior

  Calgary, CA, United States

  Joined 19 years ago

  Quote:

  
  N 044 00 DN810   T000025 09/14 11:46 00:00:10 94368220
  N 045 00 DN810   T000021 09/14 11:46 00:00:04 9645
  N 046 00 DN504   T000010 09/14 11:46 00:00:18 93744372
  N 047 00 DN201   T000034 09/14 11:46 00:01:00 98708720
  N 048 00 DN610   T000005 09/14 11:47 00:00:02 992521
  N 049 00 DN226   T000036 09/14 11:44 00:02:50 97273009148
  N 050 00 DN610   T000038 09/14 11:46 00:00:40 992524012198
  

  
  RecType text 1
  RecNo    text 3
  CustNo text 2
  LocNo  text 5
  TrunkID text 7
  MonthDay text 5
  HrMin text 5
  DurPin text 8
  DialdNo text 30
  

  
  

  
  personally I would just read it in line by line, like James mentioned, but what I would do with the line is do a Split() on it with space as the delimiter into an array.
  

  
  Then i would create 2 loops, one nested inside the other.
  

  
  Dim iCount As Integer
  Dim sArr() As String
  Dim filenumber As Integer
  Dim sLine As String
  Dim sQuery As String
  Dim cnADOConnection As ADODB.Connection
  Dim lRecsAffect As Long
  

  
  'open the file here
  'open connection to the database here
  

  
  Do Until EOF(filenumber)
     Line Input #filenumber, sLine
     sArr = Split(sLine, Space(1))
     sQuery = "INSERT INTO TableName (RecType, RecNo, CustNo, LocNo, TrunkID, MonthDay, HrMin, DurPin, DialdNo) VALUES ('"
     For iCount = LBound(sArr) To UBound(sArr)
         If iCount = UBound(sArr) Then
             sQuery = sQuery & sArr(iCount) & "')"
         Else
             sQuery = sQuery & sArr(iCount) & "', '"
         End If
     Next
     If cnADOConnection.State = 1 Then
         cnADOConnection.Execute sQuery, lRecsAffect
     Else
         cnADOConnection.open
         cnADOConnection.Execute sQuery, lRecsAffect
     End If
     'test if records were affected
  Loop
  

  
  On a side note, the nice thing about the nature of your fields is that they are all text data types.  otherwise you'd have to put a bunch of type checking in and it might be easier to put it in the database using another method.

  Report abuse
• • Reply

  19 years ago

  by UltimateWilliam

  UltimateWilliam

  , United States

  Joined 19 years ago

  i'll try them all
  

  
  thanks everyonel!!!

  Report abuse