Really struggling with cleaning this excel data when imported to Access

[gojets1721]

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?

 

[arnelgp]

clean it in the raw excel workbook.
create a function that will remove tab character, new line, etc:

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]

Could you drop that into my example spreadsheet by any chance? I know nothing about VBA in excel tbh

 

[plog]

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]

use the xlsm file.

 

[bastanu]

Cross-posted with multiple solutions here:

Struggling to clean this imported data. Any help is appreciated

I've got this data that is imported in from excel and I'm struggling to clean it up in the most efficent way. Essentially, the question and answer are both populated in the field, and I'm trying to remove the question and also replace blanks. Here's how the data looks; it's one of these four...

www.accessforums.net

Cheers,

 

[gojets1721]

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]

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]

Solution given in the other thread:

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

Cheers,

 

[gojets1721]

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

 

[plog]

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]

@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]

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]

@plog would you ever show that in my example? I'm not entirely how to do any of that tbh

 

[plog]

No. It wasn't a general solution, it was pretty detailed in the specific steps you need to take. Give it a shot.

 

[bastanu]

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]

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]

@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]

Converting bad excel data into well formed relational data can be a nightmare.
You do need to thoroughly understand the excel data first.