Solved

Direct access to a .csv file from within Access

Posted on 2004-10-13
7
196 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

760 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

18 Experts available now in Live!

Get 1:1 Help Now