我该如何导入这些数据？ [英] How can I import this data?
问题描述
我的CSV文件是制表符分隔的，没有文本限定符。通常
这没关系，但数据中也有标签！
无论如何我可以获取这些数据吗？这是数据看起来像
之类的（为了说明目的，我用[TAB]替换了实际的选项卡）：
CustomerID [TAB] CustomerNotes [TAB ] [LINEBREAK]
0001 [TAB]喜欢巧克力[TAB] [TAB]讨厌奶酪[TAB] [LINEBREAK]
0002 [TAB] [TAB]喜欢苹果[ TAB] [LINEBREAK] [TAB]讨厌菠萝[LINEBREAK]
0003 [TAB]大声打鼾[LINEBREAK] [TAB] [TAB]吸烟太多[TAB] [LINEBREAK]
0004等...
我可以用
地点的标签和换行符修复此标签分隔数据吗？顺便说一句，我只能以这种格式获取数据，即我们无法在获取CSV文件之前对它进行操作。
谢谢，
Paul
" Paul H" <无**** @ nospam.com>在留言中写道
新闻：sV *************** @ newsfe3-win.ntli.net ...我有一个CSV文件，它是制表符分隔的，没有文本限定符。
通常这没关系，但数据中也有选项卡！
无论如何我可以将这些数据输入访问？这是数据
的样子（我用[TAB]替换了实际的标签用于说明
目的）：
CustomerID [TAB] CustomerNotes [TAB] [LINEBREAK]
0001 [TAB]喜欢巧克力[TAB] [TAB]讨厌奶酪[TAB] [LINEBREAK]
0002 [TAB] [TAB]喜欢苹果[TAB] [LINEBREAK] [TAB]讨厌菠萝[LINEBREAK ]
0003 [TAB]大声打鼾[LINEBREAK] [TAB] [TAB]抽烟太多
[TAB] [LINEBREAK]
0004等...
我可以使用选项卡和整个
位置的换行符来修复此制表符分隔数据吗？顺便说一句，我只能以这种格式获取数据，即我们在获取CSV文件之前无法对其进行操作。
谢谢，
Paul
这类事情是Access程序员经常要做的事情 - 而且要正确地执行它需要编写一个自定义的vba例程。最重要的第一个问题是你希望将表格结构放在什么表格中？
进入？通常你可以使用三个字段：ID（只是一个自动编号来确定行），CustomerID和Notes，所以新表将是：
0001 0001喜欢巧克力
0002 0001仇恨奶酪
0003 0002喜欢苹果
0004 0002 Hates Pineapple
但也许你还有其他的要求。
第二个问题是：你能写任何vba或者你在寻找
有人为你写完整的例行程序吗？ />
" Anthony England" < AE ****** @ oops.co.uk>在留言中写道
新闻：7O ****************************** @ bt.com .. 。
Paul H <无**** @ nospam.com>在消息中写道
新闻：sV *************** @ newsfe3-win.ntli.net ...我有一个CSV文件是制表符分隔的，没有文本限定符。
通常情况下这没关系，但数据中也有选项卡！
无论如何我可以访问这些数据吗？这是数据
的样子（我用[TAB]替换了实际的标签用于说明
目的）：
CustomerID [TAB] CustomerNotes [TAB] [LINEBREAK]
0001 [TAB]喜欢巧克力[TAB] [TAB]讨厌奶酪[TAB] [LINEBREAK]
0002 [TAB] [TAB]喜欢苹果[TAB] [LINEBREAK] [TAB]讨厌菠萝[LINEBREAK ]
0003 [TAB]大声打鼾[LINEBREAK] [TAB] [TAB]抽烟太多
[TAB] [LINEBREAK]
0004等...
我可以使用选项卡和整个
位置的换行符来修复此制表符分隔数据吗？顺便说一句，我只能以这种格式获取数据，即我们在获取CSV文件之前无法对其进行操作。
谢谢，
Paul
这类是Access程序员经常需要做的事情 - 并且要正确地执行它，你需要编写一个自定义的vba例程。最重要的第一个问题是你想把它放在什么表格结构中？通常你可以使用三个字段：ID（只是一个自动编号来识别行），CustomerID和Notes，所以新表格将是：
0001 0001喜欢巧克力
0002 0001讨厌奶酪
0003 0002喜欢苹果
0004 0002讨厌菠萝
但也许你有其他要求。
第二个问题是：你能写任何vba或者是你在寻找有人为你编写完整的例程吗？
谢谢Anthony，
对不起，我没有我不能很好地解释自己。这只是两个领域;客户ID
和备注。它应该是这样的：
客户ID备注
0001喜欢巧克力Hates奶酪
0002喜欢苹果Hates Pineapple
问题是Notes数据中有随机选项卡和换行符。我很乐意写VBA，但不知道从哪里开始使用这些数据
因为我无法预测那里会有多少个标签或换行符
在每个Notes字段中。我很感激任何指示。
问候，
Paul
"保罗H" <无**** @ nospam.com>写在
新闻：sV *************** @ newsfe3-win.ntli.net:
我有一个CSV文件，它是制表符分隔的，没有文本限定符。
通常这没关系，但数据中也有选项卡！
无论如何我可以得到这些数据进入？这是数据
的样子（我用[TAB]替换了实际的标签用于插图目的）：
CustomerID [TAB] CustomerNotes [TAB] [LINEBREAK]
0001 [TAB]喜欢巧克力[TAB] [TAB]讨厌奶酪[TAB] [LINEBREAK]
0002 [TAB] [TAB]喜欢苹果[TAB] [LINEBREAK] [TAB] Hates
菠萝[LINEBREAK] 0003 [TAB]大声打鼾[LINEBREAK] [TAB] [TAB]吸烟过多[TAB] [LINEBREAK] 0004等...
我能解决这个问题吗？制表符分隔数据，标签和换行符遍布这个地方？顺便说一句，我只能以这种格式获取数据，即我们无法在获取CSV文件之前对其进行操作。
这很有可能编写解析和导入这些数据的VBA代码。
我很犹豫，因为海报回应的许多经历：
嗯，我''使用Access 97和替换不起作用;
嗯，这不是数据真正的样子;我只是给了一个
的例子，虽然你的解决方案适用于这个例子，但是它不会在
上运行真实的事情;
嗯，ids实际上从1到100000;我只是把000？
模式放在那里，所以它在帖子里看起来不错。
或者没有回复：
嗯，我以前从未见过这样的东西，所以我甚至都没试过。
如果你想要一些可能的解决方案，我建议你做一些事情<可用文件的
最好从网络服务器下载，指定
你正在使用什么版本的Access，并明确说明我们如何使用
识别ID。
我希望您已经从其他答案中了解到您的数据库设计可能会被怀疑为b $ b。数据库设计的一个主要规则是列是原子的，它们描述单个属性。您的备注字段不是。这可能是你问题的基础，尽管它似乎是CSV文件的问题。
无论如何，编写你想知道的代码是一个十分钟的工作，当我们知道规则和背景时，你需要b $ b。
-
Lyle Fairfield
I have a CSV file that is tab delimited and has no text qualifiers. Normally
this would be OK, but the data has tabs in it as well!
Is there anyway I can get this data into access? Here is what the data looks
like (I have replaced the actual tabs with [TAB] for illustration purposes):
CustomerID[TAB]CustomerNotes[TAB][LINEBREAK]
0001[TAB]Likes chocolate[TAB][TAB]Hates cheese[TAB][LINEBREAK]
0002[TAB][TAB]Likes Apples[TAB][LINEBREAK][TAB]Hates Pineapple[LINEBREAK]
0003[TAB]Snores loudly[LINEBREAK][TAB][TAB]Smokes too much [TAB][LINEBREAK]
0004 etc...
Can I fix this tab delimited data with tabs and line breaks all over the
place? BTW, I can only get the data in this format i.e. we can not
manipulate it before we get the CSV file.
Thanks,
Paul
"Paul H" <no****@nospam.com> wrote in message
news:sV***************@newsfe3-win.ntli.net...I have a CSV file that is tab delimited and has no text qualifiers.
Normally this would be OK, but the data has tabs in it as well!
Is there anyway I can get this data into access? Here is what the data
looks like (I have replaced the actual tabs with [TAB] for illustration
purposes):
CustomerID[TAB]CustomerNotes[TAB][LINEBREAK]
0001[TAB]Likes chocolate[TAB][TAB]Hates cheese[TAB][LINEBREAK]
0002[TAB][TAB]Likes Apples[TAB][LINEBREAK][TAB]Hates Pineapple[LINEBREAK]
0003[TAB]Snores loudly[LINEBREAK][TAB][TAB]Smokes too much
[TAB][LINEBREAK]
0004 etc...
Can I fix this tab delimited data with tabs and line breaks all over the
place? BTW, I can only get the data in this format i.e. we can not
manipulate it before we get the CSV file.
Thanks,
Paul
This sort of thing is what Access programmers often have to do - and to do
it properly you would need to write a custom vba routine. The most
important first question is what table structure do you want it to be put
into? Typically you might use three fields: ID (just an autonumber to
identify the row), CustomerID and Notes, so the new table would be:
0001 0001 Likes chocolate
0002 0001 Hates cheese
0003 0002 Likes Apples
0004 0002 Hates Pineapple
but perhaps you have other requirements.
The second question is: can you write any vba or were you looking for
someone to write the complete routine for you?
"Anthony England" <ae******@oops.co.uk> wrote in message
news:7O******************************@bt.com...
"Paul H" <no****@nospam.com> wrote in message
news:sV***************@newsfe3-win.ntli.net...I have a CSV file that is tab delimited and has no text qualifiers.
Normally this would be OK, but the data has tabs in it as well!
Is there anyway I can get this data into access? Here is what the data
looks like (I have replaced the actual tabs with [TAB] for illustration
purposes):
CustomerID[TAB]CustomerNotes[TAB][LINEBREAK]
0001[TAB]Likes chocolate[TAB][TAB]Hates cheese[TAB][LINEBREAK]
0002[TAB][TAB]Likes Apples[TAB][LINEBREAK][TAB]Hates Pineapple[LINEBREAK]
0003[TAB]Snores loudly[LINEBREAK][TAB][TAB]Smokes too much
[TAB][LINEBREAK]
0004 etc...
Can I fix this tab delimited data with tabs and line breaks all over the
place? BTW, I can only get the data in this format i.e. we can not
manipulate it before we get the CSV file.
Thanks,
Paul
This sort of thing is what Access programmers often have to do - and to do
it properly you would need to write a custom vba routine. The most
important first question is what table structure do you want it to be put
into? Typically you might use three fields: ID (just an autonumber to
identify the row), CustomerID and Notes, so the new table would be:
0001 0001 Likes chocolate
0002 0001 Hates cheese
0003 0002 Likes Apples
0004 0002 Hates Pineapple
but perhaps you have other requirements.
The second question is: can you write any vba or were you looking for
someone to write the complete routine for you?
Thanks Anthony,
Sorry I didn''t explain myself very well. It is just two fields; CustomerID
and Notes. it should look like this:
CustomerID Notes
0001 Likes chocolate Hates cheese
0002 Likes Apples Hates Pineapple
The problem is that the Notes data has random Tabs and line breaks in it. I
am comfortable writing VBA but have no idea where to start with this data
because I can not predict where and how many tabs or linebreaks there will
be in each Notes field. I would appreciate any pointers.
Regards,
Paul
"Paul H" <no****@nospam.com> wrote in
news:sV***************@newsfe3-win.ntli.net:
I have a CSV file that is tab delimited and has no text qualifiers.
Normally this would be OK, but the data has tabs in it as well!
Is there anyway I can get this data into access? Here is what the data
looks like (I have replaced the actual tabs with [TAB] for
illustration purposes):
CustomerID[TAB]CustomerNotes[TAB][LINEBREAK]
0001[TAB]Likes chocolate[TAB][TAB]Hates cheese[TAB][LINEBREAK]
0002[TAB][TAB]Likes Apples[TAB][LINEBREAK][TAB]Hates
Pineapple[LINEBREAK] 0003[TAB]Snores loudly[LINEBREAK][TAB][TAB]Smokes
too much [TAB][LINEBREAK] 0004 etc...
Can I fix this tab delimited data with tabs and line breaks all over
the place? BTW, I can only get the data in this format i.e. we can not
manipulate it before we get the CSV file.
It''s quite possible to write VBA code that parses and imports this data.
I''m hesitant because of many experiences where the poster responds:
Well, I''m using Access 97 and Replace doesn''t work;
Well, that''s not what the data are really like; I was just giving an
example and while your solution works on the example, it won''t work on
the "real" thing;
Well, the ids actually range from 1 to 100000; I just put the 000?
pattern there so it would look good in the post.
or doesn''t respond with:
Well, I''ve never seen anything like that before so I won''t even try it.
If you''d like some possible solutions I suggest that you make some part
of the file available preferable as a download from a web-server, specify
what version of Access you are using and clearly state how we may
identify IDs.
I expect you are already aware from other answers that you db design may
be suspect. A primary rule of db design is that columns be atomic, that
they describe a single attribute. Your notes field is not. This may be
the foundation for your problem, even though it appears that it''s a
problem with the CSV file.
Regardless, writing the code you are wondering about is a ten minute job,
when we know the rules and the background.
--
Lyle Fairfield
这篇关于我该如何导入这些数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
