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
Solved

Direct access to a .csv file from within Access

Posted on 2004-10-13
7
199 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

792 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