尝试导入CSV时，数据会以错误的列结束 [英] When trying to import CSV, data ending up in the wrong columns

问题描述

我正在尝试使用VBA中的导入规范将CSV文件导入Access表，但由于某种原因，某些数据被插入到错误的列中。我有两个表/ CSV文件的问题。我将提供更简单的
文件/表的详细信息，只有4列，但我希望该解决方案能帮助我修复更复杂的文件导入。

I'm trying to import CSV files into Access tables using import specifications in VBA but for some reason the some of the data is being inserted into the wrong columns. I'm having this problem with two tables/CSV files. I will give the details for the simpler file/table with only 4 columns but I'm hoping the solution will help me fix the more complex file import as well.

简单的CSV文件如下所示：
$
fxCurrency，fxHCurrency，fxRateSOD，fxRateCOD

AED，ZAR，1318.99,1382.22

USD， ZAR ,,

The simple CSV file looks like this for example:
fxCurrency,fxHCurrency,fxRateSOD,fxRateCOD
AED,ZAR,1318.99,1382.22
USD,ZAR,,

所以有列标题，空值用空值表示（,,）。 CSV文件中没有逗号或引号。

So there are column headers and null values are represented with an empty value (,,). There are no commas or quotation marks in the CSV files.

我正在使用的代码是（在代码的前面正确设置了fileName）：

The code I'm using is (fileName is being set correctly earlier in the code):

Dim tableName As String   tableName = "Currencies"   Dim xml As String   'This xml string contains the specifications the use for that table   xml = ""   xml = xml & "<?xml version=""1.0"" encoding=""utf-8"" ?>" & vbCrLf   xml = xml & "<ImportExportSpecification Path=" & Chr(34) & fileName & Chr(34) & "   xmlns=""urn:www.microsoft.com/office/access/imexspec"">" & vbCrLf   xml = xml & "   <ImportText TextFormat=""Delimited"" FirstRowHasNames=""true"" FieldDelimiter="","" CodePage=""850"" AppendToTable=" & Chr(34) & tableName & Chr(34) & " >" & vbCrLf   xml = xml & "      <DateFormat DateOrder=""DMY"" DateDelimiter=""/"" TimeDelimiter="":"" FourYearDates=""true"" DatesLeadingZeros=""true"" />" & vbCrLf   xml = xml & "      <NumberFormat DecimalSymbol=""."" />" & vbCrLf   xml = xml & "           <Columns PrimaryKey=""fxCurrency"">" & vbCrLf   xml = xml & "                    <Column Name=""Col1"" FieldName=""fxCurrency"" Indexed=""YESDUPLICATES"" SkipColumn=""false"" DataType=""Text"" Width=""3"" />" & vbCrLf   xml = xml & "                    <Column Name=""Col2"" FieldName=""fxHCurrency"" Indexed=""NO"" SkipColumn=""false"" DataType=""Text"" Width=""3"" />" & vbCrLf   xml = xml & "                    <Column Name=""Col3"" FieldName=""fxRateSOD"" Indexed=""NO"" SkipColumn=""false"" DataType=""Double"" />" & vbCrLf   xml = xml & "                    <Column Name=""Col4"" FieldName=""fxRateCOD"" Indexed=""NO"" SkipColumn=""false"" DataType=""Double"" />" & vbCrLf   xml = xml & "         </Columns>" & vbCrLf   xml = xml & "     </ImportText>" & vbCrLf   xml = xml & "</ImportExportSpecification>"      CurrentProject.ImportExportSpecifications.Add "fxRates", xml   On Error GoTo ImportDelete:      Dim delQuery As DAO.QueryDef   Set delQuery = CurrentDb.QueryDefs("0 Clear Currencies")   delQuery.Execute      DoCmd.RunSavedImportExport "fxRates"ImportDelete:   CurrentProject.ImportExportSpecifications![fxRates].Delete

该表具有与CSV文件完全相同的列名，并且顺序完全相同，列为前两个的文本字段大小为3，字段大小为Number，后两列为Double。 fxCurrency是表
中的主键，没有设置重复项（但我没有在文件中重复，所以它应该不是问题）。然而，当我导入时，fxRateSOD列中的数据被插入到fxRateCOD列中，反之亦然。我试过创建一个新表，同样的事情
发生了。我仔细检查过CSV文件和表格中的数据顺序是否相同。另外，我有另一个包含51列和自动编号主键的文件，它也没有将数据插入到正确的列中，但是情况
比简单切换列数据更复杂。所有这些都在Access 2007中。

The table has the exact same column names as the CSV file and in the exact same order, the columns are Text for the first two with a field size of 3 and Number with field size Double for the second two columns. fxCurrency is the primary key in the table with no duplicates set (but I haven't had duplicates in the file so it shouldn't be a problem). Yet when I import, the data in the fxRateSOD column is being inserted in the fxRateCOD column and vice versa. I've tried creating a new table and the same thing happened. I have double checked that the data is in the same order in the CSV file and in the table. Also, I have another file with 51 columns and a autonumber primary key which is also not inserting the data into the correct columns, but there the situation is more complex than a simple switching of columns' data. All of this is in Access 2007.

我们的想法是每天都会有新文件使用不同的文件名，但每天都会将数据插入到同一个表中。在导入数据之前的查询只是清除表格，以便只有新日期的数据才会出现在表格中。

The idea is that there will be new files daily with different filenames but their data will be inserted into the same table each day. The query just before I import the data simply clears the table so that only the new day's data will be in the table.

请帮助，我无法找到任何解决方案问题。

Please help, I have not been able to find any solutions to this problem.

推荐答案

Cristo -

Cristo -

这是我第一次在访问中使用XML ...

This is the first time I’ve worked with XML in access …

导入代码并在VBA中运行它会在Access 2010中产生运行时错误31957：

Importing your code and running it in VBA produces a runtime error 31957 in Access 2010:

"规范XML无法针对架构进行验证。 XML文档的以下行中存在错误：缺少必需的空格。

"The specification XML failed to validate against the schema. There is an error in the following line of the XML document: Required white space was missing.

。"

帮助告诉我什么。

通过眼睛看，doc对我来说看起来很好。

By eye, the doc looks well-formed to me.

这篇关于尝试导入CSV时，数据会以错误的列结束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！