Solved

Direct access to a .csv file from within Access

Posted on 2004-10-13
7
198 Views
Last Modified: 2009-07-29
Is it possible to directly access a .csv file from within Access (2000/2003) without having to import it first into an Access table. If so how can it be done and is it then possible to test the format (text, date, numeric etc) of each field.

I have actually imported .csv records as  single Text records and written code to loop through the Text field using the delimiters to isolate each field and to test if the field is null but I would like to also check the format of the field if the field is not null.  
0
Comment
Question by:Adlerm
  • 4
  • 3
7 Comments
 
LVL 12

Expert Comment

by:pique_tech
ID: 12304352
You can do this through File -> Get External Data -> Link Tables, browse to the directory where your file is located, select file type of Text Files, and follow the wizard.

I have tested this on a limited basis and it seems to work OK.  Haven't done anything but "read" data, but I don't think you can insert new data or update existing data into the text file.
0
 
LVL 12

Expert Comment

by:pique_tech
ID: 12304363
I'm not sure I understand what you mean by "test the format", but you can set it in the wizard and you can even save your wizard settings through the Advanced tab on the wizard (in the lower left corner).
0
 

Author Comment

by:Adlerm
ID: 12304421
Hi pique tech

                 What I mean by "test the format" is that if I expect a field to be say text, date, numeric etc then how can I check if the information in the .csv field conforms to the expected text/date/numeric format
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 12

Expert Comment

by:pique_tech
ID: 12304452
Hm...not exactly then.  The wizard will read the data in each column and will select the most restrictive datatype that will accept all the values, but it cannot do exactly what you ask.

I tested this on a very small text file and not "out in the wild", so I can see your concern, but I'm not sure how you'd do exactly what you've got in mind.

What will you do with the data once you have access to it?  That might influence what process you use to validate the contents of the fields.

Perhaps you can examine how the text file is generated--do you have any control over the process that creates the text file?  Can you use that process to ensure that the datatypes are what you expect/require?
0
 

Author Comment

by:Adlerm
ID: 12304541
Hi Pique tech
           
                 My main purpose in doing this is to Edit/Validate the download data in the .csv file and stop any further processing within the Access application until the .csv file contains clean data. If I encounter any errors (null fields, incorrect field formats, fields not supplied etc) during the editing/validation phase then I write details to an Error table identifying the csv record and the field(s) that failed the editing/validation rules. I then produce an error report that the users can use to correct the raw data in the provider system (SAP) and so the whole process start over again with a new dowload csv file produced after the errors have been modified. I can test if a field is null or if the expected number of fields have not been supplied but as I said I not sure how to check the format of the field. What I'm trying to avoid is that after the .csv file has been check and is imported into an Access table that certain fields are not loaded due to conversion problems as sometimes occur with the import wizard.  
0
 
LVL 12

Accepted Solution

by:
pique_tech earned 50 total points
ID: 12304575
Gotcha.

Conceptually, what I'd suggest is this:

Use the Linked Table manager as described above, use queries to validate that the data is valid and complete, then append or reject the data based on your findings.  You should be able to do all the validation you'd need in the form of queries or at worst some basic VBA coding.
0
 

Author Comment

by:Adlerm
ID: 12304590
Pique tech

             I'll go with that - many thanks for you help.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

815 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now