Solved Data Type Gumbo

[euzica2]

Hi everyone!

I am attempting to import an excel table into Access but unfortunately, there isn't a data type that I can choose that will allow for me to import this particular column without flagging errors. Here is an excerpt of the data set I am referring to:

The values highlighted in yellow are an example of the ones that typically throw errors. I would like to avoid having to alter the data before importing it into excel as it could potentially complicate things for a future user. I appreciate any insight or nuggets of wisdom!

 

[bob fitz]

Why can't you use Short Text type

 

[Gasman]

What is wrong with a String type?

 

[theDBguy]

Hi. Just curious, what sorts or errors were you getting?

 

[jdraw]

there isn't a data type that I can choose that will allow for me 
to import this particular column without flagging errors.

short text would seem to handle your data

 

[euzica2]

Hi everyone,

Thank you so much for all of your inputs!

Unfortunately, the root cause of the issue was my assumption that appending data types within the design view of the table would clear the "type conversion failure" errors I recieved. I decided to re-import the entire table while excplicitly selecting "short text" from the default "double" through the import wizard instead of assigning data types to the table after it was imported. This is the frustration I needed to experience to break a bad habit.

 

[NauticalGent]

With the word Gumbo in the title, I am shocked @The_Doc_Man refrained from commenting, being the coon-ass that he is!

 

[CJ_London]

there is a way to force everything to be text and then you have control - see this link https://www.access-programmers.co.u...deleting-a-line-in-a-csv.313850/#post-1720616

it's about a textfile, but the equivalent 'starting query' for excel would be

SELECT *
FROM (SELECT * FROM [sheet1$] AS xlData IN 'C:\PathAndFileName.xlsx'[Excel 12.0;HDR=No;IMEX=1;ACCDB=Yes]) AS XL;

After that, everything else is pretty much the same

 

[The_Doc_Man]

NG, I've been taking care of my wife with her twice-daily infusions following her staph infection following her knee replacement. My time to review new articles has been limited.

CJ's suggestion is a good one. The other alternative is that if the incoming XL file is always the same format (counting the variability of that column), you could easily make an import template that forced the column to be treated as text no matter what it might look like.