Importing text data is dropping characters after import from Excel 1

[Pack10]

I have a table that is imported from a spreadsheet that is exported from a system. The field that I need to parse
contains a string of characters...the string could have up to 4 values separated by 2 characters that look like question marks.
When I import the sheet into Access, I don't see the question mark characters that separate each value, Access seems to drop everything past the first set of question mark characters. It looks like this...

XXX Life & Retirement??XXX Property Casualty??AMG??ILFC??UGC

After importing into Access, it looks like this:
XXX Life & Retirement

Any ideas, thanks much.

 

[dhookom]

Are the characters actual carriage return and line feeds? If so, did you adjust the height of the rows in datasheet to see if the extra data is there?

Duane
Hook'D on Access
MS Access MVP

 

[Pack10]

Thanks for responding.
When i expand the row height, I see all the values, There must be a CRLF because they are viewed vertically in the row now.
How would I parse them out now. How could I use Instr in this application?

XXX Life & Retirement
XXX Property Casualty
AMG
ILFC
UGC

 

[PHV]

I'd use the Split function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Pack10]

thanks much

 

[dhookom]

thread701-1694321

Duane
Hook'D on Access
MS Access MVP

 

[Pack10]

Not sure what the delimiter would be in using the split. It is definitely a CR. When I read it character by character, using Asc function, it was
a 13. What would I use in the Split function?

thx

 

[dhookom]

I would test with
Chr(13) & Chr(10)

Duane
Hook'D on Access
MS Access MVP

 

[Pack10]

Thanks to all for helping me with this. I do have a question. I have an array with from 1 to 6 items that are loaded in from a record. I want to build another table (transaction table) with the ID and write the 1 to 6 items to that table.
When I code it, I am getting all 6 items in 1 record. I would like to have 1 item per record.

Thx

 

[Andrzejek]

When I code it ...

It would be nice to see your code. Then somebody can help you fix it.

Have fun.

---- Andy

 

[lameid]

Split returns an array. You can use the combination of a loop and Ubound to loop the correct number of elements and add a record on each loop iteration for the value.

 

[dhookom]

Create a union query from your splits query.

Duane
Hook'D on Access
MS Access MVP