SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Import from Access


Import from Access

Author
Message
briancampbellmcad
briancampbellmcad
SSC Veteran
SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 Visits: 428
I have an Access database with one single file that I want to import to my SQL Server DB... If I try in Access to create a SQL Server file it puts 255 Chars in each field... My ultimate goal is to scrub its data after I get it into SQL Server and append another file through already existing in SQL... any ideas? Thanks!
briancampbellmcad
briancampbellmcad
SSC Veteran
SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 Visits: 428
I have this as a .csv file also which may be easier to handle (?)
sgmunson
sgmunson
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7100 Visits: 4376
Ok, so what method of getting the data into SQL Server did you have in mind? There are quite a few ways to do it. You could write a VBScript with ADO code in it, and use ODBC to connect to the SQL Server. You could write similar code from within VBA for Access, and even create a form with a push button that would do the import. You could use SSMS and try the import/export wizard. You could also create an SSIS package to do it, or even open the file directly in Excel, and then import the Excel file using any of the previously mentioned methods. However, whichever method you choose, you're going to have to worry about data that doesn't fit the data type for a given column, so you might want to first verify your data in some way. Perhaps a test VBScript could help.

What we don't know is what the Access database has to do with the CSV file, or if there's even any relationship between the two, nor do we know anything about either the file or your objective in getting it into SQL Server, so we're a little shy on information. Can you please provide more detail?

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
j.miner
j.miner
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 358
It sounds like you have the data in both a csv file and in a MS Access table.

If I am wrong, please tell me.

1 - Use the import / export wizard. This is part of the client tools for SQL Server as a seperate program (icon). Also, it can be called from both SSMS or BIDS (SSDT - newest version).

2 - As for MS Access, I was once certified in it but it is not on my resume. To many people think they know it but really do not. If you import data from the CSV using the delimited wizard (forms), it sizes the fields as text (255).

This is because you did not create the table first, then append to the table.

Access pretty much can do the same task but with a little more work (linked table to SQL Server, Append Query, use functions to trim or cleanup data).

3 - Either way, I suggest you create the table in SQL Server using SSMS GUI or straight TSQL.

Aftwards, append using the tool you are most familiar with.

Good luck

John Miner
Crafty DBA
www.craftydba.com
pietlinden
pietlinden
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4949 Visits: 13194
I probably shouldn't muddy the waters, but there are two basic options:

1. a "push" from Access where you create a linked table (to the SQL Server destination) and then create a query in Access to append the data in the Access table to SQL Server and do whatever scrubbing you need to there. If it's a one-off and your have to just move the data, that's probably the easiest.

2. a "pull" from SQL Server where you use SSIS or a linked server and append query...

Which you choose will depend on how messy your data is and how much cleaning it requires.
schleep
schleep
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1028 Visits: 1328
The important point here is, it doesn't have to go from Access to your final data model in SQL Server in a single step

Import it into a staging table in SQL Server using any of the myriad methods suggested above. If you have it in .csv, BULK INSERT may be the fastest way to go. There is also the MS SQL Server Migration Assistant for Access (free from MS), although probably overkill for a single table.

Then you can clean your data before inserting it into a normalized (or not, depending on your requirements) model.



bgalway
bgalway
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 902
I use Access a lot (with SQL Server as the backend). It seems to me the simplest way is to create the table in SQL and link to it in Access..then use an Access Append query to "move" the data to the SQL table...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search