T-SQL Import CSV file- empty columns Data type mismatch

SSinhg 286 Reputation points
2021-08-05T09:32:52.723+00:00

Hi All

I am import csv file using TSQL Code

    INSERT INTO [TableName]
    SELECT *  FROM openrowset('MSDASQL'
        ,'Driver={Microsoft Access Text Driver (*.txt, *.csv)}'
        ,'select * from \\SupermanFile.csv')

Using some env variable SQL decides how many rows to see before deciding the columns data type...
Can someone pls point me where should I look for this information?

Why bz - a few rows are null after importing... that could be bz sql column might have integers initially and later it might have text.

Thanks

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Yitzhak Khabinsky 25,201 Reputation points
    2021-08-05T16:26:08.027+00:00

    Hi @SwatantraSingh-2674,

    Here is a conceptual example for you.
    This way you will have a full control on data types, header lines, errors, etc.

    T-SQL

    SELECT *
    FROM  OPENROWSET(BULK 'e:\Temp\Quark.csv'
       , FORMATFILE = 'e:\Temp\Quark.xml'  
       , ERRORFILE = 'e:\Temp\Quark.err'
       , FIRSTROW = 2 -- real data starts on the 2nd row
       , MAXERRORS = 100
       ) AS tbl;
    

    Quark.csv

    "ID"|"Name"|"Color"|"LogDate"|"Unknown"
    41|Orange|Orange|2018-09-09 16:41:02.000|
    42|Cherry, Banana|Red,Yellow||
    43|Apple|Yellow|2017-09-09 16:41:02.000|
    

    Quark.xml

    <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
       <RECORD>
          <FIELD ID="1" xsi:type="CharTerm" TERMINATOR='|' MAX_LENGTH="70"/>
          <FIELD ID="2" xsi:type="CharTerm" TERMINATOR='|' MAX_LENGTH="70" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
          <FIELD ID="3" xsi:type="CharTerm" TERMINATOR='|' MAX_LENGTH="70" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
          <FIELD ID="4" xsi:type="CharTerm" TERMINATOR='|' MAX_LENGTH="70" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
          <FIELD ID="5" xsi:type="CharTerm" TERMINATOR='\r\n' MAX_LENGTH="70" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
       </RECORD>
       <ROW>
          <COLUMN SOURCE="1" NAME="ID" xsi:type="SQLVARYCHAR"/>
          <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLVARYCHAR"/>
          <COLUMN SOURCE="3" NAME="Color" xsi:type="SQLVARYCHAR"/>
          <COLUMN SOURCE="4" NAME="LogDate" xsi:type="SQLVARYCHAR"/>
          <COLUMN SOURCE="5" NAME="Unknown" xsi:type="SQLVARYCHAR"/>
       </ROW>
    </BCPFORMAT>
    
    0 comments No comments

  2. MelissaMa-MSFT 24,176 Reputation points
    2021-08-06T06:45:06.04+00:00

    Hi @SwatantraSingh-2674,

    Welcome to Microsoft Q&A!

    Using some env variable SQL decides how many rows to see before deciding the columns data type...

    Could you please provide more details about above? What did you refer 'env variable'?

    that could be bz sql column might have integers initially and later it might have text

    If you would like to check this one, you could refer below and check whether it is a little helpful.

    Suppose the ID column was the column you referred.

     SELECT *    
        into #temp  
        FROM openrowset('MSDASQL'  
                ,'Driver={Microsoft Access Text Driver (*.txt, *.csv)}'  
                ,'select * from \\SupermanFile.csv')  
        select count(*) total,sum(case when ISNUMERIC(ID)=0 then 1 else 0 end) not_integer from #temp  
    

    You could get the total count of rows and the count of rows whose value contains text.

    If the latter is larger, you could consider to change the data type of ID column from int to varchar.

    Best regards,
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments