Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

CSV - a question for experts 1

Status
Not open for further replies.

Rydel

Programmer
Feb 5, 2001
376
CZ
I've posted a similar query to "Access: Other topics", but maybe this question would be more appropriate here"

I've searched through the previous CSV-related questions on Tek-tips, but couldn't find anything appropriate. Here is my problem, it's not an easy one:

1) I have a huge CSV text file, which I would like to import into MS Access.

2) Some of the text entries have commas and quotes inside. Here is an example how it looks, so you see my point:

Code:
10:01, 01.01.2002, "here is comma, inside and ""quotes"" as well", "another, text field",

11:02, 02.03.2002, "and the word ""foobar"" means nothing, does it", "and test2",

3) I use the wizard (File | Get External Data | Import...) and during the wizard steps it looks like it breaks the fields correctly (you can scroll and see the temporary list in one of the stages of the wizard).

4) But when I click finish I get an error message:

"Text file specification field separator matches decimal separator or text delimiter."

and the data is not imported at all.

P.S. There is data in several languages, and the CSV is a Unicode text file. Could that also affect the wizard? I've played around with the values (Unicode, Cyrillic, Western), but this doesn't seem to have any effect.

This is quite urgent and I would be very thankful for your help!! ---
---
 
I came across something like that years ago working with gwbasic. If I remember correctly I searched for the character " and replaced each instance with ' ( so "foobar" would become 'foobar'. I guess you could do this before creating your CSV file. If needed, you could then search and replace ' for ".

... and NO, I don't claim to be an expert.
Luis
 
Thanks for you suggestion! :) But I suspect this will not really work because of two reasons:

1) How will you distinguish which quotes should be replaced (the one inside the text) and which ones should not (the delimiters)?

2) Replacing the double quote with single quote inside the text is out of question because it will alter the semantics of the code (there are foreign words where single quote is a part of the language and there are code snippets where it's important that the correct quotation marks are used).

Hope you have more ideas! Because I am still stuck. :(

---
---
 
IF the issue is simply the single quote (I suspect it is not), hten you can simply replace the singl quote with any numnber of "odd" character(s). The most common is ~~:

[tab]Replace(', "'")

but -of course- since you need the string delimiters to identify the characters, it is more complicated than that. MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over
 
i'd go back to wherever you got the csv from and complain -- by definition, they cannot use a comma to separate fields if the comma is also used within fields

the other thing you can do is get somebody who knows regular expressions to parse it to match quotes and only change commas to some other character inside quotes -- i don't do regular expressions and i'm not 100% sure that will work but it's worth a try -- then once the fields are in access, use Replace() to change the other character back

rudy
 
The thread is also posted in thread181-354336, where I have commented that by using the sample lines provided by Rydel, I was able to import exactly as needed. I have also suggested that perhaps creating a custom import code to walk through the file line by line might be useful...

Declare a variable
Fill variable character by character until comma
At comma, fill appropriate field with variable
Clear variable
If you encounter a quote, keep filling variable until you
reach a quote immediately followed by a comma (this
should allow for the double quotes)
Fill field
Clear variable
etc
etc
etc If we knew what it was we were doing, it would not be called research, would it? - Albert Einstein [atom]

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top