Cannot import flat file to Azure Data Studio

Question

Cannot import flat file to Azure Data Studio

Note Thanita 6

Hi,

I'm using mac to run Azure Data Studio to work with SQL.

I use import wizard to import csv files. Some file can be uploaded but most of files can't and it shows like this below.

Please suggest. Thanks

✗ Microsoft.SqlServer.Prose.Import.BcpProcessException: Error inserting data into table. ---> System.InvalidOperationException: The given value 'Yes' of type String from the data source cannot be converted to type bit for Column 10 [owns_car]. ---> System.FormatException: Failed to convert parameter value from a String to a Boolean. ---> System.FormatException: String was not recognized as a valid Boolean.
at System.Boolean.Parse(ReadOnlySpan1 value) at System.Boolean.Parse(String value) at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) at Microsoft.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming) --- End of inner exception stack trace --- at Microsoft.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming) at Microsoft.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed) --- End of inner exception stack trace --- at Microsoft.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed) at Microsoft.Data.SqlClient.SqlBulkCopy.ReadWriteColumnValueAsync(Int32 col) at Microsoft.Data.SqlClient.SqlBulkCopy.CopyColumnsAsync(Int32 col, TaskCompletionSource1 source)
at Microsoft.Data.SqlClient.SqlBulkCopy.CopyRowsAsync(Int32 rowsSoFar, Int32 totalRows, CancellationToken cts, TaskCompletionSource1 source) at Microsoft.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinued(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource1 source)
at Microsoft.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource1 source) at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource1 source)
at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource1 source) at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken) at Microsoft.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken) at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader) at Microsoft.SqlServer.Prose.Import.BcpTextSynthesis.InsertIntoDB(String inputFilePath, String tableName, String schemaName, IReadOnlyList1 columnInfo, SqlConnection connection, Int32 batchSize, SqlTransaction transaction, IList1 allFinalTransformations, IList1 allFinalTransformationColumns, IList1 allFinalColNames) --- End of inner exception stack trace --- at Microsoft.SqlServer.Prose.Import.BcpTextSynthesis.InsertIntoDB(String inputFilePath, String tableName, String schemaName, IReadOnlyList1 columnInfo, SqlConnection connection, Int32 batchSize, SqlTransaction transaction, IList1 allFinalTransformations, IList1 allFinalTransformationColumns, IList`1 allFinalColNames)
at Microsoft.SqlServer.Prose.Import.BcpProcess.CreateTableAndInsertDataIntoDb(String connectionString, Int32 batchSize, String azureAccessToken)

3 answers

Your answer

Answer 1

DWN 11

@Olaf Helper , Unfortunately, I'm getting the exact same error, but with 0s and 1s! Any suggestions?

✗ Microsoft.SqlServer.Prose.Import.BcpProcessException: Error inserting data into table. ---> System.InvalidOperationException: The given value '0' of type String from the data source cannot be converted to type bit for Column 4 [isInDC]. ---> System.FormatException: Failed to convert parameter value from a String to a Boolean. ---> System.FormatException: String was not recognized as a valid Boolean.

Laurent Tourreau 5

Hi @Olaf Helper ,
I am facing the same issue:
ADS 1.47
Sql Server Import extension 1.5.5
Microsoft.SqlServer.Prose.Import.BcpProcessException: Error inserting data into table.
---> System.InvalidOperationException: The given value '1' of type String from the data source cannot be converted to type bit for Column 2 [bluetooth].
---> System.FormatException: Failed to convert parameter value from a String to a Boolean.
---> System.FormatException: String '1' was not recognized as a valid Boolean.

`at System.String.System.IConvertible.ToBoolean(IFormatProvider provider)`  
`at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)`  
`at Microsoft.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)`  
`--- End of inner exception stack trace ---`  
`at Microsoft.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)`  
`at Microsoft.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed)`  
`--- End of inner exception stack trace ---`  
`at Microsoft.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed)`  
`at Microsoft.Data.SqlClient.SqlBulkCopy.ReadWriteColumnValueAsync(Int32 col)`  
``` at Microsoft.Data.SqlClient.SqlBulkCopy.CopyColumnsAsync(Int32 col, TaskCompletionSource`1 source) ```  
``` at Microsoft.Data.SqlClient.SqlBulkCopy.CopyRowsAsync(Int32 rowsSoFar, Int32 totalRows, CancellationToken cts, TaskCompletionSource`1 source) ```  
``` at Microsoft.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinued(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source) ```  
``` at Microsoft.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source) ```  
``` at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source) ```  
``` at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source) ```  
`at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)`  
`at Microsoft.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)`  
`at Microsoft.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)`  
``` at Microsoft.SqlServer.Prose.Import.BcpTextSynthesis.InsertIntoDB(String inputFilePath, String tableName, String schemaName, IReadOnlyList`1 columnInfo, SqlConnection connection, Int32 batchSize, SqlTransaction transaction, IList`1 allFinalTransformations, IList`1 allFinalTransformationColumns, IList`1 allFinalColNames) ```  
`--- End of inner exception stack trace ---`  
``` at Microsoft.SqlServer.Prose.Import.BcpTextSynthesis.InsertIntoDB(String inputFilePath, String tableName, String schemaName, IReadOnlyList`1 columnInfo, SqlConnection connection, Int32 batchSize, SqlTransaction transaction, IList`1 allFinalTransformations, IList`1 allFinalTransformationColumns, IList`1 allFinalColNames) ```  
`at Microsoft.SqlServer.Prose.Import.BcpProcess.CreateTableAndInsertDataIntoDb(String connectionString, Int32 batchSize, String azureAccessToken)`

Steve Hiner 0 Reputation points

2023-12-28T23:04:10.4166667+00:00

I get the same error:

Microsoft.SqlServer.Prose.Import.BcpProcessException: Error inserting data into table.
---> System.InvalidOperationException: The given value '1' of type String from the data source cannot be converted to type bit for Column 2 [IsActive].
---> System.FormatException: Failed to convert parameter value from a String to a Boolean.
---> System.FormatException: String '1' was not recognized as a valid Boolean.

Makes little sense that the very value SQL Server uses to represent booleans can't be imported. I exported this data from a SQL Server table but I can't import it into another table without modifying every single row it seems. My table with 8 rows isn't a problem but the one with 108,000 rows will be.
Steve Hiner 0 Reputation points

2023-12-28T23:33:23.5433333+00:00

It's extra dumb that the extension detects the 0 and 1 values and suggests a bit field but then it can't convert the 0 and 1 to a bit value.

Answer 2

CyrAz 5,181

Seems that the column "owns_car" has defined type of "bit", which only accepts 1, 0 or NULL as values; and the dataset you're trying to import contains the value "Yes".
See https://learn.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql?view=sql-server-ver15

Answer 3

Olaf Helper 47,436

the given value 'Yes' of type String from the data source cannot be converted to type bit for Column 10 [owns_car].

You get a clear error message, SQL Server can't convert string Yes/No to bit, you have to convert it on your own, like

SELECT CASE WHEN sourceColumn = 'Yes' THEN 1 ELSE 0 END AS owns_car

Share via

Cannot import flat file to Azure Data Studio

3 answers

Your answer