Link to home
Start Free TrialLog in
Avatar of LarryDumoulin
LarryDumoulin

asked on

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

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.
Avatar of Chris Mangus
Chris Mangus
Flag of United States of America image

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.
Avatar of LarryDumoulin
LarryDumoulin

ASKER

Sounds great, but I've never used a DTS package, can you be a little more specific?
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.
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?
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

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

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???
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.
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"?
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.
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 :)
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.
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?
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.
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
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?
ASKER CERTIFIED SOLUTION
Avatar of Chris Mangus
Chris Mangus
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial