0

How do I correctly export a table from an old Access .mdb to SQL Server 2012?

In Access I do: right click on table, export as text file and use comma or tab separator. My line endings are CRLF as seen from Notepad++

Then when I try to import into my RDS SQL Server with

bcp dbname.dbo.tablename in C:\AllModels.txt -n 
    -S servername -U username -P password -b 10000

I am getting:

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]Unexpected EOF encountered in BCP data-file

help help

3
  • 1
    Use tab and try -c instead of -n Jan 13, 2015 at 17:58
  • Now I'm getting a bunch of SQLState = 22001, NativeError = 0 Error = [Microsoft][SQL Server Native Client 11.0]String data, right truncation 69 rows got copied out of 681
    – OutFall
    Jan 13, 2015 at 18:04
  • Compare the column size in your destination table(s) with the length of the data being imported - there is a mismatch somewhere. Jan 13, 2015 at 18:05

1 Answer 1

1

First, use tab delimited files unless you want to use -t to specify a different delimiter (such as a comma). By default it will accept the tab.

Next, unless you're exporting data with the -n flag, use -c to import the data.

Finally, based on your comments, it looks like there is a mis-match between the size of your table columns and the actual width of the data. That's why you're getting notified the data is being truncated. Match those two up and you'll be all set.

You can check out the MSDN page on bcp for more information on flags, etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.