Search code examples
excelcsvoledbvba

Issue reading in CSV file. + and - Characters being read as 0

I am experiencing a rather weird issue will reading in a small csv file using the Microsoft.ACE.OLEDB.12.0 engine.

    Dim cN As ADODB.Connection
    Dim RS As ADODB.Recordset
    Set cN = New ADODB.Connection
    Set RS = New ADODB.Recordset
    cN.Open ("Provider=Microsoft.ACE.OLEDB.12.0;" & _
               "Data Source=" & myPath & ";" & _
               "Extended Properties=""Text;HDR=NO;FMT=CSVDelimited""")
    RS.ActiveConnection = cN
   RS.Source = "select * from " & myfile
   RS.Open

When calling on RS.Fields(7) which should be "+" or a "-" it is returning a value of 0?

I have no control over the csv files given so I cannot have the "+" or "-" characters changed to something else.

Solution

  • ACE is a beast with this kind of stuff. Unlike a normal database that has field types as part of it's metadata, a CSV is just data. Because of that, ACE has to make a decision about the data type. In your case, ACE is deciding that this is a number since it starts with a + or -. If I recall correctly, ACE and it's JET predecessor only analyzes the top 8 records to determine the field type.

    There are two ways around this though.

    1. You can use a schema.ini file. Basically this is a text file named schema.ini that you just stick in the same folder as the data you are connecting to. ACE will automatically use the file if it's present. Here's some info about how to build it (and you can google it and get tons of examples).

    2. This is the band-aid approach, which is sometimes easier than the schema.ini depending on your circumstances. Because ACE only analyzes the top 8 records of the CSV to determine the field type, you can just insert 8 new records at the top of the file that have a definitive field type. For example, your field containing + or - just stick a character like S. ACE will then see that this is a non-numeric field and will treat it as a string. You can see here how to prepend data to an existing text file using the filesystemobject (and google for more info, it's terribly useful stuff)