[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to Import a .CSV File into an existing .ADP table on SQL Server 2000 with ADO 2.8

Posted on 2006-05-10
19
Medium Priority
?
903 Views
Last Modified: 2008-01-09
I have an .ADP project that is the front-end for my database which sits on an SQL 2000 Server.  I need to import a .CSV file into an existing table using ADO 2.8 and Provider "Microsoft.Access.OLEDB.10.0"

I'm sure there is a way to read the .CSV file in ADO and simply add each record to my table, but for th elife of me I cannot figure out how.  My .CSV file contains less than 500 records, but needs to be imported on a weekly basis, so I want to automate it the process.

Any help that you could offer would be greatly appreciated :)

Larry D.
0
Comment
Question by:LarryDumoulin
  • 8
  • 7
  • 2
17 Comments
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 16653523
Why not have your application call a DTS package that would handle the .CSV import?  You'd get far more robust error and data handling.  You could also avoid a lot of VBA programming.
0
 

Author Comment

by:LarryDumoulin
ID: 16653569
Sounds great, but I've never used a DTS package, can you be a little more specific?
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 16653690
Data Transformation Services (DTS) is a feature of SQL Server that allows you to extract, transform, and load data from disparate sources.

In your case, you would design a DTS package in SQL Server that would handle the import of the data from the .CSV file.  There is even an import data wizard in SQL Server that would build the basic DTS package for you.  Importing .CSV data is a fairly routine task for DTS.

Once you've built and tested the package and are satisfied with the results you can use VBA from within your .ADP project to execute the package.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:LarryDumoulin
ID: 16654153
ok, I think I get it, but I am a little lost here, is there not a reference somewhere that can walk me through this step by step?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16656981
I believe with a little VBA, u can use DoCmd.TransferSpreadsheet
e.g.

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tablename", "filename", True, "range"


True refers to whether spreadsheet has headers or not
the "range" is optional, allows u to import a specific range

acSpreadsheetTypeExcel9 refers to what version office u have

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16656995
If the spreadsheet does not match the table, how about importing into a temp table
then use ADO to read the temp table and insert into the proper table.

If u want to bypass the import into temp table, u can create a temporary link.Basically link the excel spreadsheet and treat as a table
then use ADO to read this linked table and write to proper table

for this, u simply use   acLink   instead of   acImport

0
 

Author Comment

by:LarryDumoulin
ID: 16661601
The TrasnsferSpreadsheet is not very stable running on the SQL Server, tried that, it sucks actually.

I have now successfully built and run a DTS Package directly on the SQL Server using Enterprise Manager.  Now my problem is that I have to get this "package" onto my MSDE version of this project so I can fine tune all of the other things that run after the package is run.  So, my question now is how do I get this DTS Package onto my MSDE version??

Any ideas???
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 16662185
You can register your MSDE instance in Enterprise Manager.  Then, open the DTS package on your main server in design view.  Click on Package and Save As and choose to save it to your MSDE instance.  Then, open the DTS package on your MSDE instance through Enterprise Manager and fine tune it to your needs.
0
 

Author Comment

by:LarryDumoulin
ID: 16662617
I'm sorry to appear like such a dunce, but this is my first real go around with SQL Enterprise Manager, how do you register another "instance"?
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 16662692
LarryDumoulin,

Not a problem!

From within Enterprise Manager, right click on your SQL Server Group in the navigation tree in the left pane and choose New SQL Server Registration.  This launches a wizard that will let you register a new SQL Server in your Enterprise Manager.  If you don't see your MSDE instance name in the Available Servers list you can manually type it in.  If your MSDE instance is a named instance you must supply the fully qualified name.  If you've installed just a default instance only the server name is required.  The wizard will also give you a choice of registering the server with Windows or SQL authentication.  In many cases, Windows authentication is sufficient.
0
 

Author Comment

by:LarryDumoulin
ID: 16662952
Excellent, so far so good.... that works ok.  I should have clarified my request - I need to take the DTS Package I created on my SQL Server, and move it to another PC that only has MSDE, and is not at all connected to the main SQL Server.  I've read yuo cannot develop a DTS Package on MSDE, but you can run it, so I'm essentially trying to figure out how to do that.

Thanks you soooooooo much, you're really helping me out here :)
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 16663119
If both PC's are part of the same domain, and you can register both SQL Servers in Enterprise Manager then the technique I described above works to copy the package from one server to another.

If you have an MSDE instance registered in your Enterprise Manager then you can develop DTS packages on there.  Typically, people who run MSDE don't have Enterprise Manager and then yes, you can't really develop DTS packages on MSDE.

On your main SQL Server, all you need to do is open the DTS package in design view.  Click on Package and Save As and choose to save it to your MSDE server / PC.  Then, you can open and edit the DTS package directly on your MSDE instance through Enterprise Manager.
0
 

Author Comment

by:LarryDumoulin
ID: 16663411
And therein lies my problem - they are not on the same domain, and therefore my MSDE PC is not accessible to the PC I have with Enterprise Manager......

Maybe DTS is not the way to go here, I'm just trying to import a .csv file to my Access ADP project that which is connected to the SQL Server.  I've tried TransferSpreadsheet and TransferText with limited success, and someone in here pointed me to DTS, but I'm starting to think that this may be too complicated for what I need anyway.

The CSV file I import chages each month, and is in fact a different file(name) each month and DTS seems rather cumbersome to work with different file names, or am I imagining that?

Anyway, I am getting a little frustrated, and I feel badly for taking up so much of your time, but I am really lost with this one.  Everything other part of my project works great, but the actual importing of the CSV file is proving to be a real challenge.  Is there not a way I can do this import with ADO, like opening the CSV as a recordset and reading through it one record at a time?  Oh by the way, each file only has a few hundred records, so it's not a huge file, so if I have to crawl through one record at a time, it's no big issue......

Anyway, sorry for babbling, but as I said I am dying here, hahaha     Any thoughts on doing this a different way, as in ADO?
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 16663580
It was me who pointed you toward DTS.  For any bulk data transfer of data into and out of SQL Server it really is the best way to go.

You can install SQL client tools on your MSDE PC and thereby have DTS designer available.  As to getting the package on the MSDE machine...when the package is open in DTS designer you can save it to a Structured Storage File.  This will create a flat file that you can move over to your MSDE machine.  Then, assuming you have the client tools on the MSDE machine you open up Enterprise Manager on that one, right click on Data Transformation Services and choose Open Package.  You can open the .DTS file you created before and then resave it locally to SQL Server.

When you say the .CSV file changes each month, is it the structure that is changing or just the file name?

I do quite a bit of work with Access and ADO but don't really know of a way to work with a .CSV file.  I did find a link at http://www.vb-helper.com/howto_ado_load_csv.html that might be helpful for you.
0
 

Author Comment

by:LarryDumoulin
ID: 16663773
I'll check that link, thanks.......

The structure is relatively constant, it's the data that changes.  Therefore the only thing that changes is the number of records, but the columns are constant.

I don't have Enterprise Manager on the MSDE box, not do I have a way to instal it, because I only have it on a lap top I've borrowed from someone else, so I could do this DTS Package.  I use Visual Studio 2005 and it works fine for what I need.  I'll likely invest in Enterprise Manager, but for the time being, it's not in my budget so I will have to do without.

I've played a bit with the DTS and I quite like the functionality, the problem is that I really don't have these tools on my home OC, so I am kind of limited in what I can do, which is why I had to borrow this lap top from one of our techies in MIS, so I could at least have access to the Enterprise Manager.  Other than that, I do my work on Visual Studio 2005 and in Access - and other than this DTS issue, all has been good.

Thanks for all you rhelp - I'll chekc out that link later and get back to you, ok?

Larry
0
 

Author Comment

by:LarryDumoulin
ID: 16667835
How about this - I have a .CSV File that I would like to be able to open and look at as a an ADO recordset, so I could loop through each record (only 200 or so) and use the results of that loop through to add records to tabele in my project.

Is that feasible?
0
 
LVL 17

Accepted Solution

by:
Chris Mangus earned 2000 total points
ID: 16668446
The link I sent you before describes a technique that would allow you to do that very thing.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

873 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