NEED HELP TO LOAD DATA...
-
Hi everyone, can someone help me??
I need to load a file that is File.scp, it is just plain text, the field terminator are ','. I need to load it into a table, but I can not use DTS, so I found something that is bcp or bulk insert but it gives me an error:
With BCP
bcp Integra.dbo.temp_Reporte_shpmnt in F:\Cerveceria\adw_39_FPP\Sp\MasterPlanning\enterprise\scp\data\solver_data\op_data\Shipment.sol /U user /P password /S server /c /t","
the error is:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.
and when I use BULK INSERT:
BULK INSERT Integra.dbo.temp_Reporte_shpmnt
FROM 'F:\Cerveceria\adw_39_FPP\Sp\MasterPlanning\enterprise\scp\data\solver_data\op_data\Shipment.sol'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '|\n'
)
but it gives me this error:
Server: Msg 4860, Level 16, State 1, Line 1
Could not bulk insert. File 'F:\Cerveceria\adw_39_FPP\Sp\MasterPlanning\enterprise\scp\data\solver_data\op_data\Shipment.sol'
I thought that it was the extension of the file and changed it to C:\shipment.txt
but it gives me the same error.
Could someone help me?? please???
Do you have any other ideas to load the information????
Thanks a lot to all
Ana
Ana
Ana -
Can you post a sample data file (a couple lines) and I'll see what we can do.
Steve Jones
-
Thanks Steve. Here is the sample:
Mosto_Boh,,P_Mty,ELABMTY1,,1997-09-01,3600,3600,3600,0,I,,,100.000000,0.000000,0,0.000000
Mosto_Boh,,P_Mty,ELABMTY1,,1997-09-15,3200,3200,3200,0,I,,,100.000000,0.000000,0,0.000000
Mosto_CB,,P_Mty,ELABMTY1,,1997-09-01,60600,60600,60600,0,I,,,100.000000,0.000000,0,0.000000
Mosto_CB,,P_Mty,ELABMTY1,,1997-09-08,70700,70700,70700,0,I,,,100.000000,0.000000,0,0.000000
Mosto_CB,,P_Mty,ELABMTY1,,1997-09-15,10100,10100,10100,0,I,,,100.000000,0.000000,0,0.000000
Mosto_Light,,P_Mty,ELABMTY1,,1997-09-01,3400,3400,3400,0,I,,,100.000000,0.000000,0,0.000000
Mosto_Light,,P_Mty,ELABMTY1,,1997-09-08,3400,3400,3400,0,I,,,100.000000,0.000000,0,0.000000
Mosto_Sol,,P_Gdl,ELABMTY1,,1997-09-01,1500,1500,1500,0,I,,,100.000000,0.000000,0,0.000000
Mosto_Sol,,P_Gdl,ELABMTY1,,1997-09-08,1500,1500,1500,0,I,,,100.000000,0.000000,0,0.000000
Mosto_Sol,,P_Mty,ELABMTY1,,1997-09-01,7800,7800,7800,0,I,,,100.000000,0.000000,0,0.000000
Mosto_Sol,,P_Mty,ELABMTY1,,1997-09-08,18200,18200,18200,0,I,,,100.000000,0.000000,0,0.000000
22,P_Mty,280,CAMION1,1997-09-01,1997-09-01,2794,2794,2794,0,A,P,,100.000000,1.000000,0,0.000000
22,P_Mty,340,CAMION1,1997-09-01,1997-09-01,969,969,969,0,A,P,,100.000000,1.000000,0,0.000000
22,P_Mty,350,CAMION1,1997-09-01,1997-09-01,247,247,247,0,A,P,,100.000000,1.000000,0,0.000000
22,P_Mty,390,CAMION1,1997-09-01,1997-09-01,2538,2538,2538,0,A,P,,100.000000,1.000000,0,0.000000
22,P_Mty,400,CAMION1,1997-09-01,1997-09-01,1400,1400,1400,0,A,P,,100.000000,1.000000,0,0.000000
Ana
Ana -
bcp is a command line utility so you need to use xp_cmdshell
master..xp_cmdshell
'bcp Integra.dbo.temp_Reporte_shpmnt in F:\Cerveceria\adw_39_FPP\Sp\MasterPlanning\enterprise\scp\data\solver_data\op_data\Shipment.sol /U user /P password /S server /c /t","'
for the bulk insert it usually gives an eof error or bad terminator.
I don't see any | characters in the sample data so maybe you just need /n (which it will default to anyway).
It is always worth starting off with a smaller file with fewer columns to test and to set batch size to 1 to find which row an error is on.
Cursors never.
DTS - only when needed and never to control. -
Hi, someone told me that the user need a sysadmin profiles or soomething like that, to do a bcp or a bulk insert.
Thanks
Ana
Ana -
Hi, I try what you told me:
master..xp_cmdshell
''bcp Integra.dbo.temp_Reporte_shpmnt in F:\Cerveceria\adw_39_FPP\Sp\MasterPlanning\enterprise\scp\data\solver_data\op_data\Shipment.sol /U user /P password /S server /c /t","''
but I received this message...
output
---------------------------------------------------------------------------------
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file
(2 row(s) affected)
What does it means??
Thanks a lot to all
Ana
Ana -
Hi to all, guess what. I was trying the bcp, with the example that nigelrivett send. First I was having errors, I was documenting them but finally I found that I have to put the path of the server and not the path from my machine, and it works
Thanks to all, it feels good whe you can do something. Thaks for your help
Ana
Ana -
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply