Solved

Import selected text file records into Access

Posted on 2014-02-24
6
843 Views
Last Modified: 2014-03-26
I need to import into an Access table selected segments of selected data records that are saved in a text file.The text file has a lot of garbage records and data within each record not useful to me that I need to skip over.

In plain English, this is what I need to accomplish:

1. Read the text file from top to bottom
2. If bytes 1 thru 9 = 'Marketing', then import bytes 1 thru 9 of that record into Field1 of the table and bytes 30 thru 40 of the same record into Field2 of the table. Otherwise skip the record.

Thank you.
0
Comment
Question by:dbfromnewjersey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 167 total points
ID: 39883636
Often the best way is to import the file into a temporary or "staging" table, and then perform your data validation using SQL and VBA. Once you've validated the records, you can then move them over to the live tables.

If this is a one-time deal, then you can just use the Access interface to import the Text file.  I'm not sure what sort of table structure you'd end up with - that depends on what sort of file you're working with, and how you import it - but if the first Field of your Staging table is the one that contains the "Market" word, then you could just do this:

Currentdb.Execute("INSERT INTO YourLiveTable(Field1, Field2, Field3) (SELECT Col1, Col2, Col3 FROM YourStagingTable WHERE Col1='Market'")

Of course, how you do this depends entirely on the file structure and such. If you could post an example file, perhaps we could provide more information.
0
 
LVL 36

Assisted Solution

by:PatHartman
PatHartman earned 166 total points
ID: 39883808
All the examples I have are way too complicated to post here.  Look for the syntax for -
Open .... For Input
and
Line Input #FileNum, strLine

To see how to read a text file.

Basically, you have to get a FileNum, Open the file with Open, and then read each record with Line Input.  Beyond that, you're on your own and it depends on the format how you have to process the records.
0
 

Author Comment

by:dbfromnewjersey
ID: 39883868
I'll try to post an example of the type of text files I deal with in a bit.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:dbfromnewjersey
ID: 39883970
By the way, I had already tried importing the file directly into a new Access table for it to act as a staging area for further processing. The problem I had with that method is that in my source file, there is a debit column and a credit column.  When I import the text file, both of those columns wind up being combined into one field in Access instead of staying separate.  I can import the file into Excel first but I wind up having to move the verticle lines around manually in the Text Import Wizard window to get them where I need to be. I'm trying to automate the process as much as possible so that I don't always have to deal with manually moving those lines around before importing.
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 39884050
There is no easy way to import a file with inconsistent row formats which is what it sounds like you have.  If you have debit and credit fields on different rows, your code will need to combine them into one row as you are looping through the import file.
0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 167 total points
ID: 39884192
Sometimes you can clean up a file if you import it into Word and use Word's Search and Replace feature to do the cleanup -- then re-save it as a clean text file for import into Access.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

735 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