none
Bulk Insert failure when inserting Yahoo Quotes RRS feed

  • Question

  • Hi,

    I’m creating a procedure to download Yahoo Quotes to a table.

    This works correctly when I retrieve 1 quote (1 row of data) such as

    SET @URL ='http://finance.yahoo.com/d/quotes.csv?s=BHP.AX&f=sohgl1vd1t1'

    Data from .csv

    "BHP.AX",26.30,26.49,26.22,26.45,11132295,"7/31/2015","4:10pm"

    It fails when I add a 2<sup>nd</sup> quote (2 rows of data) such as

    SET @URL ='http://finance.yahoo.com/d/quotes.csv?s=BHP.AX,CSL.AX&f=sohgl1vd1t1'

    Data from .csv

    "BHP.AX",26.30,26.49,26.22,26.45,11132295,"7/31/2015","4:10pm"

    "CSL.AX",97.79,99.33,96.50,98.96,3012714,"7/31/2015","4:11pm"

    The error message is

    Msg 4864, Level 16, State 1, Line 1

    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 8 (LastTradeTime).

    When I inspect the csv file there is data for 2 quotes, but it seems the bulk insert fails with 2 rows of data and nothing is inserted into my table. What could be causing the issue when there is 2 rows of data?

    Here’s the code in full

    Delete From tbl_Quotes

    -- multiple currencies

    DECLARE @cmd      NVARCHAR(250);

    DECLARE @tOutput  TABLE(data NVARCHAR(50));

    DECLARE @URL      NVARCHAR(200);

    DECLARE @file     NVARCHAR(200);

    -- SET UP the variables

    --SET @URL ='http://finance.yahoo.com/d/quotes.csv?s=BHP.AX,CSL.AX&f=sohgl1vd1t1'

    SET @URL ='http://finance.yahoo.com/d/quotes.csv?s=BHP.AX&f=sohgl1vd1t1'

    SET @file='M:\Data\ASX\SQL\Quote\quote.csv'

    SET @cmd ='powershell "(new-object System.Net.WebClient).DownloadFile('''+@URL+''','''+@file+''')"'

    -- Download the Data

    EXEC master.dbo.xp_cmdshell @cmd, no_output

    SET @cmd ='BULK INSERT tbl_Quotes FROM '''+@file+''' WITH (FIELDTERMINATOR='','', ROWTERMINATOR = ''\n'')'

    EXEC(@cmd)

    SELECT * FROM tbl_Quotes

    Saturday, August 1, 2015 1:26 AM

Answers

  • To load that file you need to use a format files, because else the quotes will get in the way. And then I'm talking about the quote characters in the file, not the stock quotes.

    The format file should look something like this:

    9.0
    9
    1 SQLCHAR   0 0   "\""     0  ""   ""
    2 SQLCHAR   0 0   "\","    1  col1 Latin1_General_CS_AS
    3 SQLCHAR   0 0   ","      2  col2 Latin1_General_CS_AS
    4 SQLCHAR   0 0   ","      3  col3 Latin1_General_CS_AS
    5 SQLCHAR   0 0   ","      4  col4 Latin1_General_CS_AS
    6 SQLCHAR   0 0   ","      5  col5 Latin1_General_CS_AS
    7 SQLCHAR   0 0   ",\""    6  col6 Latin1_General_CS_AS
    8 SQLCHAR   0 0   "\",\""  7  col7 Latin1_General_CS_AS
    9 SQLCHAR   0 0   "\"\r\n" 8  col8 Latin1_General_CS_AS

    This format file defines nine fields, while your file only seem to have eight field. The extra field is the first, which is an empty field terminated by the leading quote. This field is not loaded which is testified by the 0 in the sixith column; the 0 means "don't load".

    I did not actually test the file, so you may have to fine-tune the file.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by bassmann2 Saturday, August 8, 2015 12:39 PM
    Saturday, August 1, 2015 8:34 AM