在Excel中更改连接字符串会混乱列数据类型 [英] Changing connection string in Excel messes up column data types

问题描述

这与我的上一个问题。我正在Excel中更新外部数据连接到文本文件（由几个数据透视表使用）来指向正确的数据源，以便当Excel工作簿和文本文件复制到不同的目录/计算机时可以刷新数据。 。 （它应该像文本文件的路径是相对的）。

数据源是制表符分隔的文本文件，头文件是第一列。我有可以改变ODBC连接字符串中的路径的代码，但是当我尝试刷新数据时，Excel将其全部导入为文本，而不是像原来那样保留一些列的数字。

 公共子测试（）
 Dim wb As Excel.Workbook 
 Dim pc As PivotCache 
 Dim path As String 
 
设置wb = ActiveWorkbook 
路径= wb.path 
 
对于每个pc在wb.PivotCaches 
'Debug.Print pc.Connection 
 pc.Connection =ODBC; DBQ =&路径& ; DefaultDir =&路径& ; Driver = {Microsoft Text Driver（* .txt; * .csv）}; DriverId = 27; FIL = text; MaxBufferSize = 2048; MaxScanRows = 8; PageTimeout = 5; SafeTransactions = 0; Threads = 3; UserCommitSync = Yes 
 
下一个
 End Sub 
  

我尝试更改code> MaxScanRows 为零，我读到某处会有Excel扫描所有的行来猜测数据类型，但似乎没有帮助。

原来的连接信息是：

连接字符串：

  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; 
  

命令文本：

  SELECT * 
 FROM tableName.txt tableName 
  

当我创建与数据透视表向导（外部数据 - > MS文本驱动程序）的连接我设置所有导入/解析信息（制表符分隔，第一行中的列标题，猜测数据类型等）。有没有办法（1）告诉Excel查看这些行并找出数据类型，（2）在每个列的数据类型中手动编写代码， （3）保留最初使用的相同数据类型，或（4）刷新数据后，通过宏中的数据透视表，并将所有数字转换为数值而不是文本？显然（1）或（3）看起来似乎最简单，最有意义，但我愿意尝试其他选项。

解决方案

例如：

  [mycsv.csv] 
格式=分隔（|）
 NumberDigits = 2 
 CurrencyThousandSymbol =，
 CurrencyDecimalSymbol =。 
 CurrencyDigits = 2 
 DateTimeFormat =yyyy-mm-dd
 
 Col1 = ADate日期
 Col2 = AText文本
  

Schema.ini文件（文本文件驱动程序）

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.

解决方案

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)

这篇关于在Excel中更改连接字符串会混乱列数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！