December 17, 2012 at 6:46 am
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!
December 17, 2012 at 6:55 am
I have this as a .csv file also which may be easier to handle (?)
December 17, 2012 at 7:50 am
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 17, 2012 at 9:21 pm
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
December 17, 2012 at 10:58 pm
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.
December 18, 2012 at 5:18 am
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.
December 18, 2012 at 12:13 pm
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...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply