Post reply

importing csv with different data type within the same column...HELP!!!!!!!!

  • June 18, 2008 at 1:45 pm

    #189279

    I have a file that has Integer and string within the same column. Depending which data type is first the second get nulled out. For example

    col1 col2 col3 col4 col5

    ___________________________________________

    123 joe bob 12 abc

    abc alex goat abc efg

    345 123 dog cat hij

    345 wer 123 1234 234

    After import into SQL, I get

    col1 col2 col3 col4 col5

    ___________________________________________

    123 joe bob 12 abc

    NULL alex goat NULL efg

    345 NULL dog NULL hij

    345 wer NULL 1234 NULL

    Does anyone know how to fix this? Any help will be appreciated

    thanks

  • June 18, 2008 at 2:06 pm

    #830747

    How are you importing this into SQL?

    Matt

  • June 18, 2008 at 2:10 pm

    #830748

    SELECT *

    FROM OPENROWSET ('MSDASQL',

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

    DefaultDir=C:\;

    Extensions=csv;',

    'SELECT * FROM myFile.csv')

    Same problem occurs with XLS file and with import wizard as well

  • June 18, 2008 at 2:16 pm

    #830752

    I haven't seen numbers disappear out of text columns, but I have seen text disappear out of number columns, in that same manner.

    My solution was to pre-define the table I was importing into, make sure the columns were all varchar, then clean up the data as needed and finally move that into a table with correctly defined data types on columns.

    Another solution I've seen work is change the file type to .txt and save it fixed-width instead of delimited.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • June 18, 2008 at 2:30 pm

    #830764

    still wont work 🙁

  • June 18, 2008 at 2:41 pm

    #830773

    The problem is the import process takes a look at a small sample of the data in the column and makes a judgement as to what the data type is. This behavior also happens in a column intermixed data, meaning alphanumeric.

    Try specifiying imex = 1 to your import. I would also recommend you use a format file, as this will more than likely solve your problem, and that you specify the bulk option for openrowset if you plan on importing bulk data.

    Your connection string should look like this to excel.

    OPENROWSET('Microsoft.Jet.OLEDB.4.0',

    'Excel 8.0;HDR=NO;IMEX=1;Database=C:\test.xls;',

    'SELECT * FROM [sheet1$]')


    My blog: http://jahaines.blogspot.com

  • June 18, 2008 at 3:08 pm

    #830796

    thanks Adam, you are a genious

  • June 18, 2008 at 9:35 pm

    #830872

    I got a new problem...how do i import a pipe delimiter file. This is what I try but it's not working

    --Example to open CSV file

    SELECT *

    FROM OPENROWSET ('MSDASQL',

    'Driver={Microsoft Text Driver (*.txt; *.csv)};HDR=NO;IMEX=1;

    DefaultDir=C:\;

    Extensions=csv;Delimiter=|',

    'SELECT * FROM myFile.txt')

    but it's not working

Viewing 8 posts - 1 through 7 (of 7 total)

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