February 3, 2011 at 7:56 am
Hi,
I am trying to import 5,22,000 rows from excel file(.xlsx) to the sql server.
When I tried to do it thru the sql server data import/export. It asked me to convert the file .xlsx to .xls firstly. When I did that, only 65,000 rows could accomodate to the sql server table.
Secondly, i tried to do SSIS package, bulk insert,
In which i got many errors for each row and a specified column:-
Bulk load data conversion error (truncation)
How can I proceed.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
February 3, 2011 at 9:56 am
So first there are a few things you can do:
1 - If you happen to have sql server 2008 versions then you can use the new import/export wizard as part of SSMS to import .xlsx files
2- My prefered method would be to convert it into a .csv file or some other delimated file and do a bulk insert.
February 3, 2011 at 10:00 am
I changed the excel file into a csv file and then ran bcp :-
I got
Starting copy....
0 rows copied.
The bcp statement i used is :-
bcp dbname.dbo.ccSales_test in "C:\CCSalesSep10_Feb11.csv" -c -T
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
February 3, 2011 at 10:15 am
bcp dbname.dbo.ccSales_test in "C:\CCSalesSep10_Feb11.CSV" -SERVERNAME -c -t, -T -F 1 -r -h "TABLOCK" -C RAW -a 10240
I get
bcp unknown option W
and then all bcp signs and symbols whcih doesn't have a W
Regards
Sushant Kumar
MCTS,MCP
February 3, 2011 at 10:20 am
I am not extremely versitile with bcp for the in direction, you can try these options instead -c -t, -T as the -c is normally expecting tab delimated the -t, makes it comma delimated. It looks like you are calling this via cmd line. If this can be place in sql server in a store procedure or something then I would suggest using the bulk insert instead http://msdn.microsoft.com/en-us/library/ms188365(v=SQL.90).aspx
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy