troubleshooting Question

Access Query on CSV returning number not text

Avatar of Joe Jernigan
Joe Jernigan asked on
Microsoft AccessSQL
6 Comments1 Solution123 ViewsLast Modified:
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.
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros