sterlingalston
sterlingalston

Reputation: 725

Access importing wrong columns

Help! Access is importing wrong columns from a csv file. There are no commas in the csv and there should not be any spaces like (space)(space), but just (space). I'm ending up with '' in the SERIAL_NR header, but in the actual csv there are values.

enter image description here

This is giving me 1000 extra rows. Is there anything else to check for?

Upvotes: 0

Views: 129

Answers (1)

sterlingalston
sterlingalston

Reputation: 725

It's sneaky and underhanded, but the alt+enter character [chr(10)] was included in a few of the cells. I used the following vba code to get rid of it.

sub cleaner()
Dim enterchar as string

enterchar = chr(10)

activesheet.usedrange.Replace What:=enterchar, Replacement:="", LookAt:=xlPart, _ 
SearchOrder:=xlByRows, MatchCase:=False 

end sub

Pretty nifty for prepping a csv for import into Access. I've created an add-in for cleaning csv's (eliminating duplicates, special char's, etc.) if anyone's interested I'll post.

Thanks all!

Upvotes: 1

Related Questions