2

I have created a linked server to query some CSV files. The query below works with no errors:

SELECT * FROM OPENQUERY(LINKEDSERVER, 'Select * FROM [import.csv]')

There is a field that could contain either an number or text and the query seems to want to treat it strictly as an integer and returns NULL as the values for any records that have text in that field. All of the records that contain numeric data in that field are displaying properly.

My first thought was maybe that I needed to cast or convert that field to a string to make sure that the query returned data regardless of whether it was numeric or text, but I haven't been able to get that to work. Something like:

SELECT * FROM OPENQUERY(LINKEDSERVER, 'Select cast(field1, varchar(50)) as newfield, field2 FROM [import.csv]')

Does anyone have any ideas?

Thom A
  • 88,727
  • 11
  • 45
  • 75
Ron
  • 61
  • 3
  • Put the conversion in the outer query, not the inner. Also you seem to be mixing up `CAST` and `CONVERT` syntaxes. You likely want `TRY_CAST`/`TRY_CONVERT` too. – Thom A May 20 '22 at 17:09
  • The confusion with the data type seems to be happening in the inner query. Using CAST/TRY_CAST or CONVERT/TRY_CONVERT in the outer query returns the exact same results as the original query in my OP where the text values are returned as NULL. – Ron May 20 '22 at 17:49
  • The linked server is the one that is providing that table, I suggest you look there. Is this an Excel sheet using ACE OLDEB? – Charlieface May 20 '22 at 17:51
  • I just now opened the source CSV file, changed one of the text values to a numeric value, saved it and re-ran the query. As expected, the query returned the correct data for that record once the data was changed from a text value to a numeric value. All of the unchanged records are NULL in the query results. – Ron May 20 '22 at 17:53
  • @Charlieface, it is a flat CSV file using ACE OLEDB. – Ron May 20 '22 at 17:55
  • Then how about you change the formatting within Excel? Set it to `Text` and save it as an xlsx – Charlieface May 20 '22 at 17:56
  • @Charlieface, I can make it work that way, but the problem is that I am trying to automate the process and the only export option from the data source is CSV. I was hoping that there was trick to treat the field as a string or maybe a setting. From what I am seeing, the data type in a CSV file is being detected based on the the larger percentage of a specific data type. For example, if a field in 6 of 11 records are detected as integers and the same field in the remaining 5 records are detected as strings, the data type is assumed to be an integer. I have been able to duplicate this. – Ron May 20 '22 at 18:51
  • 1
    Sounds like you're running afoul of Type Guessing. When configuring the linked server connection do the extended properties include `IMEX=1; TypeGuessRows=0; ImportMixedTypes=TEXT`? Ref: [Initializing the Microsoft Excel driver](https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/initializing-the-microsoft-excel-driver) – AlwaysLearning May 20 '22 at 22:17
  • Also relevant https://stackoverflow.com/a/68912543/14868997 and https://stackoverflow.com/a/50018861/14868997 – Charlieface May 21 '22 at 22:12
  • @AlwaysLearning, that looked promising, but no luck. I included those extended properties and it's still autodetected the data type. However, the second link that Charlieface provided eventually led me to a solution after some additional research and digging through the registry. I ended up changing ImportMixedTypes to Text under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Text. – Ron May 23 '22 at 14:14

0 Answers0