Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Importing a fixed width text file into a access table

Posted on 2012-09-20
5
Medium Priority
?
457 Views
Last Modified: 2012-09-24
I need to import a fixed width text file with a record length 249 characters into a access table with a data type of text and a lenght of 249. I need to keep all the spaces ithat are in the text file. Is there a way this can be done. Thanks
0
Comment
Question by:dfacemire
  • 2
  • 2
5 Comments
 
LVL 40

Expert Comment

by:als315
ID: 38418524
Can you show sample from this file? Is there only one column?
0
 

Author Comment

by:dfacemire
ID: 38418559
UTBank.txt fixed width length 1 to 249

That you for the quick response. I've attached the text file that gets imported. Yes its just 1 column but there are multiple field with in the 249 characters. The access table is one column. Thanks
UTbank.txt
0
 
LVL 40

Accepted Solution

by:
als315 earned 1000 total points
ID: 38419143
You can manually set line width in import master (Press button Advanced) :
import masterDelete all lines except first and set width to 255:
import
0
 
LVL 26

Assisted Solution

by:jerryb30
jerryb30 earned 1000 total points
ID: 38419189
In my Access 2010, white space at end of line gets truncated. If I do this in a function:
Function gettext()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("table2") 'your destination table. Field must be memo data type


Dim vstring As String
Open ".\ff.txt" For Input As #1 'full path and name of source file
Do While Not (EOF(1))
Line Input #1, vstring
Debug.Print Len(vstring) & " " & vstring
With rs
.AddNew
rs!newfield = vstring 'newfield is destination field name
.Update
End With
Loop
Close #1
End Function
all lines are 249 characters.
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 38419221
error in comment. Destination field can be text data type, length 255
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Implementing simple internal controls in the Microsoft Access application.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question