April 11, 2005 at 11:07 am
I am using BCP to import a text file into a SQL table. The first few lines of the text file look like the following:
STATION ID,DATE ON,TIME ON,DATE OFF,TIME OFF,HOURS ON,VOL-MCF,P-B
00000101,03/21/05 ,12:30 ,03/22/05 ,09:00 ,20.50 ,11 ,14.650
00000101,03/22/05 ,09:00 ,03/23/05 ,09:00 ,24.00 ,13 ,14.650
The BCP command I am using is the following:
EXEC master..xp_cmdshell 'bcp "myDB.dbo.myTable" in "D:\Temp\download.csv" -F2 -c -t"," -S"MySQLServer" -T'
As you can see, I start with line 3.
The first "Station ID" comes in as " 00000101" (two blank spaces before the number) and the rest of them come in properly (just 8 characters).
Does anyone know why this is happening?
Thanks,
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
April 12, 2005 at 12:41 am
The option -F2 starts the copy from the second row. Try using -F3.
You should also try to use BULK INSERT and not EXEC master..xp_cmdshell :
BULK INSERT myDB.dbo.myTable from 'D:\Temp\download.csv'
WITH ( FIELDTERMINATOR = ',' , FIRSTROW = 3 )
From BOL :
-F first_row
Specifies the number of the first row to bulk copy. The default is 1, indicating the first row in the specified data file.
Bert
April 12, 2005 at 7:08 am
Duh, 20 lashes with a wet noodle on me!
I had it set to row 3 earlier. Apparently at some time I changed it to row 2 and then I never looked back.
Thanks for pointing it out. Sometimes a fresh set of eyes is all that is needed.
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply