Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 29th, 2006, 11:14 PM
Authorized User
 
Join Date: Apr 2006
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default Import a fixed width txt file to access

Hi,
I would like to import a fixed width text file into an Access database using VBA.
I would like to do the following:
Open the text file that contains the set of data.
Import the first record.
Assign the record to a recordset
Analyse the fields.
store the data in a table
Next
Please note that the text file is a fixed width format, and contains 5 fields.

Your help is appreciated

Thanks

Sayed
 
Old May 1st, 2006, 06:09 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You can read the text file instead of importing it. You can create a scripting.filesystemobject, then check for the file by path and name, then open the file for reading if it exists, and ReadLine. Then split() the lines into an array using "<Tab>" as the split, and analyze the fields in the array, then redim the array, and read the next line.

Does this help?

mmcdonal
 
Old May 1st, 2006, 06:12 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Forgot to mention, open a connection and recordset in the current project first, then open the text file and loop through it. If you want to store the data, do an objRS.AddNew - Update. You will need to open a dynamic recordset to do this. After you are done looping through the text file, close the connection and recordset.

HTH

mmcdonal
 
Old May 1st, 2006, 07:13 AM
Authorized User
 
Join Date: Apr 2006
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,
Thanks for responding to my query.

I am still new to VBA.

Is there any example that shows how to open a text file and assign the first record to a recordset and I will take car of the rest.

Your help is appreciated.

Thanks
Sayed


 
Old May 1st, 2006, 07:22 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Without declaring the variables, you can do something like...


'-----Code Starts-----

    Set objConn = CreateObject("ADODB.Connection")
    Set objRS = CreateObject("ADODB.Recordset")
    objConn.Open "DSN=Your DSN;" 'or Use a Provider for Jet to Access
    objRS.Open "SELECT * FROM tblYourTable", objConn, 3, 3

    Set fso = CreateObject("Scripting.FileSystemObject")
     If fso.FileExists("C:\data.txt") Then
      Set objStream = fso.OpenTextFile("C:\data.txt", 1, False, 0)
     End If

    Do While Not objStream.AtEndOfStream
      strLine = objStream.ReadLine
      YourArray = Split(strLine, " ")
        (Your analysis here)
      objRS.AddNew
      objRS("FieldName0") = YourArray(0)
      objRS("FieldName1") = YourArray(1)
      ...
      objRS.Update
    Loop

    objRS.Close
    objConn.Close

'-----Code Ends-----

Does this help?




mmcdonal
 
Old May 2nd, 2006, 06:30 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

It didn't occur to me that this was a local copy of the db. I use ADO a lot since I go out over the network etc with other types of back ends. You may want to use DAO instead of an ADO jet connection.

Any luck on this?

mmcdonal
 
Old May 2nd, 2006, 11:19 AM
Authorized User
 
Join Date: Apr 2006
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you Very much.

I will work on this and let you know.

realy appreciate your help.

Thanks
Sayed

 
Old May 2nd, 2006, 04:38 PM
Authorized User
 
Join Date: Apr 2006
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

I hope that this is my last question regarding this topic.

The text file that I am importing to Access has a determined specification.
How can I split up the array or the StrLine according the specification below?

[u]Field Name</u> [u]Start</u> [u]Width</u>
Account 1 20
First Name 21 30
Last Name 51 35
Phone 86 10

Thank you so much
Sayed



 
Old May 3rd, 2006, 06:32 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Please post a few lines from the text document, with false data, of course.

Then indicate what table cells that data needs to go into and how it will ook once it is there.

Then we can do this. No worries.

mmcdonal
 
Old May 3rd, 2006, 07:09 AM
Authorized User
 
Join Date: Apr 2006
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,
Please see below for sample data.

All what I need help with is how to wride the first line? and how to split it up into the appopriate elements?


[u]Sample Data</u>
99999999999999991 John Smith 111111112
99999999999999992 Rohan Jackson 111111113
99999999999999993 Marie Clarkson 111111114
99999999999999994 Flora Mistry 111111115

[u]Table structure</u>:
Field Name Start Width
Account 1 20
First Name 21 30
Last Name 51 35
Phone 86 10


Thanks
Sayed







Similar Threads
Thread Thread Starter Forum Replies Last Post
Importing Fixed Width .txt Document and Then Some! Diretemus Access VBA 8 March 26th, 2008 07:27 AM
Fixed Width text file to MSDE with C# mgudmund C# 2005 3 November 9th, 2007 05:09 AM
Import a Comma separated txt file to SQL table arielote C# 2 April 17th, 2006 01:08 AM
Automate import (fixed width) U.N.C.L.E. Access VBA 0 February 3rd, 2006 06:55 PM
DTS Fixed Width File Import rochak SQL Server DTS 3 September 26th, 2005 09:12 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.