How To Create a Custom Text File Import Routine
If you need leading and/or trailing spaces to be preserved or need to readthe data in a situation where Jet's Text ISAM driver cannot be used, youwill need a custom import routine.
The following code is a template you can use to build a custom importroutine.
WARNING: Your use or modification of the CODE provided in this article isat your own risk. Microsoft provides this CODE "as is" without warranty ofany kind, either expressed or implied, including but not limited to theimplied warranties of merchantability and/or fitness for a particularpurpose. Microsoft does not support modifications of the CODE to suitspecific customer requirements.
- Using Notepad, create a text file with the following data:
1, Description 1 ,1,100.00,3/1/1998
2, Description 2 ,2,200.00,3/2/1998
- Save the file as c:\test.txt.
- In Visual Basic, create a new project and add a reference for Microsoft DAO 3.5 Object Library. In Access, open the Northwind.MDB database.
- Create a new form with a CommandButton (Command1) and the following code:
Private Sub Command1_Click () Dim F As Long, sLine As String, A(0 To 4) As String Dim db As Database, rs As Recordset F = FreeFile Open "c:\test.txt" For Input As F ' uncomment following line if you want to skip field headings ' Line Input #F, sLine Set db = CurrentDb ' Access only Set db = DBEngine(0).OpenDatabase("biblio.mdb") ' Visual Basic On Error Resume Next db.Execute "DROP TABLE TestImport" On Error Goto 0 db.Execute "CREATE TABLE TestImport (ID LONG, [Desc] TEXT (50), " _ & "Qty LONG, Cost CURRENCY, OrdDate DATETIME)" Set rs = db.OpenRecordset("TestImport", dbOpenTable) Do While Not EOF(F) Line Input #F, sLine ParseToArray sLine, A() rs.AddNew rs(0) = Val(A(0)) rs(1) = A(1) rs(2) = Val(A(2)) rs(3) = Val(A(3)) rs(4) = CDate(A(4)) rs.Update Loop rs.Close db.Close Close #F End Sub Sub ParseToArray(sLine As String, A() As String) Dim P As Long, LastPos As Long, I As Long P = InStr(sLine, ",") Do While P A(I) = Mid$(sLine, LastPos + 1, P - LastPos - 1) LastPos = P I = I + 1 P = InStr(LastPos + 1, sLine, ",", vbBinaryCompare) Loop A(I) = Mid$(sLine, LastPos + 1) End Sub
- Run the project/form and click the CommandButton to import the data.
- Examine the contents of the TestImport table and note that the Desc column has both leading and trailing spaces preserved and that other fields have been correctly converted to the appropriate data type.
NOTE: In Visual Basic, you can use the VISDATA sample project to examine the contents of the table.
Article ID: 183638 - Last Review: 12/05/2015 08:32:21 - Revision: 1.2
- kbnosurvey kbarchive kbhowto KB183638