英文:
Converting number to text with Access SQL
问题
我有一个 *.csv 文件里面有数据。我尝试将数据转移到 Access 数据库。
我的数据看起来像这样：
我正在使用一个 SQL 语句 "INSERT INTO ... SELECT ... FROM"。这个语句在 Access 中运行正常。
问题出在当我尝试将像 "99.48%" 这样的值转换为数字 99.48 时。我使用 REPLACE([Featured Offer (Buy Box) Percentage],'%','')，这会返回 99.48，但是如果目标字段是数值类型，我会收到一个警告 - "数据类型不匹配"。当我将字段数据类型更改为文本时，就可以了。但我最终需要的是数值，类似于 0.9948。
CDbl(REPLACE([Featured Offer (Buy Box) Percentage],'%','')) 不起作用 - 仍然会收到相同的警告 "数据类型不匹配"。
我会感激任何帮助。
英文:
I have an *.csv file with data. I'm trying to transfer the data to an Access db.
My data look like this
I'm using an sql "INSERT INTO ... SELECT ... FROM"
The construction itself works ok for Access.
The problem comes when I'm trying to convert a value like "99.48%" to a number 99.48
I use REPLACE([Featured Offer (Buy Box) Percentage],'%','') and this returns 99.48, but I get a warning - "data type mismatch" if a destination field has a numerical type. When I change field data type to text then it's ok. But I need numerical, finally like 0.9948
CDbl(REPLACE([Featured Offer (Buy Box) Percentage],'%','')) doesn't works - same warning "data type mismatch"
I will appreciate any help
答案1
得分: 1
CSV导入因本地化问题而臭名昭著。如果CSV文件使用.作为小数分隔符，但您的Access/Windows本地设置期望使用,，则无法将其导入为数字。
您最好的选择可能是定义一个导入规范，在其中可以指定小数分隔符为.，字段分隔符为,。
英文:
CSV imports are notorious for localization issues. If the csv has . as decimal separator, but your Access/Windows locale expects ,, it cannot be imported as number.
Your best bet is probably to define an import specification where you can specify the decimal separator . and field separator ,.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论