电子表格上传数据转换错误 [英] Spreadsheet upload data convertion error
问题描述
各位大家好！
我得到了excel电子表格，可以说我需要上传到访问数据库的50000行。我创建了vba代码来执行自动上传到指定的表，但是，据我所知，当访问执行上传时，它从excel获取前25行数据并确定每个字段的类型，然后执行上载，然后将数据转换为匹配表中指定的那个。我面临的问题是我有字段发票号。可以是8到11位数。在上传时间期间，访问权限将该字段识别为int，但当它出现在11位数的发票号码时，它会将其空白，因为它超出了int'的范围。
有没有办法在上传之前指定要使用的数据类型？ （除了在excel中对数据进行排序）
非常感谢您的帮助。
Hello everyone!
I got excel spreadsheet with lets say 50000 lines that I need to upload into access db. I created vba code to perform automated upload into specified table, however, as far as i understand, when access performs upload it takes first 25 rows of data from excel and determines the type of each field, then performs upload and then converts the data to match the one specified in the table. The problem I''m facing is that I have field "invoice number" that can be ranging from 8 to 11 digits. during upload time access recognizes the field as int, but when it comes accross 11 digit invoice number it blanks it out since its out of range for int''s.
Is there a way to specify what data type to use before upload? (besides sorting data in excel)
Thanks a lot for any help.
推荐答案
大家好！
我得到了excel电子表格，可以说我需要上传到访问数据库的50000行。我创建了vba代码来执行自动上传到指定的表，但是，据我所知，当访问执行上传时，它从excel获取前25行数据并确定每个字段的类型，然后执行上载，然后将数据转换为匹配表中指定的那个。我面临的问题是我有字段发票号。可以是8到11位数。在上传时间期间，访问权限将该字段识别为int，但当它出现在11位数的发票号码时，它会将其空白，因为它超出了int'的范围。
有没有办法在上传之前指定要使用的数据类型？ （除了在excel中分类数据）
非常感谢您的帮助。
Hello everyone!
I got excel spreadsheet with lets say 50000 lines that I need to upload into access db. I created vba code to perform automated upload into specified table, however, as far as i understand, when access performs upload it takes first 25 rows of data from excel and determines the type of each field, then performs upload and then converts the data to match the one specified in the table. The problem I''m facing is that I have field "invoice number" that can be ranging from 8 to 11 digits. during upload time access recognizes the field as int, but when it comes accross 11 digit invoice number it blanks it out since its out of range for int''s.
Is there a way to specify what data type to use before upload? (besides sorting data in excel)
Thanks a lot for any help.
您有两种选择。
You have two options.
- 首先创建表并上传数据
- 将excel文件更改为csv文件，并创建导入数据的导入规范。
Mary
我正在尝试寻求第一个建议的解决方案，但在转换过程中仍然会丢失一些值。例如：
excel文件：
发票号码
1234
1235
1236
...
第250行：1234df6
好，访问权限下降250行因为基于第一25记录它确定该字段是一个整数，即使我在destanation表中将其指定为文本...所以它将它作为一个整数上传它然后转换为表中指定的文本和问题我我试图解决。
第二个选项对用户来说不方便。
感谢您的回复...
任何其他建议如何从excel上传数据？
I''m trying to pursue the first suggested solution but some values are still getting lost during the conversion. for example:
excel file:
invoice number
1234
1235
1236
...
line 250: 1234df6
well, access drops line 250 because based on first 25 records it determined the field to be an integer, even tho I specify it as text in the destanation table... so it takes it as an integer uploads it and then converts in to text as specified in the table and that the problem I''m trying to work around.
second option is not convinient for users.
Thanks for replying...
any other advises how to upload data from excel?
我正在尝试寻求第一个建议的解决方案但是在转换过程中仍然会丢失一些值。例如：
excel文件：
发票号码
1234
1235
1236
...
第250行：1234df6
好，访问权限下降250行因为基于第一25记录它确定该字段是一个整数，即使我在destanation表中将其指定为文本...所以它将它作为一个整数上传它然后转换为表中指定的文本和问题我我试图解决。
第二个选项对用户来说不方便。
感谢您的回复...
任何其他建议如何从excel上传数据？
I''m trying to pursue the first suggested solution but some values are still getting lost during the conversion. for example:
excel file:
invoice number
1234
1235
1236
...
line 250: 1234df6
well, access drops line 250 because based on first 25 records it determined the field to be an integer, even tho I specify it as text in the destanation table... so it takes it as an integer uploads it and then converts in to text as specified in the table and that the problem I''m trying to work around.
second option is not convinient for users.
Thanks for replying...
any other advises how to upload data from excel?
如果您创建了表格，那么只需进入并将数字格式更改为Long Integer或任何适合的文本。
If you created the table then just go in and change the number format to Long Integer or text whichever suits.
这篇关于电子表格上传数据转换错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
