Excel CSV Read issue

[bline22]

Hello,
I am trying to read in a CSV file into excel. The issue I am having is, when I read it in it reads all rows, but one of the columns it reads in some of the values and others not. It seems the values that have a character preceeding them don't get read in. Here is my code:

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFilePath & ";Extended Properties=""text;HDR=No;FMT=Delimited"""
Set oRS = CreateObject("ADODB.RECORDSET")
oRS.Open "SELECT * FROM " & strFileName, oConn, 3, 3

Example data. The first 2 sets as well as the last would get read but the middle 2 show up as blank. any idea why?

416370
416418
82281653L
21033913405
1165899

Thanks for the help.

[Jan Karel Pieterse]

Why are you using this method to open the csv as opposed to e.g. Workbooks.Open or Workbooks.OpenText?

[bline22]

Thanks for the response.

I should have prefaced that I am pretty new to VBA development.

I guess I didin't know I could open an external CSV file with those functions. You don't have a quick example do you?

[Jan Karel Pieterse]

Easiest is to rename your csv to .txt. Then start Excel's macro recorder whilst opening the txt file. he text import wiz will appear in which you can set all needed options. At the end of the wiz, turn off the macro recorder and if your imported file looks good, your code should be close to what you need. Then edit the recorded code so your filename is included.

[bline22]

After messing around with this a bit the macro always creates a new sheet and dumps the data to it.

Is there any way to manipulate the data that is coming from the csv or txt file before it dumps it to the excel sheet? I need to do a lot of data validation and editing before dumping it into the sheet. that was part of the reason I was dumping it to a reordset is that i could read from it and do all my manipulation and then send it to the sheet.

Is there another way of reading this csv file to allow for this?
Thanks again for the help.

[Jan Karel Pieterse]

You could also use a fixed worksheet on which you do Data, get external data, import data. Then set up all details and when done, parse that sheet. If you want it to open a new csv, you can simply hit the refresh button when on that sheet and Excel will prompt for a new filename.