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