Really struggling with cleaning this excel data when imported to Access (1 Viewer)

gojets1721

Registered User.
Local time
Today, 08:08
Joined
Jun 11, 2019
Messages
429
I am sent an excel file of data from another department (see attached example) and I'm trying to import it into Access and clean it up. I expected this to be very simple but the excel file's formatting is horrific.

Firstly, the '1st question' column has both the question and response listed in it. So in theory, I'd like to completely get rid of the question and either have it say 'yes' or 'unanswered'.

The issue is that you'll notice in the Excel file that there's like 40 spaces at the end of the last character in that column. It's bizarre. I try to use the trim function in Access to get rid of that and it doesn't work.

Any suggestions?
 

Attachments

  • Example.zip
    6.7 KB · Views: 177

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:08
Joined
May 7, 2009
Messages
19,169
clean it in the raw excel workbook.
create a function that will remove tab character, new line, etc:
Code:
Public Function fnTrim(ByVal p As Variant) As String
    p = p & ""
    fnTrim = p
    If Len(p) = 0 Then
        Exit Function
    End If
    p = Replace$(p, Chr(9), "")
    p = Replace$(p, Chr(10), "")
    p = Replace$(p, Chr(13), "")
    p = Trim$(p)
    fnTrim = p
End Function
 

gojets1721

Registered User.
Local time
Today, 08:08
Joined
Jun 11, 2019
Messages
429
Could you drop that into my example spreadsheet by any chance? I know nothing about VBA in excel tbh
 

plog

Banishment Pending
Local time
Today, 10:08
Joined
May 11, 2011
Messages
11,611
The import into Access works fine--I mean it recognizes the right data types, finds all columns, no issues thrown with the data. I would just import it into Access as a temporary table, then use a query to move the data to a table with the proper structure.

For your 1st Question issue, you simply need to create a calculated field to test if the string '? yes' is in it.

1stAnswer: Iif([1st Question] Like "*? yes*", True, False)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:08
Joined
May 7, 2009
Messages
19,169
use the xlsm file.
 

Attachments

  • Example_2.zip
    21.6 KB · Views: 159

bastanu

AWF VIP
Local time
Today, 08:08
Joined
Apr 13, 2010
Messages
1,401
Cross-posted with multiple solutions here:

Cheers,
 

gojets1721

Registered User.
Local time
Today, 08:08
Joined
Jun 11, 2019
Messages
429
The import into Access works fine--I mean it recognizes the right data types, finds all columns, no issues thrown with the data. I would just import it into Access as a temporary table, then use a query to move the data to a table with the proper structure.

For your 1st Question issue, you simply need to create a calculated field to test if the string '? yes' is in it.

1stAnswer: Iif([1st Question] Like "*? yes*", True, False)
That did the trick @plog!

Is there a way to write it so that if "? no" is in there as well, then that appears as -2?
 

plog

Banishment Pending
Local time
Today, 10:08
Joined
May 11, 2011
Messages
11,611
So give me all the cases. So far you have yes no and blank. Are there any more? What should they resolve to? What should blank resolve to?
 

bastanu

AWF VIP
Local time
Today, 08:08
Joined
Apr 13, 2010
Messages
1,401
Solution given in the other thread:

IIf(Right([CustomerContacted],1)="?","Unanswered",Trim(Replace([CustomerContacted],"Was the customer contacted?","")))

Cheers,
 

gojets1721

Registered User.
Local time
Today, 08:08
Joined
Jun 11, 2019
Messages
429
So give me all the cases. So far you have yes no and blank. Are there any more? What should they resolve to? What should blank resolve to?
@plog N/A as well. Sorry.

So: yes, no, n/a and blank
 
Last edited:

plog

Banishment Pending
Local time
Today, 10:08
Joined
May 11, 2011
Messages
11,611
You need to make me a map. What does the text in the field look like, and what do you want the result to be.
 

gojets1721

Registered User.
Local time
Today, 08:08
Joined
Jun 11, 2019
Messages
429
@plog Gotcha. See below

Was the customer contacted? = Unanswered
Was the customer contacted? Yes = Yes
Was the customer contacted? No= No
Was the customer contacted? N/A = N/A
 

plog

Banishment Pending
Local time
Today, 10:08
Joined
May 11, 2011
Messages
11,611
I would make a table of that data and use it.

1. Make a query based on your source file. Bring down [1st Question] field, change the query to an aggregate query (click on the Summation/Sigma) so that you have Group By under [1st Question], make the query a MAKE TABLE query and save the new table as [QuestionMap].

2. In design view of [QuestionMap] add a new text field called [MapTo]. Save an open it in data mode and add the values you want each question text to map to ( Yes, No, N/A).

3. Build another query with your source file and [QuestionMAp], link them via [1st Question] and bring down MapTo instead of [1st Question] and it will show you what you want.
 

gojets1721

Registered User.
Local time
Today, 08:08
Joined
Jun 11, 2019
Messages
429
@plog would you ever show that in my example? I'm not entirely how to do any of that tbh
 

plog

Banishment Pending
Local time
Today, 10:08
Joined
May 11, 2011
Messages
11,611
No. It wasn't a general solution, it was pretty detailed in the specific steps you need to take. Give it a shot.
 

bastanu

AWF VIP
Local time
Today, 08:08
Joined
Apr 13, 2010
Messages
1,401
The table solution is great as it would allow the options to grow with no extra code; in the meanwhile try this please (it needed an extra Trim to deal with the spaces):

IIf(Right(Trim([CustomerContacted]),1)="?","Unanswered",Trim(Replace([CustomerContacted],"Was the customer contacted?","")))

Cheers,
 

gojets1721

Registered User.
Local time
Today, 08:08
Joined
Jun 11, 2019
Messages
429
I would make a table of that data and use it.

1. Make a query based on your source file. Bring down [1st Question] field, change the query to an aggregate query (click on the Summation/Sigma) so that you have Group By under [1st Question], make the query a MAKE TABLE query and save the new table as [QuestionMap].

2. In design view of [QuestionMap] add a new text field called [MapTo]. Save an open it in data mode and add the values you want each question text to map to ( Yes, No, N/A).

3. Build another query with your source file and [QuestionMAp], link them via [1st Question] and bring down MapTo instead of [1st Question] and it will show you what you want.
I was able to get it working! Thanks so much!
 

Minty

AWF VIP
Local time
Today, 15:08
Joined
Jul 26, 2013
Messages
10,354
@templeowls I also provided an answer in the crossposted thread, yet you haven't acknowledged that or @bastanu contributions here?
Both of the answers provided gave you what you needed.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:08
Joined
Sep 12, 2006
Messages
15,613
Converting bad excel data into well formed relational data can be a nightmare.
You do need to thoroughly understand the excel data first.
 

Users who are viewing this thread

Top Bottom