November 27, 2001 at 8:55 am
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
November 27, 2001 at 9:13 am
Can you post a sample data file (a couple lines) and I'll see what we can do.
Steve Jones
November 27, 2001 at 9:17 am
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
November 27, 2001 at 10:21 am
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.
November 29, 2001 at 7:51 am
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
November 29, 2001 at 7:59 am
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
November 29, 2001 at 9:42 am
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
November 30, 2001 at 6:02 pm
You're welcome! Please visit anytime you have a question.
Andy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply