Solved Data Type Gumbo (1 Viewer)

euzica2

New member
Local time
Yesterday, 19:48
Joined
Sep 5, 2020
Messages
5
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:
1600625124560.png


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!
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:48
Joined
Sep 21, 2011
Messages
14,048
What is wrong with a String type?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:48
Joined
Oct 29, 2018
Messages
21,358
Hi. Just curious, what sorts or errors were you getting?
 

jdraw

Super Moderator
Staff member
Local time
Yesterday, 20:48
Joined
Jan 23, 2006
Messages
15,364
Code:
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

New member
Local time
Yesterday, 19:48
Joined
Sep 5, 2020
Messages
5
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

Ignore List Poster Boy
Local time
Yesterday, 20:48
Joined
Apr 27, 2015
Messages
6,286
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

Super Moderator
Staff member
Local time
Today, 00:48
Joined
Feb 19, 2013
Messages
16,553
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

Immoderate Moderator
Staff member
Local time
Yesterday, 19:48
Joined
Feb 28, 2001
Messages
27,001
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.
 

Users who are viewing this thread

Top Bottom