Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Import from Access Expand / Collapse
Author
Message
Posted Monday, December 17, 2012 6:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 13, 2014 2:33 PM
Points: 117, Visits: 315
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!
Post #1397239
Posted Monday, December 17, 2012 6:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 13, 2014 2:33 PM
Points: 117, Visits: 315
I have this as a .csv file also which may be easier to handle (?)
Post #1397243
Posted Monday, December 17, 2012 7:50 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 8, 2014 4:07 PM
Points: 1,669, Visits: 2,215
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)

Internet ATM Machine
Post #1397266
Posted Monday, December 17, 2012 9:21 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:03 PM
Points: 80, Visits: 350
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
Post #1397536
Posted Monday, December 17, 2012 10:58 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 9:01 AM
Points: 774, Visits: 4,995
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.
Post #1397557
Posted Tuesday, December 18, 2012 5:18 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:30 AM
Points: 430, Visits: 963
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.




Post #1397712
Posted Tuesday, December 18, 2012 12:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 9:46 AM
Points: 20, Visits: 470
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...
Post #1397977
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse