 |
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
|
|
|

April 29th, 2006, 11:14 PM
|
Authorized User
|
|
Join Date: Apr 2006
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

May 1st, 2006, 06:09 AM
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
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
|

May 1st, 2006, 06:12 AM
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
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
|

May 1st, 2006, 07:13 AM
|
Authorized User
|
|
Join Date: Apr 2006
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

May 1st, 2006, 07:22 AM
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
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
|

May 2nd, 2006, 06:30 AM
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
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
|

May 2nd, 2006, 11:19 AM
|
Authorized User
|
|
Join Date: Apr 2006
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Thank you Very much.
I will work on this and let you know.
realy appreciate your help.
Thanks
Sayed
|

May 2nd, 2006, 04:38 PM
|
Authorized User
|
|
Join Date: Apr 2006
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

May 3rd, 2006, 06:32 AM
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
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
|

May 3rd, 2006, 07:09 AM
|
Authorized User
|
|
Join Date: Apr 2006
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
 |