Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

process text file in access 2003

Posted on 2011-03-15
8
Medium Priority
?
262 Views
Last Modified: 2012-05-11
I am writing an access 2003 application that needs to process data in a text file that is sent from a vendor each month.  (I have attached a sample with names changed to protect the innocent)

The text file is tab delimited and with have the same format column wise.   The row count will be different each month

My first thought was to import the text file and save as an excel spreadsheet but wasn't sure of the best way to do that.

I am open to other ideas as well
testdata.txt
0
Comment
Question by:johnnyg123
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35137778
That file does not appear to be tab delimited.
0
 

Author Comment

by:johnnyg123
ID: 35137868
Sorry .... I was working with multiple vendor files



you are correct....this file is not tab delimited.  


When I was manually opening with excel I was allowing the conversion wizard to use fixed width

The wizard was coming up with 9 columns

There will be a little manual intervention and the first 2 rows and the last row will be deleted
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35139849
So are you saying that the file you posted is the raw data?

You always should endeavor to avoid any unnecessary "conversion" steps and try to import the file directly to Access...

So if it were me, I would ask that whatever system creates these files be structured such that the first row and the totals are stripped out, and the file be saved as a true delimited file before hand.

Many people just say "This is the way I get the file".
But many times it can be modified at the source to be whatever you request, .
It never hurts to ask... (especially it save you from *Forever* performing the intermediate step of converting it to Excel)

That being said you find an expert here willing to help you create an automated system in Excel to clean the file.

JeffCoachman

0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35139907
I totally agree with Jeff but, even if you can't get the output changed, I don't see the point in involving Excel. You can use straight File I/O to create a new textfile without the first 2 and last lines, then import directly into Access with a predetermined import spec, unless my memory of Access is completely failing me - and I'm sure someone will correct me if so :)
Rory
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35140531
rorya

Hey buddy!
;-)

The Access import Wiz will ask if "First row contains field names," but won't ask if the second row contains them...
;-)
I also don't know of an easy way to remove the totals in the last row (by using the Wiz and/or an Import Spec).
(Although I guess in the Big picture it may not be such a big deal to leave the totals in, ...it just makes for an imperfect import)

Then there is still the issue of converting the file, as it stands, to a real Tab delimited file.

That's why I thought one of the Excel Experts might be able to come up with something to automate the Cleaning of this data.
(...that is unless the file can be manipulated at the source)

...making an interface to select the file would be another issue...

;-)

Jeff
0
 

Author Comment

by:johnnyg123
ID: 35140745
Thanks for all the posts ... really appreciate it

I totally agree that it would be best to have the file in a clean format.

Most of the time I am successful of using my powers of persuasion to accomplish this.

However, as I mentioned earlier this is a file that comes from an outside vendor and they are unwilling to change the format.  (I diid make a comment to the effect of perhaps we should switch vendors but this fell on deaf ears :-))

So....I am stuck with the file format

Since my last post I have started down the path of just reading the text file using the file scripting object and pulling the data out that way in conjunction with the some string functions.

So far so good.

I did not mention earlier that one of the complaints is that the process currently in place is too manual

even the use of running through wizards is being challenged.  just exploring different options
0
 
LVL 40

Accepted Solution

by:
als315 earned 2000 total points
ID: 35140959
I've modified this example:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26701701.html#34428530
You can test it. All fields are imported as text, you can convert it during import or later.
You can also read date from first string and store it in your table, if you need it.
TextImport26887661.zip
0
 

Author Comment

by:johnnyg123
ID: 35148378
Thanks again for all the posts!

My initial thought was to use excel but as Rory correctly pointed out that thought was not a good one.

Thanks to als315 for providing a solution that is indeed the better approach
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

972 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