SQL query that worked before, does not work anymore

Question

An excel file i'm being supplied daily contains a space in one of the numbers that is supposed to be an integer. This was easily fixed when i simply replaced it via a replace and convert in a query. However, now that i switched from .NET Core to NodeJS, when i tried to use the same queries, it didn't go as expected. Here is the query i'm working with.

"INSERT INTO table SELECT ANvarChar, CONVERT(INT, replace(AVarchar,' ', '')), AnotherVarCharFROM TempImport"

When i ran it through C#, targetting the internal SQL Server database worked, but now i'm doing the same on linux, using mssql server.

"BULK INSERT TempImport FROM '" + csvFilePath + "' WITH (FIELDTERMINATOR = '||', ROWTERMINATOR = '\n');"

It throws the error:

RequestError: Conversion failed when converting the varchar value 'AVarchar' to data type int.

It's clear that the convert and replace don't work in this context, What could i do instead? I already tried doing it via the converter, and during the download, without any luck.

Are you running the first query ('INSERT INTO') also from NodeJS? And then how is the 'BULK INSERT' query related to all this? — Peter B
Yes, it's also run from there, isn't that what i'm supposed to be using for inserting CSV files into tables? Basically, the bulk insert inserts the CSV file into a temporary table, which is then inserted into other tables. the insert into is one of them — Colgate_Man420
My recommendation is to load the data as a string into a staging table, then do the type conversions in the database. — Gordon Linoff
Which query throwed the error? If the second one, I guess table TempImport's related data type should be VARCHAR or NVARCHAR instead of INT - unlike in the final table. Compare the table definitions on the 2 servers. — Dávid Laczkó
@Colgate_Man420 Yes it was wrong. This is correct one : SELECT * FROM TempImport WHERE IsNumeric(replace(AVarchar,' ', ''))=0 . Also you replace only space, the value might have TAB or ENTER. if you import values from excel it is highly possible. Try to change your replace with this : REPLACE(REPLACE(REPLACE(REPLACE(AVarchar,CHAR(13),''),CHAR(10),' '),CHAR(9,'')),' ', '') — Zeki Gumus

Dávid Laczkó Dávid Laczkó · Accepted Answer · 2018-11-27T15:22:57

The character could be something other than space after replacement. You can check if the data contains space after replacement like this:

SELECT * FROM (
SELECT ANvarChar, replace(AVarchar,' ', '') as TEST, AnotherVarChar FROM TempImport
) as Conv
WHERE TEST LIKE '% %'

This should not return any rows. In this case other whitespace replacements should be added (like one adviced by Zeki Gumus in the comment).

SQL query that worked before, does not work anymore

2 Answers