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.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.