Solved

Direct access to a .csv file from within Access

Posted on 2004-10-13
7
200 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
[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
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server views 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 Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

730 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