none
data conversion failed RRS feed

  • Question

  • hello guys,

    Has anyone encountered this issue before and know what is the solution for it?

    Friday, February 8, 2019 6:40 AM

Answers

  • Hi Tan_100,

    Please check the data row 324 in your DLG200.csv file.

    Based on my test, it works normally in my environment, it looks like that some part in your data resource cannot be recognized as bool type.

    Actually, we can use the full SSIS feature to import this csv file, SSIS can separate the wrong data rows.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 11, 2019 3:25 AM
    Moderator
  • double click on the setup.exe

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Tan_100 Thursday, February 14, 2019 2:12 AM
    Tuesday, February 12, 2019 5:50 AM
    Moderator

All replies

  • Hi,    
    Use the way of Cast to overcome such error.



    Many Thanks & Best Regards, Hua Min

    Friday, February 8, 2019 7:00 AM
    Answerer
  • Hello HuaMin,

    For the column 'Loss of Reduncy " is true or false data and in the advanced I have changed it to Boolean data type.

    Regards,

    Tan

    Friday, February 8, 2019 7:11 AM
  • You can convert boolean like
    SELECT CAST(true AS INT) AS true_int, CAST(true AS DOUBLE) AS true_double,
      CAST(false AS INT) AS false_int, CAST(false AS DOUBLE) AS false_double;



    Many Thanks & Best Regards, Hua Min

    Friday, February 8, 2019 7:24 AM
    Answerer
  • Hello HuaMin,

    Can I ask why do I need to convert booelan?By changing the data to Boolean in the advanced don't work?

    Regards,

    Tan

    Friday, February 8, 2019 7:38 AM
  • Hi,
    What is your error?

    Many Thanks & Best Regards, Hua Min

    Friday, February 8, 2019 7:49 AM
    Answerer
  • Hello HuaMin,

    My data is in Boolean type and what I need to do is importing the data into the wizard. This is the error I occurred" 

    Data conversion failed. The data conversion for column "Loss of Redundancy" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
     (SQL Server Import and Export Wizard).

    Regards,

    Tan

    Friday, February 8, 2019 9:36 AM
  • Hi,
    Can you try to duplicate current table by only adjusting this Boolean column into INT data type, using the way (for conversion) shown in above, and then re-try for this?

    Many Thanks & Best Regards, Hua Min


    Friday, February 8, 2019 9:43 AM
    Answerer
  • Hi Tan_100,

    This problem is often related to the mismatch in data type, could you please share more information about your resource table? From which resource?

    Here is a blog which discusses about this problem, please refer to it: https://blog.sqlauthority.com/2012/12/30/sql-server-fix-error-the-conversion-returned-status-value-2-and-status-text-the-value-could-not-be-converted-because-of-a-potential-loss-of-data-sql-server-import-and-export-wizard/

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 11, 2019 2:57 AM
    Moderator
  • Hello Teige,

    I have been stuck at this issue for days already. The data in my column is in Boolean either True or False . 

    I have attached two photos for your reference.  

    These are the error messages that I got.

    Messages
    Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Loss of Redundancy" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
     (SQL Server Import and Export Wizard)
     
    Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Source - DLG200_csv.Outputs[Flat File Source Output].Columns[Loss of Redundancy]" failed because error code 0xC0209084 occurred, and the error row disposition on "Source - DLG200_csv.Outputs[Flat File Source Output].Columns[Loss of Redundancy]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
     (SQL Server Import and Export Wizard)
     
    Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "C:\Users\TrainerPC1\Desktop\07022019\DLG200.csv" on data row 324.
     (SQL Server Import and Export Wizard)
     
    Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on Source - DLG200_csv returned error code 0xC0202092.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.
     (SQL Server Import and Export Wizard)
     

    Regards,

    Tan

    Monday, February 11, 2019 3:05 AM
  • Hi Tan_100,

    Please check the data row 324 in your DLG200.csv file.

    Based on my test, it works normally in my environment, it looks like that some part in your data resource cannot be recognized as bool type.

    Actually, we can use the full SSIS feature to import this csv file, SSIS can separate the wrong data rows.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 11, 2019 3:25 AM
    Moderator
  • Hello Tegie,

    Thanks for your promptly reply , I have check my row 324 it seems all right. 

    

    I am using SQL Server 2014 management studio to import my data .

    My objective is to import all the data in my csv to the server but there are many problems to it.

    Regards,

    Tan

    Monday, February 11, 2019 3:35 AM
  • Hi,

    Did you try to adjust data type of Boolean column and others?


    Many Thanks & Best Regards, Hua Min

    Monday, February 11, 2019 3:57 AM
    Answerer
  • Hello HuaMin,

    May I know what do you mean ? 

    I have changed the data type in the advanced to Boolean

    Regards,

    Tan

    Monday, February 11, 2019 5:00 AM
  • Hello HuaMin,

    May I know what do you mean ? 

    I have changed the data type in the advanced to Boolean

    Regards,

    Tan

    How is the data coming in your CSV?

    Is it coming as True, False

    or 1 and 0

    or true,false?

    Also is there a chance of any row missing data i.e data coming as blank?

    If you can provide a sample file with some test data then we can try reproducing the issue here


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, February 11, 2019 5:25 AM
    Moderator
  • Could you please share the sample file to us to reproduce this problem?

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 11, 2019 5:28 AM
    Moderator
  • Hello Teige and Visakh16 ,

    Thank you very much for your time to help. I keep getting this error. 

    Basically, my objective is to import all the data into the SQL server. Line 1 is all the data variable and line 2 onwards are the data. Anything you are unsure feel free to email me :finishtherace100@gmail.com

    How do I share my sample data with you guys?

    Regards,

    Tan

    Monday, February 11, 2019 6:30 AM
  • You can use any credible network disk like Onedrive, Google Drive to share the csv file to us.

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 11, 2019 6:32 AM
    Moderator
  • Hello Teige,

    What is your email address i share with you on google drive.

    Regards,

    Tan

    Monday, February 11, 2019 6:38 AM
  • here you go :

    https://drive.google.com/file/d/1xnTN2xaNgjvCKSWEIhy1-rcvbajvNtli/view?usp=sharing

    Monday, February 11, 2019 6:43 AM
  • Hi,

    kindly update me if you are able to view the data.

    thank you

    Monday, February 11, 2019 6:44 AM
  • Hi,

    kindly update me if you are able to view the data.

    thank you

    Yes

    I can view the data


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, February 11, 2019 7:09 AM
    Moderator
  • Hello Visakh16,

    At my end I have created all the table for all the variables in my data for line 1. Now my issue is having problem to import it to the wizard and is giving me error after error.

    Once you are able to import all the data to the server, kindly update me thank you so much appreciate your time to help.

    Regards,

    Tan

    Monday, February 11, 2019 7:15 AM
  • Finally, I find the root problem, there are 32 rows whose [Loss of Redundancy] columns contains value not like TRUE or FALSE. This problem can be cause by that the column delimiter is not unique.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 11, 2019 7:41 AM
    Moderator
  • For example, if you use the comma{,} as the column delimiter, then the comma has shown in the content in another column, then the content will be divided as two columns.

    Actually, not only the data in the [Loss of Redundancy] has problem, other column also have problem.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 11, 2019 7:43 AM
    Moderator
  • I have set the column which has problem to red, I will send it to you via e-mail

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 11, 2019 7:51 AM
    Moderator
  • Hello Teige,

    Where do u see the 32 rows ? i only see true or false in my data?

    Monday, February 11, 2019 7:52 AM
  • I use the SSIS tool to check it

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 11, 2019 7:54 AM
    Moderator
  • As far as I see , I cant find any such rows while the shared csv in Excel


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, February 11, 2019 7:55 AM
    Moderator
  • Hello ,

    I thought I have change the comma in the 'MESSAGE' already? May I know which variable  got comma in the data

    Monday, February 11, 2019 7:56 AM
  • Yeah i cannot find also in the csv file ??
    Monday, February 11, 2019 7:59 AM
  • Hello visakh16,

    May I know what is the problem? Why i cant import the data to the server??

    Regards,

    tan

    Monday, February 11, 2019 8:00 AM
  • i need to save my data in .csv format for your info
    Monday, February 11, 2019 8:00 AM
  • I have sent this file to you, please check it.

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 11, 2019 8:25 AM
    Moderator
  • Hello Teige,

    I have used your file but still same error .

    I have to download SISS?

    Regards,

    Tan

    Monday, February 11, 2019 8:39 AM
  • Hi Tan_100,

    Let's sort it out to make things more clear.

    Actually, I found that there are some problem in your data resource, the csv file you shared to me uses the ',' as column delimiter. Actually, the import and export tool also uses it as column delimiter (but you cannot see it in Excel). However, in your csv file, there are many ',' in the content, like in the message column and the speed column. These ',' in the content will also be recognized as column delimiter, then one column will be divided into two columns and more.

    For example, if we have 'part one, part two' string in the column before [Loss of Redundancy] column, then the 'part two' will be recognized as the content in [Loss of Redundancy] column, rather than the original content in this column.

    Finally, I found that there is not a '|' in your content, then I changed the column delimiter of csv to '|', but based on my test, the import and export tool cannot handle so much data, we still need to use the SSIS.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 11, 2019 8:51 AM
    Moderator
  • Actually, in this scenario, the most convenient method is converting this csv file to Excel file, then use SSIS to import this file to SQL Server.

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 11, 2019 8:52 AM
    Moderator
  • Hello Teige,

    I have converted my data file to excel format. Where do I download the SSIS?

    My objective is to upload all the data into the SQL server and than use the data in the server for Tableau 

    Regards,

    Tan

    Monday, February 11, 2019 8:59 AM
  • I upload the data to the SQL Server and generate the backup file of the database, please restore it directly: https://1drv.ms/u/s!Ao9Of0JgO6MU32UKmaTXLFArCmuG

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 11, 2019 9:25 AM
    Moderator
  • To import Excel to SQL Server with SSIS, please refer to this article: https://www.excel-sql-server.com/excel-import-to-sql-server-using-ssis.htm

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 11, 2019 9:27 AM
    Moderator
  • Hello Teige,

    I have converted my data file to excel format. Where do I download the SSIS?

    My objective is to upload all the data into the SQL server and than use the data in the server for Tableau 

    Regards,

    Tan

    you can create a full fledged SSIS package for that

    This will give you more flexibility like changing data types etc to handle cases like above

    see

    https://www.sqlshack.com/using-ssis-packages-import-ms-excel-data-database/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, February 11, 2019 10:06 AM
    Moderator
  • Hello Visakh16 and Teige,

    Thank you so much for your help:) Btw Teige , the link you provided I downlaoded than how can I open it?

    Regards,

    Tan

    Monday, February 11, 2019 1:33 PM
  • This is a SQL Server database full backup, you can restore it to your SQL Server.

    https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-database-backup-using-ssms?view=sql-server-2017


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 12, 2019 1:26 AM
    Moderator
  • Hello Teige,

    Good Morning. Can I check with you where can I download SSIS? I have click my setup for sql 2014 server and cannot find any SSIS in the shared features column?

    Regards,

    Tan

    Tuesday, February 12, 2019 2:16 AM
  • Which edition of SQL Server did you use? Only Standard, Enterprise, Developer edition can use this feature.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 12, 2019 2:18 AM
    Moderator
  • Hello Teige,

    I am running on Windows 7 Professional and using Microsoft SQL 2014 Express edition. I guess I cant use SSIS already. Any suggestion how I can proceed?

    Regards,

    Tan

    Tuesday, February 12, 2019 2:36 AM
  • Express Edition cannot use SSIS, if this is not a business environment, you can try to install a Developer edition to get this feature.

    There is also another workaround, you can save this csv file as a XML file, then use this method to import it: https://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/.

    By the way, have you restored the database I shared with you?


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 12, 2019 2:41 AM
    Moderator
  • Hello Teige,

    Can Windows 7 professional use developer edition? if can do you have the download link?


    I havent restored the database yet.

    Regards,

    Tan

    Tuesday, February 12, 2019 2:46 AM
  • First check this link: https://download.my.visualstudio.com/sg/en_sql_server_2014_developer_edition_with_service_pack_3_x64_dvd_083c344f.iso?t=3cb00139-491c-4d2f-bdac-f668b7c9bd3b&e=1549976227&h=6548387f0aeff30170a40ca924d59e0b&su=1

    If it cannot work, go to https://my.visualstudio.com/downloads, create an account, this is free, then search SQL Server 2014, you will find the download link


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 12, 2019 2:55 AM
    Moderator
  • Hi Tan,

    This is interesting but I just keep wondering, 

    Did you try to load the file as Excel workbook and NOT use csv file? You can open csv and save the file as Excel Workbook and then load it using Export/Import because I think it will load ok, if use excel as source.

    Good luck with SSIS.  



    Tuesday, February 12, 2019 3:05 AM
  • Hello Teige,

    Firstly , I would like to thank you for your prompt reply and assist me in this problem:) 

    I have clicked on the link that you have provided and is downloading in progress.

     I have changed the file format to excel and when I import I get this error.

    Regards,

    Tan

    Tuesday, February 12, 2019 3:17 AM
  • Install Microsoft Office, I think it will be easier than installing SSIS. I used Excel as source and it loaded without any error.


    Tuesday, February 12, 2019 3:30 AM
  • Hello Teige,

    My download is complete , how do I install it?

    Tuesday, February 12, 2019 3:35 AM
  • I didn't find the installation media in your folder. It should be a file named "en_sql_server_2014_developer_edition_with_service_pack_3_x64_dvd_083c344f.iso"

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, February 12, 2019 5:41 AM
    Moderator
  • You will need to download it: https://www.microsoft.com/en-US/download/details.aspx?id=13255, both 32-bit and 64-bit, that depends on the platform of your import and export wizard.

    It looks like that you have installed the Office on your machine, then you will need to use CMD to install the above driver, using the /quiet option.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, February 12, 2019 5:47 AM
    Moderator
  • Hello Teige,

    I just extracted it and these are the stuff i am able to see it:) 

    

    Tuesday, February 12, 2019 5:49 AM
  • double click on the setup.exe

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Tan_100 Thursday, February 14, 2019 2:12 AM
    Tuesday, February 12, 2019 5:50 AM
    Moderator
  • Hello Teige,

    can  i check with you how do I import like 1 year of data into the sql server? right now i am able to import 1 day data to the server only. I need to purchase sql standard edition? or whats your suggestion?

    Regards,

    Tan

    Friday, February 15, 2019 5:55 AM