Changing connection string in Excel messes up column data types

Question

This is related to my previous question. I'm trying, in Excel, to update external data connections to text files (used by several pivot tables) to point to the correct data sources so data can be refreshed when the Excel workbook and text files are copied to a different directory/computer. (It should act like the paths to the text files are relative.)

The data sources are tab delimited text files with the headers in the first columns. I have code that can change the path in the ODBC connection string, but when I try to refresh the data Excel imports it all as text, not keeping some of the columns numeric as they originally were.

Public Sub Test()
    Dim wb As Excel.Workbook
    Dim pc As PivotCache
    Dim path As String

    Set wb = ActiveWorkbook
    path = wb.path

    For Each pc In wb.PivotCaches
        'Debug.Print pc.Connection
        pc.Connection = "ODBC;DBQ=" & path & ";DefaultDir=" & path & ";Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;FIL=text;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes"

    Next
End Sub

I tried changing MaxScanRows to zero, which I read somewhere would have Excel scan all the rows to guess the data type, but that didn't seem to help.

The original connection info is:

Connection String:

DefaultDir=C:/directoryPath;Driver={Microsoft Text Driver (*.txt; *.csv)};
  DriverId=27;FIL=text;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;
  SafeTransactions=0;Threads=3;UserCommitSync=Yes;

Command Text:

SELECT *
FROM tableName.txt tableName

When I created the connection with the PivotTable wizard (External Data -> MS text driver) I set up all the import/parsing information (tab delimited, column headers in first row, guess data types, etc).

Is there any way to (1) tell Excel to look through the rows and figure out the data types, (2) manually code in the data types for each column, (3) keep the same data types that were originally used, or (4) after the data is refreshed go through the pivot tables in the macro and convert all the numbers to numeric values instead of text? Obviously (1) or (3) seem like they would be easiest and make the most sense, but I'm willing to try other options.

Is your data mixed? You can only set MaxScanRows to zero by tampering with the registry. You then need to set IMEX=1. You may be able to use a schema.ini, it works for the usual recordsets, but I have not tested with a pivot table. — Fionnuala
– Fionnuala, Commented Aug 27, 2012 at 10:23
@Remou Some of my columns are text and others are numeric. I need to be able to copy the whole project to another computer and still have it work, so tampering with the registry doesn't seem very practical. How would you use schema.ini to (potentially) do it? Is there no way to do it in VBA? — scaevity
– scaevity, Commented Aug 27, 2012 at 14:31
You can copy the text into various sheets in excel instead of into a text file and refer to the excel sheets in VBA. A schema.ini is also VBA. Mixed data means different data in the same column, not in different columns. — Fionnuala
– Fionnuala, Commented Aug 27, 2012 at 14:37
You will find an example of schema.ini here stackoverflow.com/a/12109123/2548 more: msdn.microsoft.com/en-us/library/windows/desktop/… — Fionnuala
– Fionnuala, Commented Aug 27, 2012 at 14:39
Thanks. I can't copy it into Excel first as there are too many rows to fit in excel. I'll try the schema.ini thing and get back to you! — scaevity
– scaevity, Commented Aug 27, 2012 at 14:42

Fionnuala · Accepted Answer · 2012-08-27 16:35:40Z

1

Given your requirements, it would seem that using a schema.ini would be the best bet.

For example:

[mycsv.csv]
Format=Delimited(|)
NumberDigits=2
CurrencyThousandSymbol=,
CurrencyDecimalSymbol=.
CurrencyDigits=2
DateTimeFormat="yyyy-mm-dd"

Col1=ADate Date
Col2=AText Text

Schema.ini File (Text File Driver)

answered Aug 27, 2012 at 16:35

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Changing connection string in Excel messes up column data types

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related