Post reply

importing CSV file and data format question with query analyzer

  • June 16, 2008 at 8:15 am

    #189132

    I am new to SQL and I have 2 issues I am seeking help.

    (1) How do you import CSV file using query analyzer?

    (2) When I import data from Excel to SQL with different data type within a column, it doesnt work, with SQL Analyzer. What I mean is let's say I have some value in Numeric and some in a string. Depending on which one is first the other one get import as null. As in

    Column 1 Column2 Column3

    123 bob smith

    abc joe blow

    124 123 joe

    ...

    When I import it into SQL, I get

    Column 1 Column2 Column3

    123 bob smith

    NULL joe blow

    124 NULL joe

    How do I fix that...it's driving Bazooka...HELP!!!!!!!!!!

    thanks

  • June 16, 2008 at 10:52 am

    #829344

    For my first issue. I have tried this code in which I found on previous posted

    select *

    from OpenRowset('MSDASQL',

    'Driver={Microsoft Text Driver (*.csv)};

    DBQ=C:\myFolder\',

    'select * from myFile')

    However, I get the following error

    OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".

    Msg 7303, Level 16, State 1, Line 1

    Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

    How do I fix this?

  • September 9, 2009 at 8:08 am

    #1051136

    David,

    Based on the information you have provided, you may just want to import your data using the BULK INSERT statement with a format file. By using the format file, you can specify the data types for all columns being imported. So, for the 2 columns that have mixed data types, you can specify the column as being "Charterm" which is equivalent to a varchar. This should prevent the mixed data type columns from importing NULL values. By using the format file, you can also specify the field terminator for each field(which in your case would be the comma). A sample BULK INSERT statement:

    BULK INSERT dbo.BulkTest

    FROM 'C:\YourDataFile.csv' WITH (FORMATFILE='C:\YourFormatFile.xml');

    For more information on the BULK INSERT statement:

    http://msdn.microsoft.com/en-us/library/ms188365.aspx

    For information and syntax for format files:

    http://msdn.microsoft.com/en-us/library/ms189327.aspx

    Sample format files:

    http://msdn.microsoft.com/en-us/library/ms191234.aspx

    Bob Pinella

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply