Post reply

How to import Excel data into CSV file?

  • November 24, 2011 at 1:11 am

    #263389

    Experts –

    I have an Excel sheet which has plenty of columns and I need to push the data into CSV file using a SSIS Package, so I‘ve used Excel Source and Flat File Destination. While doing in this way, entire data from Excel is pushing into a single column of CSV file.

    But I wanted to push the Excel data into CSV with same columns as Excel does, please help!!!

    Thanks !!

  • November 24, 2011 at 2:38 pm

    #1412798

    Are you sure SSIS is the right tool to do it?

    CSV is a rather straight forward format of Excel. But Excel is not really a database source...

    A short VB script or even a shell script should do it...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • November 24, 2011 at 10:57 pm

    #1412845

    Hi, I would like to have a package to do this job for me so that I can trigger a SQL Job whenever I wanted to push the data. I am fine to have a VB Code in order to achieve this task but I am not quite sure on how to achieve it. I tried using Excel Source -> Flat File destination and got the below error:

    Flat File Destination [271]] Error: Data conversion failed. The data conversion for column "OrganizationName" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

    I did change the TruncationRowDisposition as RD_IgnoreFailure by checking from one of the blogs, it didn’t work either.

    If you can help with any VB Code, that would be very much appreciated !!

  • November 25, 2011 at 5:13 am

    #1412962

    MSBI Learner (11/24/2011)

    Hi, I would like to have a package to do this job for me so that I can trigger a SQL Job whenever I wanted to push the data. I am fine to have a VB Code in order to achieve this task but I am not quite sure on how to achieve it. I tried using Excel Source -> Flat File destination and got the below error:

    Flat File Destination [271]] Error: Data conversion failed. The data conversion for column "OrganizationName" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

    I did change the TruncationRowDisposition as RD_IgnoreFailure by checking from one of the blogs, it didn’t work either.

    If you can help with any VB Code, that would be very much appreciated !!

    The truncate error is because you are trying to put a string into field with a smaller length than the length of the string. For example, the string is 'abcde', but the destination field has only 3 characters, so you would end up with 'abc'. SSIS gives by default an error at truncation. You can change this to ignore the error, or you could enhance the length of your destination fields.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 4 posts - 1 through 4 (of 4 total)

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