Avatar of Joe Jernigan
Joe Jernigan

asked on 

Access Query on CSV returning number not text

I have an access database with an import function to bring in data from CSV files but have run into an issue that I can't seem to find a solution for and hope that you experts can help.

We are running a query on a CSV file to select the relevant, unique, data that is required and importing that data into various other tables within the database.  One of those fields, [Field D], contains and alphanumeric string to identify where the data came from.  Examples FW119, A009, K12, etc.

Everything was working great until we tried to run the query on CSV's where [Field D] began with a K.  Access is returning these values as a numeric value, when it needs to be a Text value at all times.

Here is a snippet of our current query that is run by the import function:
SELECT DISTINCT Replace([importfile#csv].[Field A],"'","") AS [Field A], Replace([importfile#csv].[Field B],"'","") AS [Field B], [importfile#csv].[Field C], [importfile#csv].[Field D]
FROM [Text;FMT=Delimited;HDR=YES;IMEX=2;CharacterSet=437;Database=file location].[importfile#csv];

Small Example of Data in the CSV
Column 1,Column 2,Field C,Field A,Field B,Field D,Column 3,Column 4,Column 5,Column 6,Column 7,Column 8,Column 9
13,100,123 That Street,'1234567890123456,'12345678901234,K12,FALSE,FALSE,35,0,TEST,FALSE,
13,60,123 This Street,'1234567890234567,'12345678902345,K12,FALSE,FALSE,34,1,TEST,FALSE,

[Field D] is currently returned as 12.00 K

How do I get it to return as K12?

Any help you can provide, even if it's a link to a solution, is greatly appreciated.
Microsoft AccessSQL

Avatar of undefined
Last Comment
PatHartman

8/22/2022 - Mon