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.