Getting data from .csv file to .mdb

[Doug4Now]

I'm working with an accounting program that can export data to .csv files (or tab delimited files ie .txt). I need to import that data into an Access database so that I can then work with the data via a VB front end.

Would appreciate any suggestion re the best way to tackle this and whether it is better to have the data exported to a .csv or tab-delimited file.

Thanks, Doug.

 

[CCLINT]

thread222-375073 [/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[billy71]

Set up your table in a blank database with all the fields corresponding to the CSV file.

Use Microsoft Access to import the CSV.

Open the Database to be updated. Tables window with the Table present.

Right click in the blank space slide down to import and click.
In the box below click the down arrow and find Text Files.
Double click or click the Export button in the upper right corner .
Delimeted box is checked click next.
Comma is checked, click next.
Check in an existing Table.
Click the down arrow and select the table to receive the imported data.
click next, if the table is correct click Finish.
This table should be empty as this procedure adds data to an existing table.

 

[Doug4Now]

Thanks, for that - the only thing is I need to code it in VB as the users will not be interfacing with the database directly (and some may not even have Access installed), they will only see the data via the VB front end.

Each time they open the program it needs to compare the .csv file with the database and add any extra records to the database.

I guess what I'm looking for is a way to identify unique records in the .csv file and then read in each field.

Do you know if there is an end-of-line or end-of-record marker in a .csv file?

I realise that it is probably possible to read the text of the .csv file one character at a time but am hoping that there is a less tedious way to code it.

Thanks again, Doug.

 

[Spellman]

If you use ado or dao there is a means of opening up a recordset using a connection object which uses a text file driver. I'm not really sure how to do it cause it's been a while but it's definitely available if you had a look into it. Once the connection to the file is open you just 0pen a recordset up as you normally would. It's very neat. Mark

The key to immortality is to make a big impression in this life!!

 

[johnwm]

I suspect that neither of you have read the thread that CCLINT pointed you to.

Before posting a new question, it's usually worthwhile to:
1. Do a search on the forum, using search tool at top of page
2. Look at FAQs for the forum, using link at top of page
3. Review faq222-2244

When you've posted, check the threads that you're referred to.
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[CCLINT]

Thanks johnwm.

I think an additional note is needed to that mentioned thread:

The Text ISAM driver imports/exports data to/from a text file, into, as in the case mentioned, an MDB.

However, if the text file is in another format, such as the delimeter used, than the delimeter default settings (or any other setting) in the registry is different, then the driver needs to know what to use and how to interpet the file.

This is where a Schema.Ini is created and placed in the same folder as the Ascii file.

See thread709-334828 on how to create a Schema.ini file.

 

[Doug4Now]

Thanks for all your responses/advice - unfortunately since posting the initial question I've had to focus on a different project and have had to put the ".csv to .mdb project" on the back burner temporarily - so I have'nt had a chance implement any of the suggestions - probably in a couple of weeks I'll be able to breathe again...

Nevertheless I really appreciate the fact that you're all willing to give advice/make suggestion freely - it's great in this dog-eat-dog world that there are still people around who will help just for the sake of it.

Doug.

'... I'd give my right arm to be ambidextrous'