Microsoft Query data type incorrect

[DaKen]

I have an Excel spreadsheet where I'm pulling data from a CSV file using Microsoft Query. One of the fields is an invoice #. Most of the invoices are all numeric (34046), but some have an alpha character after the # (34046A). The query pulls all the numeric values fine, but the ones with the alpha character don't pull at all.
I'm assuming that the query is assuming it is a numeric field and doesn't recognize the alpha character and skips it.
How can I fix it to pull all the data?

 

[Norie]

What happens if you use Data>From Text/CSV... to import the data?

That should fire up the Import Wizard where you can specify the data type of the Invoice# column as Text.

 

[DaKen]

I need to use the query method to allow me to adjust some columns and do some calculations. The text method doesn't allow that.

 

[Norie]

Have you considered using Data>Get and Transform...?

That would allow you to import the data from the CSV, perform adjustments/calculations and return the results to Excel.

 

[DaKen]

I’m not familiar with that. It sounds like a few extra steps, but I’ll have to give it a look see.